Different Execution Plans followed between Linq and SQL text

Different Execution Plans followed between Linq and SQL text

Postby crazypit » Wed 14 Sep 2011 09:43

Hello,

I seem to have a performance issue that you maybe help me with. At the system described in http://www.devart.com/forums/viewtopic.php?t=20504 i have a function-based index on a CLOB column in which i index the first 4000 characters in it. dbms_lob.substr(, 4000, 1). I have the bizarre situation where my application's LINQ compiled query DO NOT use the Index but when i run the generated SQL text query in SQL plus, everything works ok.

I use the DataContext.Log to get the SQL. When i run my application, at the line where the compiled query is called, the SQL text is written in my log very fast (i guess the precompilation works ok) and then, i guess, the query is run against the database. This way, the query does not use the index and takes about 30 secs.

Next test. I get the LINQ-generated SQL text from my log file (is it 100% sure that this is the correct SQL actually run against the database?) and run it in SQL plus. The query uses the index and takes about 0.3 secs.

Next test. I change the line of code in my application that calls the compiled query and use the ExecuteQuery Method projecting to the same type which is a list of Customer objects. So, from:

var customers = Queries.GetCustomers(dataContext, ).ToList()

i go to:
var customers = datacontext.ExecuteQuery("SQL Text Query")
The query again uses the index! I tried that a lot of times, and everytime i get the same results! I can't imagine what goes wrong...

Difference i can see, is that i use oci connection pooling in my application and from SQL plus i have to open a dedicated connection.

Can you think of something?
crazypit
 
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Postby StanislavK » Thu 15 Sep 2011 15:41

Could you please describe how you are performing these tests (e.g., whether you are using the same or different DataContext objects for LINQ query and for the ExecuteQuery method), and how exactly you determine that the index is not used for the LINQ query?

When executing the query, LinqConnect performs the following:
1) compiles the query and generates the SQL command to be executed;
2) sends the query to the server and waits until it is executed;
3) fetches and materializes the data from the executed command.

When working with multiple CLOBs, both preparation and execution of the query should take much less time than the entity materialization (unless the query is too complicated). Thus, if you are using the same DataContext instance for both LINQ query and the ExecuteQuery method, the difference in time consumed may be caused by the fact that all retrieved entities are cached when executing the LINQ query. In this case, ExecuteQuery should take much less time since it can use the cached result.

In case different DataContext instances are used, we couldn't reproduce the issue in our environment: the index was (or was not, depending on the query) used for both LINQ query and the ExecuteQuery method.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby crazypit » Fri 16 Sep 2011 06:31

As i mentioned, the line of code that uses the LINQ method is replaced by the line of code that uses the ExecuteQuery. The datacontext is always different, and is disposed to a pool of connections.

I assume that the index is not used because i cannot think of something else for the delay. Fast value is 0.3 secs and slow value is 30 secs. Im asking if you think that something else is the problem.

An other reason about assuming that the index is not used, is the way i managed to overcome the problem by still using the LINQ implementation. I created a materialized view that has two columns. The Id column of the table that holds the CLOB value and an other column that is created by using the dbms_lob.substr(, 4000, 1) on the CLOB. Effectively i created a table with the value of the index. I just join the tables over the Id column and check the value on the materialized view table and not the original one. That is:

I had: (from objCustomer in dataContext.Customers
where objCustomer.Value.Substring(0,4000) == select objCustomer).ToList()

and went to:
(from objCustomer in dataContext.Customers
from objView in dataContext.MaterializedView
where objView.Value == && objCustomer.Id == objView.Id
select objCustomer).ToList()

Of course the query is not that simple, but the changes are as mentioned. The query runs in less than a second even if i just joined an other table to the equation.

As for the problem with the entity materialization, im fully aware of. The CLOB column is not present in the SELECT part. Anyway, this is not the problem as both queries are the same and project to the same List of Customers and therefore the entity materialization should be the same for both.
crazypit
 
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Postby StanislavK » Wed 21 Sep 2011 16:41

Sorry for the delay. Besides materialization, the difference in the execution time may be caused by either the preparation of the query or the time needed to open the connection. Provided that these two possibilities do not take place, the performance problem should be caused by slow query execution.

However, we couldn't reproduce such behaviour in our environment. I will send you a letter with a small sample, please specify what should be changed in the sample to reproduce the problem.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)