SQL Server Performance Forum – Threads Archive
script execution problem
G’day all, Wondering if anyone out there can tell me why executing SQL script in Query Analyzer takes 30+ seconds when using a variables and < 1 second when hardcoding? With the below script there are three lines as follows: — AND Ebco_ServerDate >= @x— AND Ebco_ServerDate >= @Cust_LastExport
— AND Ebco_ServerDate >= ‘2005-05-31 19:00:27.000’ first, the "Ebco_ServerDate" field in the table is a datetime field. Now when I use either "@x" or "@Cust_LastExport" this script takes 30+ seconds, evern though the value of the two variables is "2005-05-31 19:00:27.000". When I use the date hardcoded as in the third line, the script executes in a split second. Here’s the who script code:
DECLARE
@Cust_LastExportdatetime,
@xvarchar(20) SET @Cust_LastExport = (SELECT Cust_LastExport FROM Sys_Cust)
SET @Cust_LastExport = DATEADD(day,-1, @Cust_LastExport)
SET @x = cast(@Cust_LastExport as varchar(20))
SET @x = ‘2005-05-31 19:00:27’ select @x, @Cust_LastExport — before collecting data first ensure all data is correct
CREATE TABLE #TempQueuedEbcoRecordsForExport(
Lineint identity(1,1),
Ebco_linkint,
Emp_Numvarchar(50),
Emp_Linkint,
Job_numvarchar(50),
Job_Linkint,
Oper_numvarchar(50),
Oper_Linkint,
Work_numvarchar(50),
Work_Linkint
) INSERT INTO #TempQueuedEbcoRecordsForExport (Ebco_link, Emp_Num, Emp_Link, Job_num, Job_Link, Oper_num, Oper_Link, Work_num, Work_Link)
SELECT eb.Ebco_link, Emp_Num, Emp_Link, Job_num, Job_Link, Oper_num, Oper_Link, Work_num, Work_Link
FROM Sys_Ebco eb
WHERE Ebco_IsQueued = 0
AND NOT (Approval_Time IS NULL)
AND Job_Num NOT IN(‘UNPAID_BREAK’, ‘9999’)
AND Ebco_TCode = ‘J’
— AND Ebco_ServerDate >= @x
— AND Ebco_ServerDate >= @Cust_LastExport
AND Ebco_ServerDate >= ‘2005-05-31 19:00:27.000’
AND Ebco_MatchingLink > 0
AND Ebco_Link NOT IN (SELECT ex.Ebco_link FROM Export_VM_Sys_Ebco ex WHERE ex.Ebco_link = eb.Ebco_link and Transaction_ID > 0)
ORDER BY Ebco_Date, eb.Ebco_Link select * from #TempQueuedEbcoRecordsForExport
drop TABLE #TempQueuedEbcoRecordsForExport Thanks to anyone who can off any advice. Cheers,
brendan
What is the execution plan for each of these query. Probably the query optimizer did not use the index with the variable.
When running the execution plan I do know that it was taking a long time to do the Clustered Index scan. Right now I’m removing the clustered index then re-adding it to see if the problem goes away. I did previously rebuilt the index which had no effect! If I execute the scritp on a colegues database it takes a split second for all three examples. So I’m more leaning towards there is something wrong with my SQL Server.
The query optimiser uses statistics to determine wether it should use the index or not depending of your criteria. In your case, the 2 possibilities are an index seek plus a bookmark or a clustered index scan. My guess is the query optimiser does not evaluate the amount of rows the index seek would return and it is why it executes the clustered index scan (it happens with fucntion). Is your colegue database exactly the same?
first off thanks for all the ideas as they are much appreciated. with your question about my collegues system, yes the database structure and indexes are the same though the data itself is different. I made sure I was testing it with nearly equivelent amount of data. I’ve been doing a few things in the meantime:
– I deleted the clustered index that the execution plan pointed out as being the bottle neck.
– I re-tested the script with the same long duration result.
– I re-added the clustered index
– I re-tested the script with the same long duration result. One other thing that was interesting is that I stoped then restarted SQL Server. When it was restarted the SQL Server memory was at 18Meg. As soon as I executed the scipt, the memory quickly climed to 170Meg and stayed there for about 10 seconds until the script finished executing. Does this seem normal to you? Cheers,
brendan
]]>