Variables in WHERE clause

I am using SQL Server 2005 and the T-SQL below to select records. If I type 
in the WHERE clause values (as shown) the query time is 1 second or less.  If 
I use the variables @YearStart and @EndDate in the WHERE clause the query 
takes 33 seconds.  Can someone explain and give me an alternative?  Thanks.

DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
DECLARE @YearStart datetime;
DECLARE @ItemId varchar(24);

SET @StartDate = '8/1/2010';
SET @EndDate = '8/31/2010';
SET @YearStart = '1/1/2010';
SET @ItemId = null

	SELECT D.PartId, 
		MIN(I.Descr) AS ItemDescription,
		SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.QtyShipSell 
				ELSE 0 
				END) AS PTDSalesQty, 
		SUM(D.QtyShipSell) AS YTDSalesQty, 
		(SELECT SUM(Q.Qty - Q.RemoveQty - Q.InvoicedQty)
		   FROM MGB.dbo.tblInQtyOnHand Q
		  WHERE Q.ItemId = D.PartId)AS QtyOnHand,
		0 AS CMTD,
		0 AS PurchOnOrd,
		SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.PriceExt 
				ELSE 0 
				END) AS PTDSalesAmt, 
		SUM(D.PriceExt) AS YTDSalesAmt, 
		SUM(D.CostExt) AS YTDCostAmt,
		(SELECT SUM((Q.Qty - Q.RemoveQty - Q.InvoicedQty) * Q.Cost)
		   FROM MGB.dbo.tblInQtyOnHand Q
		  WHERE Q.ItemId = D.PartId) AS OnHandAmt,
		0 AS CMTDAmt,
		0 AS POAmt,
		MIN(I.UsrFld1) AS Platform,
		MIN(I.UsrFld2) AS [Age/Action]
		
	FROM MGB.dbo.tblArHistHeader AS H, 
		MGB.dbo.tblArHistDetail AS D,
		MGB.dbo.tblInItem AS I
	WHERE H.InvcDate BETWEEN '1/1/2010' AND '8/31/2010' 
	  AND H.PostRun = D.PostRun 
	  AND H.TransId = D.TransId 
	  AND D.PartID = I.ItemId
  	  AND (CASE WHEN @ItemId IS NOT NULL AND D.PartId = @ItemId THEN 'T'
			WHEN @ItemId IS NULL AND D.PartId IS NOT NULL THEN 'T'
			ELSE 'F'
			END = 'T')
	GROUP BY D.PartId; 

-- 
David
0
Utf
8/16/2010 1:41:03 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1024 Views

Similar Articles

[PageSpeed] 37

DavidC (dlchase@lifetimeinc.com) writes:
> I am using SQL Server 2005 and the T-SQL below to select records. If I
> type in the WHERE clause values (as shown) the query time is 1 second or
> less.  If I use the variables @YearStart and @EndDate in the WHERE
> clause the query takes 33 seconds.  Can someone explain and give me an
> alternative? 

Normally, SQL Server compiles the batch in one go. This means that if you 
use constants, SQL Server knows the values, and can estimate which the best
plan given these values. But if you use variables, SQL Server does not know 
the values at compile time, and makes a blind estimate of a 20% hitrate. 
This typically results in a different query plan.

An easy way out is to add the hint OPTION (RECOMPILE) at the end of the 
statement, to force SQL Server to recompile the statement, whereupon it will 
see the actual variable values.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/16/2010 3:30:22 PM
When you give an exact value the query generation will generate a plan that 
is specific to the values entered and thus get the most efficient plan.  
However when you pass in variables it will generate a generalized plan which 
might run slower then fixed values.

If the difference is that large it might be indication of index optimization 
is needed and your statistics needs updating.  Check out the execution plan 
for both with fixed values and with variables.

-- 
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/


"DavidC" wrote:

