Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2014-01-20 : 12:10:44
|
I have a query that is a little complex from another Department but let me simplify it as Q1:select * from Table1 where UserID = 'x' then I run Q2:select * from Table1 where UserID = 'y' Q1 runs slow on a clean cache and then fast on subsequent runs. Than I try Q2 without clearing the cache and it runs slow as well on the first run and fast on subsequent runs.I first thought that the issue was bringing data into the cache but with SET STATISTICS TIME ON, it seems that it is compile time which is taking longer. Then I checked in dm_exec_query_stats and I see SQL server made a separate plan for Q1 and Q2.Is there a way to force SQL Server to use the same query plan for Q1 and Q2? With a stored procedure it would since the UserIDs, 'x' or 'y' would be parameters. But this department said because of the tool they are using, they can't use Stored Procedures. |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-01-20 : 14:47:25
|
| You need to specify the table schema:select * from dbo.Table1 where UserID = 'x'Assuming you've allowed SQL to autoparameterize queries, I would think it would then do so for that query. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-25 : 15:16:51
|
If the code is being generated as adhoc SQL (e.g. from Application), I've had good results by getting the DEVs to change to use sp_ExecuteSQL instead. Usually when the DEVs are bolting together the SQL with string handling it isn't difficult for them to generate strings with @Parameters in place of SQL 'string constants' (i.e. representing data in the APP).I'm sure you know this, but sp_ExecuteSQL is happy to be given a bunch of parameters, regardless of whether they appear in the query, so adhoc code generation can just build the parameterised SQL, and then use the same everything-you-could-possibly-need parameter definition etc.Provided that some of the adhoc SQL strings repeat their patterns then my experience has been that there is a marked improvement in performance. The more the better of course  If you need me to elaborate pls just ask.If this isn't adhoc SQL then please ignore! |
|
|
|
|
|
|
Why is SQL Server no re-using Query Plan