Different Execution Plans followed between Linq and SQL text
Posted: 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?
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?