> I am using SQL Server 2005 and the T-SQL below to select records. If I type 
> in the WHERE clause values (as shown) the query time is 1 second or less.  If 
> I use the variables @YearStart and @EndDate in the WHERE clause the query 
> takes 33 seconds.  Can someone explain and give me an alternative?  Thanks.
> 
> DECLARE @StartDate datetime;
> DECLARE @EndDate datetime;
> DECLARE @YearStart datetime;
> DECLARE @ItemId varchar(24);
> 
> SET @StartDate = '8/1/2010';
> SET @EndDate = '8/31/2010';
> SET @YearStart = '1/1/2010';
> SET @ItemId = null
> 
> 	SELECT D.PartId, 
> 		MIN(I.Descr) AS ItemDescription,
> 		SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.QtyShipSell 
> 				ELSE 0 
> 				END) AS PTDSalesQty, 
> 		SUM(D.QtyShipSell) AS YTDSalesQty, 
> 		(SELECT SUM(Q.Qty - Q.RemoveQty - Q.InvoicedQty)
> 		   FROM MGB.dbo.tblInQtyOnHand Q
> 		  WHERE Q.ItemId = D.PartId)AS QtyOnHand,
> 		0 AS CMTD,
> 		0 AS PurchOnOrd,
> 		SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.PriceExt 
> 				ELSE 0 
> 				END) AS PTDSalesAmt, 
> 		SUM(D.PriceExt) AS YTDSalesAmt, 
> 		SUM(D.CostExt) AS YTDCostAmt,
> 		(SELECT SUM((Q.Qty - Q.RemoveQty - Q.InvoicedQty) * Q.Cost)
> 		   FROM MGB.dbo.tblInQtyOnHand Q
> 		  WHERE Q.ItemId = D.PartId) AS OnHandAmt,
> 		0 AS CMTDAmt,
> 		0 AS POAmt,
> 		MIN(I.UsrFld1) AS Platform,
> 		MIN(I.UsrFld2) AS [Age/Action]
> 		
> 	FROM MGB.dbo.tblArHistHeader AS H, 
> 		MGB.dbo.tblArHistDetail AS D,
> 		MGB.dbo.tblInItem AS I
> 	WHERE H.InvcDate BETWEEN '1/1/2010' AND '8/31/2010' 
> 	  AND H.PostRun = D.PostRun 
> 	  AND H.TransId = D.TransId 
> 	  AND D.PartID = I.ItemId
>   	  AND (CASE WHEN @ItemId IS NOT NULL AND D.PartId = @ItemId THEN 'T'
> 			WHEN @ItemId IS NULL AND D.PartId IS NOT NULL THEN 'T'
> 			ELSE 'F'
> 			END = 'T')
> 	GROUP BY D.PartId; 
> 
> -- 
> David
0
Utf
8/16/2010 4:08:03 PM
Reply:

Similar Artilces:

Truncate Table Table variable
Hi, Sql Server 2005 I am using table variable with in a stored procedure and within a table variable i am using identity field. Sometimes I required to truncate the table variable to rseed the identity, but truncate table variable, dbcc checkident are not working. Any idea... Declare @Columns Table(id Int Identity(1,1), [Name] VarChar(155)); Insert Into @Columns([Name]) Select 'A' Union All Select 'B' Select * From @Columns Truncate Table @Columns Insert Into @Columns([Name]) Select 'X' Union All Select 'Y' Select * From @Columns Ra...

Global variables - again
Hi, When I load my first form in the system, it reads in the following parameters from my INI file. User Name the DOS Path to the Data I store these two value in a global variable and only reference them from that point on. I use the user name to read in a row from the tblUserParm table that has all of the settings for that user. I use the DOS data path to get to different sub-directories under my Access db directory. I will also use it to make automatically re-do my links, but I am not at that point yet. Is this an appropriate use of global variables? Is there...

Environment variables
Are Windows XP x64 %environment variables% available to Excel and VB equations? The general answer is yes, but you need to understand that there are different views of the filesystem and registry for 32-bit programs as compared to 64-bit programs and the OS. This also affects things like ODBC sources as well. -- Charlie. http://msmvps.com/blogs/russel <johnsuth@nospam.com.au> wrote in message news:c1.2c.3KklPW$0TR@PC1.BIGPOND.COM... > > Are Windows XP x64 %environment variables% available to Excel and VB > equations? > > ...

