March 15, 2010 at 12:08 pm
I have trapped a LINQ generated tsql batch using Sql Server Profiler. It uses exec sp_executesql with prepared parameters. It takes about 12 seconds to run the first time then a consistent 7 seconds to pull 921,000 rows. I assume the query plan is being cached.
If I run the same query without using sp_executesql (e.g. find and replace the parameters with real values and remove the call to sp_executesql), the query takes ~335 milliseconds.
Question#1: What kind of overhead is involved in sp_executesql? The difference in duration between the two methods is ridiculous. Is there anything I can do about it?
I found an index that brought the Estimated Subtree Cost for the above query (when viewing the estimated query plan) from ~40 down to 1.8. Executing the query using sp_executesql still takes 7 seconds.
Question#2: Is it incorrect to assume that if the index improves the unparameterized query that it should also prove useful when the db engine is determining the execution plan using sp_executesql?
Thanks,
dnash
March 15, 2010 at 1:08 pm
Could you post execution plans for both queries? Have you tried to wrap the query in a stored proc and pass the parameters to it? The sp_executesql overhead is minimal, the dynamic sql is often a very good alternative to use when dealing with complex where clauses.
One of the things that comes to my mind is implicit data conversion, this can prevent indexes from being used. As in, string may be mapped to nvarchar data type, and you have varchar fields in where clause.
It is hard to say anything without seeing the execution plans.
Regards
Piotr
...and your only reply is slàinte mhath
March 16, 2010 at 7:00 am
sp_executesql is actually one of the best ways to run ad hoc tsql because you can parameterize it and get more plan reuse.
It sounds like, possibly, you're hitting a parameter sniffing issue. But that's just a guess. Post the execution plans as previously requested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 7:10 am
Grant Fritchey (3/16/2010)
sp_executesql is actually one of the best ways to run ad hoc tsql because you can parameterize it and get more plan reuse.It sounds like, possibly, you're hitting a parameter sniffing issue. But that's just a guess. Post the execution plans as previously requested.
I'll second Grant's answer. There is a big difference in what the optimizer does with parameters and scalar values. If you provide a specific value to a query the optimizer can choose the best access path for that value.
The execution plans will show the story.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2010 at 9:17 am
I think if you can change to query to stored procedure and use this procedure in LINQ to...Maybe it can be progresss.....
March 16, 2010 at 9:30 am
Esat,
I think you'd see the same behavior with a stored procedure because the plan would be cached the same as the sp_executesql code is being cached. Also with the project using Linq to SQL stored procedures aren't normally used and it is a pain to use them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2010 at 9:55 am
I think I solved this so I'll update this for future posters.
When disassembling the RPC call and replacing all the parameters in the tsql with real values, I was forced to CAST all my dates to a datetime2(7) since they were string values. I believe that this CAST was preventing the query optimizer from using the indexes I set up since it was not pre-aware of the data types.
Going back to the C# code and setting the LINQ queries to be pre-compiled using CompiledQuery.Compile<> somehow solved the issue. Now my queries come through as SQL:BatchCompleted (in SQL Profiler) rather than RPC:Completed. All dates are now wrapped in a CONVERT function and the queries now make use of the available indexes.
I can't completely explain what is happening here. It seems that something about pre-compiling the query prior to passing it to the database is allowing it to create a query plan that uses available and useful indexes???
Go figure
March 16, 2010 at 10:17 am
I don't think the CAST should have been an issue. I'm not a Linq to SQL Expert, but I've used it and evaluated it and it should be passing the parameters using the DateTime data type. You may have an issue if it is a DateTime2 in the db because I don't know if Linq supports DateTime2 although it should as .NET 3.5 does. Also casting a parameter usually does not have an affect on the query plan, but having to cast the column to match the parameter would be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2010 at 11:24 am
almost certainly parameter sniffing on the initial query, wrong datatype, or widely disparate values leading to suboptimal plan in cache. There are ways to deal with each of those.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 18, 2010 at 4:41 am
TheSQLGuru (3/17/2010)
almost certainly parameter sniffing on the initial query, wrong datatype, or widely disparate values leading to suboptimal plan in cache. There are ways to deal with each of those.
See Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply