SQL Server Performance Forum – Threads Archive

Same SQL fast in batch script – dog slow in SPROC!

Hi, I have a stored procedure that takes several minutes to run in, but when I cut and pasted the EXACT SAME SQL into a batch script it runs in seconds! If anything I would expect the performance difference to be the other way round. Does anyone have any suggestions for what could cause this behaviour?!?!?
(I have tried running the stored procedure with the WITH RECOMPILE option and it didn’t appear make any difference)
Thanks Max

try creating transcation in ur SP.
Finally committing the transaction will make SQL to run it faster

Don’t forget to include SET NOCOUNT ON. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also make sure that it is exactly the same, i.e. any stored proc parameters should be defined as local variables of the same datatype in your batch script, not replaced with hardcoded values within the SQL as this gives MSSQL more options for index selection Cheers
Twan

I am already setting NOCOUNT ON and the script does contain local variables, instead of hard coded values. I have tried enclosing the query within an explicit transcation, but it doesn’t appear to have made any difference to the data retrieval time within the stored procedure. The script / stored procedure is just a simple SELECT statement with a number of INNER, RIGHT OUTER and LEFT OUTER joins across around 10 tables. One of the tables, ‘tblTransaction’, has 125 million records and two of the tables contain around 5 million records. The query joins tblTransaction to another table and also selects the tblTransaction rows with a ‘TransactionDate’ value between ‘@StartDate’ and ‘@EndDate’ variables. The major difference in execution plan appears to be that while the script uses the join to another table to do a SEEK on a non-clustered index of tblTransaction the stored procedure does a Clustered Index Scan on tblTransaction, looking up records with dates between ‘@StartDate’ and ‘@EndDate’. I have tried specifying the Non-Clustered index for the table with an index hint, but it actually made the performance worse. Does any one know why the query optimiser might be creating a different execution plan for the stored procedure than the one it is creating for the script?? Might it be taking notice of the order in which the tables are listed in the stored procedure?

You can free the cache by using DBCC FREEPROCACHE and recompile the SPs in order to take affect newly plans. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Solved it! I reordered the tables listed in the query with the most selective listed first, and only used left joins instead of a mixture of left and right joins. Thanks for your help on this one. Sorry it was just a case of bad SQL! Max
(Out of interest does anyone know why the script could work out the best execution plan despite the bad table listing, but the stored procedure would stick to the order of the tables?)

]]>