QP bottleneck due to type compatibility check.

[eriksunsol]

Starcounter version: 3.0.

Issue type

• Bug

Issue description

We discovered a performance bottleneck in SC 3.0 which is not present in SC 2.3.1 LTS. The query processor attempts to verify that the type returned by a query is compatible with the generic type argument passed in ISqlContext.Sql<T>(string query, params object[] values). This type compatibility check uses reflection on the type argument apparently without caching the results.

In our case the compatibility check is not needed because we do not use tuples in this context. Anyway, we see a 25-50% performance penalty from this check alone (no exact numbers as tests were not run on isolated queries, but rather a real-world scenario with lots of other code). We are able to work around this by simply passing object as the type argument. In one scenario this small change reduced execution time of a complex algorithm from ~15 seconds to ~11 seconds. So the only change to get that improvement was instead of calling context.Sql<ActualType>("SQL...", args) we call context.Sql<object>("SQL...", args").Cast<ActualType>().

[miyconst]

The root cause of this issue is the fact that we are trying to serve all possible return types with a single db.Sql<T> methods. Here are a few examples:

• db.Sql<Product>("SELECT p FROM Product p"); - returns a Product object itself.
• db.Sql<ISqlResultRow>("SELECT p FROM Product p"); - returns an instance of an internal class SqlResultRow with the product properties as the values.
• db.Sql<Tuple<Product>>("SELECT p FROM Product p"); - returns a Tuple<Product> with the product as a value.
• db.Sql<Tuple<Product, Product>>("SELECT p, p.Parent FROM Product p"); - returns a Tuple<Product, Product> with the product and its parent as the values.
• db.Sql<string>("SELECT p.Name FROM Product p"); - returns a string with the product name.
• db.Sql<Tuple<string>>("SELECT p.Name FROM Product p"); - returns a Tuple<string> with the product name as a value.
• db.Sql<ISqlResultRow>("SELECT p.Name FROM Product p"); - returns an instance of an internal class SqlResultRow with the product name as a value.

It is also required to take a special treatment of the Starcounter.Database.Binary type.

I have created a branch where I added some caching to the place which delivers the biggest performance hit, but in the long run, it would be nice to redesign this part, so there will be no such calculations at the runtime.

@per-samuelsson & @bigwad will be in CC for the branch with caching.