Query Cache Performance

[Goransi]

Our application uses complex queries (some dynamically generated) which makes it hard to use CompiledQueries. In our performance tests we discovered a lot of time is spent by Devart.Data.Linq.Engine transforming query expression to sql (SqlVisitor).
Query caching is turned on so this is surprising.
How does query caching work in LinqConnect? Do query expressions always get parsed to sql string representation before checking query cache? Is there anything we should avoid doing to not confuse the query caching?

Thanks!

[MariiaI]

The information about how CompiledQueryCache works is available here:
http://www.devart.com/linqconnect/docs/ ... eries.html
Additional information: at the moment, the cached query is not used, if there are parameters with null values.

We have corresponded with you about the performance issue with CompiledQueryCache (CompiledQueryCache locking issue) by e-mail. We have fixed this issue and sent you a notification regarding this:
The bug related the CompiledQueryCache locking issue is fixed.
New build of LinqConnect 4.4.717 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=31499 .

Please tell us, whether you've upgraded your LinqConnect, and whether you're encountering performance issues with the new version.
Also, if possible, you can send us a sample project with your complex queries, so that we are able to investigate the behaviour you are encountering with the CompiledQueryCache in more details.

[Goransi]

Hi!

Yes, we're in the process of testing out the latest version of LinqConnect. This version mitigates the performance problems of locking the query cache. But our performance tests show that linq queries are still pre-processed before check against the query cache is made.

We're wondering if there are some things we should avoid in order to help query processor to make this pre-process step faster. (ie. not using nullable parameters etc...)

at the moment, the cached query is not used, if there are parameters with null values.

Our tests show that it is used even if nullable parameters with value null are used. Can you elaborate on this?

Goran

[MariiaI]

We're wondering if there are some things we should avoid in order to help query processor to make this pre-process step faster.

Unfortunately, the information you've provided is not enough to investigate your scenario and find the solution for you. Thus, please create and send us a sample project with your complex queries, so that we are able to investigate the behaviour you are encountering with the CompiledQueryCache in more details. Please also describe which exactly results you're getting and the results you're expecting to get.

As for the parameters with null values, for example, we have these queries:

Code: Select all

(1)
 var result1 = from o in context.TESTTABLEs
     where o.THREADID == "1"
     select o; 
(2)								  
var result2 = from o in context.TESTTABLEs
    where o.THREADID == null
    select o;

In this case, the second query will not use the cached query that corresponds to (1), but new cached query will be added to the CompiledQueryCache.
This is because in fact these two queries differ (http://www.devart.com/linqconnect/docs/?DbMonitor.html):

Code: Select all

(1)
SELECT t1.ID, t1.THREADID, t1."TIMESTAMP"
FROM MARIIAI."TESTTABLEs" t1
WHERE t1.THREADID = :p0
-- p0: Input NVarChar (Size = 1; DbType = String) [1]
(2)
SELECT t1.ID, t1.THREADID, t1."TIMESTAMP"
FROM MARIIAI."TESTTABLEs" t1
WHERE t1.THREADID IS NULL

Looking forward to your reply.

[Goransi]

Unfortunately, the information you've provided is not enough to investigate your scenario and find the solution for you.

I'll try and extract a test case. The problem is such queries join many tables and it's hard to create a small repro.

In this case, the second query will not use the cached query that corresponds to (1), but new cached query will be added to the CompiledQueryCache.

That is what we expect. I misunderstood you to mean CQC is not used at all.

Thank you for your responses!

Goran

[MariiaI]

I'll try and extract a test case. The problem is such queries join many tables and it's hard to create a small repro.

We are looking forward to your reply with a sample project and test model for reproducing the issue.