SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Why is SQL Server no re-using Query Plan
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 01/20/2014 :  12:10:44  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 01/20/2014 :  14:47:25  Show Profile  Reply with Quote
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.

Edited by - ScottPletcher on 01/20/2014 14:48:33
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 01/20/2014 :  14:51:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Is their tool using prepared statements or adhoc queries? If adhoc queries, you could try this option:
http://technet.microsoft.com/en-us/library/cc645587.aspx

How big is the plan cache?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/25/2014 :  15:16:51  Show Profile  Reply with Quote
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!

Edited by - Kristen on 01/25/2014 15:23:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000