TEMP environment variable
Hi, Does somebody know what happens when I write in the 'TEMP' environment variable more than one directory, seperated by a ';' (For example 'C:\windows\temp ; d:\temp')? Does the OSs (98, 2000, XP) know to use BOTH directories as a temporary directories? Regards, Ram ...

Object variable or With block variable not set
I'm trying to put a command button on a form using the command button wizard and I am getting this message "Object variable or With block variable not set". I cannot figure out what's happening. I tried opening different instances of Access and am getting the same problem with some but not all of my .mdb files. Hope someone can help. Thanks, Laura Laura Its hard to think what it would be with no example... did you edit the code generated by the wizard? or is it the wizard producing the error? Can you paste the code here? cheers Jeff "laura" <replyto@gr...

Access Control variable values in one PropertyPg from another Pro
hi all, i am unable to access the property page control values from another property page. // snippet of the code CPropertySheet dlg(_T("Hellow"), this, 0); one a; //First Property Page two b; //second Property Page three c; //third Property Page dlg.AddPage(&a); dlg.AddPage(&b); dlg.AddPage(&c); dlg.SetWizardMode(); dlg.DoModal(); //================================================ if the first property page contains the ComboBox with some values i am trying to acces the values in the second property page as one* m_Main2 = (one *) GetParent(); CString str; fo...

Recordset in excel with variables
I have successfully figured out how to retrieve data from a SQL server from within an excel sheet. The thing I can figure out is how I can use variables in that recordset from data in a particular cell. Example : Select * from inventoryAudit *that works currently in my sheet but I need to add a variable to the query Example : Select * from inventoryAudit Where transfer_dte Between varStart And varEnd * varStart & varEnd would be dates entered into 2 cells on the sheet, than a button would be pushed that would run the query and return the results into the sheet This is what I have...

how to convert a CString variable to unsigned short array one?
Hi, In my unicode VC6 app, how to convert a CString variable to unsigned short array? ....... CString strRst; unsigned short strOleChars[100]; strOleChars = strRst; //??? Thank you. Is ConvertStringToBSTR() what you are looking for? AliR. "David" <David_Wang_Xian@hotmail.com> wrote in message news:e6gbA3avFHA.720@TK2MSFTNGP15.phx.gbl... > Hi, > In my unicode VC6 app, how to convert a CString variable to unsigned short > array? > ...... > CString strRst; > unsigned short strOleChars[100]; > strOleChars = strRst; //??? > > Thank you. > > ...

Variable instances
Hi, I just want to check that i have the correct understanding of the following premise. I have a Class , say Class A that i use as a base class for two other classes, say Class B and Class C. I declare instance sof each class as follows. ClassB p_BVar = new ClassB( ); ClassC p_CVar = new ClassC ( ); Each class inherits a member variable from the Base Class called bool bStatus; Now if i change this variable using p_BVar->bStaus = .... this change should not be relected in p_CVar since p_BVar and p_CVar are two separate instances and do not point to the same address. Is this a corr...

Referencing Variable Name Worksheets
I'm updating a formatted worksheet every day. I have a macro that processes, lets the user augment, and then saves that days data into a new worksheet with that days date. I need to reference that worksheet the next business day to error check. I cannot : PREVIOUS DATE IS IN CELL A1 s = range("a1").value worksheets(s).select Thank you for any help/suggestions Chris Cameron You might try sheets(1) sheet1 -- Don Guillett SalesAid Software donaldb@281.com "camerons" <camerons@epud.net> wrote in message news:117nvk3b9e8mg06@c...

typed variables
I just now noticed there is a big difference in coding $i = [int] 9.99 and [int] $i = 9.99 PS C:> $i = [int] 9.99 PS C:> $i 10 PS C:> $i = 9.99 PS C:> $i 9.99 PS C:> [int] $i = 9.99 PS C:> $i 10 PS C:> $i = 9.99 PS C:> $i 10 How would I take away the type binding from $i without deleting the variable once I associate a type with it? - Larry you can change the type [double]$i = 9.99 Typing as an integer and then giving it a non-integer value could cause issues with your logic -- Ric...

How to evaluate VB constant name in string variable?
Greetings! Example: the value of vbOkay within the VBE is 6. {i = vbOkay} yields i = 6. Would like a construct where {sMyString = "vbOkay" : i = UnknownFunction(sMyString)} yields i = 6 TIA! George Try i = VAL(sMyString) -- Regards, Nigel nigelnospam@9sw.co.uk "G Lykos" <GLykos@CompuServe.com> wrote in message news:%23iVJr8tqKHA.3408@TK2MSFTNGP06.phx.gbl... > Greetings! > > Example: the value of vbOkay within the VBE is 6. {i = vbOkay} yields i > = 6. > > Would like a construct where {sMyString = &q...

? Using environment variables in filenames
Hi, I have a bit of code where I use a CFile to check to see if the file designated by the filename in m_File exists. The problem is that if I put a variable in the filename ie, %systemroot%\system32\calc.exe in m_File, and open it with CFile, then it returns a file not found error. How can I get around this? -- Alec alec@synetech.cjb.net > I have a bit of code where I use a CFile to check to see if the file >designated by the filename in m_File exists. The problem is that if I put a >variable in the filename ie, %systemroot%\system32\calc.exe in m_File, and ...

Variables/Bookmarks in powerpoint
Hi, is it possible to create bookmarks in powerpoint like in Word. I want to reference the title and date from the title slide in other slides without retyping it. Thanks. Dwayne On 3/1/10 1:55 PM, Dwayne Roberts wrote: > Hi, > > is it possible to create bookmarks in powerpoint like in Word. I want to > reference the title and date from the title slide in other slides > without retyping it. > > Thanks. > Dwayne I'm not sure what you mean by a bookmark in this case. A bookmark in Word takes you to a specific place in a document. The only ...

SSIS Variables and variables. What is best practise.
Dear reader, At the moment I am strugling with the deployment and configuration of a set of SSIS packages. I have one package [A] which calls the packages A1, A2 and A3. Each package starts with reading the parameters from a database. I found a script on the web which does read all the matching parameters from the database into the package. (http://blog.boxedbits.com/archives/8). Thanks for that script. But each script (A1,A2,A3) still needs the location of the database where to read the parameters, I use to variables for that, one for the servername, one for the databa...

Define a variable problem
Can anyway advise me on how to define a variable in Excel. I did basic at school as a kid and I wanted to define a variable and name it at the beginning of the spreadsheet and then use the formulas to alter the variable as we move through the sheets. I'm modeling financial spreadsheets, so I might want to add 100 to the variable sales at time t+1 or something. Can anyone help or suggest how I might get started. Many thanks in advance Alex You could name a cell, and enter the variable in that cell. For example: Select cell A1 Click in the Name Box, to the left of the formula bar. Type a...

Input variable contains a duplicate JournalId in Transaction Work
Hi All, I am using Web Service to CreateGLTransaction but I have a problem of Duplicated JournalId even I use Random Number for JournalId. Anyone knows how to generate JournalId without duplicated number. Thanks, -- Toi Day ...

Using Variables in Links
I have a multiple worksheet workbook that pulls data in from several other worksheets in workbooks. Each worksheet in my workbook is consistent in look and format except for the data it shows - each sheet represents a different cost center number. I am referencing other files that use the same scheme, e.g., "'c:\accounting\[workbook]9440'!A1" pulls data from "'s:\data\[masterfile]9440'!B762". Is there a way to use variables in the filename/reference? I want to insert the worksheet name (9440, 9884,3325, etc.) displayed on the tab into the referenc...

How to get one variable to equal the value of a changing variable (its hard to explain in the subject)
Dim CurrMenu as ???Dim MenuNo As IntegerDim Menu1ItemCount as Integer, Dim Menu2ItemCount as IntegerMenu1ItemCount = 1Menu2ItemCount = 3Menu3ItemCount = 5 etcDo While MenuNo < whatever MenuNo = 1 CurrMenu = "Menu" & Str(MenuNo) & "ItemCount" & ".Value" MenuNo 1LoopWhat I'm trying to do is set CurrMenu to Menu1ItemCount (not the textbut the value of the variable). How do I do that? I've tried to putwords together so that it equals 'Menu1ItemCount.Value' but obviouslythat does not work because it thinks its text, not the valu...

INDEX and 2 MATCHES (of which 1 variable)
Hello, I'm stucked with the following: I try to retrieve a result (column C)via a combination of a INDEX and two MATCH functions, the first MATCH needs to be matched exactly but the second MATCH has to be defined as the closest match. Sample: A B C 1 January 2005 Low Forecast PE <V,Lcl> 5 2 January 2005 Small Cap <S,lcl> 6 3 January 2005 High EBIT/EV <V,Lcl> 7 4 February 2005 Low Forecast PE <V,Lcl> 5 5 February 2005 Small Cap <S,lcl> 6 6 February 2005 High EBIT/EV &l...

LINQ and Where clause
Hello everyone, I need to know how to use a WHERE clause to weed out elements that are not present. If I remove the where altogether and all elements are present app runs as should. However, sometimes the element "ThumbNail" is not present and app fails - stories not set. I have tried: Where story.Element("thumbnail") is not nothing Where story.Element("thumbnail") <> "" Where story.Element("thumbnail") not nothing What is missing? I have included the sub in question and also an example xml file. If you look at the xml, one story i...

Declaring Public Variables from Another Program (Word 2007)
I want to declare a Public Variable in Word 2007 from another program (in this case, Access 2007). How is this done? LA Lawyer wrote: > I want to declare a Public Variable in Word 2007 from another program (in > this case, Access 2007). How is this done? With a FOIA request? Only partly in jest. Don't think this is gonna happen. Unless you can articulate a bit clearer what you're trying to do. -- ..NET: It's About Trust! http://vfred.mvps.org If I was doing this in Word, I would do something like: Public Constant NameOfContact as String = ...

List of receipt variables for the POS
I thought I would share with the group a complete list of the RMS POS receipt variables. Now you can modify your receipt files with ease. Moe Cashier.Name Cashier's full name, first & last Cashier.Firstname Cashier's first name only Cashier.ID ID in the cashier table for this cashier Cashier.Loaded Returns true when a cashier has been loaded. Cashier.Number Cashier's Number Store.Name Self-explanatory Store.Address1 Self-explanatory Store.Address2 Self-explanatory Store.City Self-explanatory Store.State Self-explanatory Store.Zip Self-explanator...

Prompt for variable
I have a main spreadsheet that is linked to other Spreadsheets. I receive these other spreadsheets daily (i.e. MMDDYYYY.xls). I would like to have the main spreadsheet prompt me for file name and then replace all links within the main spreadsheet to point to the filename entered. Thanks in advance for your feedback! --- Message posted from http://www.ExcelForum.com/ Below is a macro for changing all links in a spreadsheet. Sub Change_Links( Dim CurrLink, NextLink As Strin '*****Option 1 Promt User for Path Name***** NextLink = InputBox("Enter File Name and Directory...

How to graph x-variable versus y-variable in Excel ?
I feel this is a very basic challenge .... but it seems beyond my ability ...... I after much trial and error .... i humbly approach the masters' feet !!! I have tried to draw simple graphs plotting x versus y and i am having difficulties because the choices are not in excel "chart layout" to plot simple quadratic functions. I'm trying to plot the various flows in a river versus the percent time they are exceeded. i have my columns of figures but I cannot find the correct "chart layout". I imagine this is a very basic challenge because this is what we used to...