stored procedure much slower than query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

stored procedure much slower than query

Hi, We have SQL Server 2000 Standard Edition.
A query that retrieves 11 thousands rows executes for 36 seconds. If we put the same query in a stored procedure, it takes forever. Any ideas?

Can you post the query code? (if its not too big) Also try recompiling the execution plan of the query and see if this takes the same amount of time. Also monitor database locking to see if some other process is blocking this stored procedure. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

is there a hash join or a hash match in the execution plan?
when you say stored procedure, do you mean a stored proc executed from a client or query analyzer?
a stored procedure executed from a client with RPC (Profiler will record the Event class as either SQL Batch or RPC) and the plan has a Hash Join or Hash Match, and the hash has more than ~10K rows, it may spool intermediate results to the tempdb, which could be very slow depending on your system

Both query and stored procedure are executed within query analyzer. The select query uses joins, one union and order by, the query uses only selects – NO inserts, updates, deletes. When it is run as an SQL batch it produces results for about 36 secs. When I put the same query into a stored procedure and I run the procedure (source is the query and nothing more, no transactions) it does not finish ( actually I cancel the execution of the stored proc – i’ve never tried to figure out how long it would take). Locks do not seem to be a problem. Result is the same even if the query is run after server restart with no other users connected. Monitoring locks shows no blocking or blocked processes. Removing the union clause and half of the query doesn’t seem to help. environment is SQL server 2000 standard edition no SPs ,Windows 2000 Server
SP4, at most 2-3 users connected, 1GB of memory , one 1.8 Xeon CPU, plenty of free disk space, users are not running tasks that require lots of resources

If we remove procs’ parameters and declare local variables, the procedure executes as fast as the query.
What is the problem with parameters? There is no type convertion during query execution. Prior to query we convert date to char and in query we use only character comparisons:
set @Date2 = cast(datepart (yyyy,@DateOfEntry) as char(4)) + ‘.’ +
Case
when len(datepart(mm,@DateOfEntry)) = 1 then ‘0’+ cast(datepart(mm,@DateOfEntry) as char(1))
else cast(datepart(mm,@DateOfEntry) as char(2))
end
+ ‘.’ +
Case
when len(datepart(dd,@DateOfEntry)) =1 then ‘0’+ cast(datepart(dd,@DateOfEntry) as char(1))
else cast(datepart(dd,@DateOfEntry) as char(2))
end

It has to do with how the query engine analyzes the parameters. When they have an actual value like when doing a dynamic query or using local variables in an SP the engine knows exactly what values to build the execution plan around. With a stored procedure with parameters the engine does not know the actual values of the parameters when generating the plan and it has to rely in index statistics. Try and run sp_updatestats and see if it helps. It could also be that you have run the SP with parameter X before and that is the plan that is in the cache. Then when you run the stored procedure with parameter Y the cached plan for parameter X might not be good for parameter Y. With stored procedures that use a lot of IF or CASE statments it’s often recommended to use the WITH RECOMPLIE keywords to make it complie a new plan each time it’s run. This is the same for any SP where the actual query within can be different for each run or where the number of rows returned varies a lot (meaning an index scan is sometimes better than a index seek etc.) /Argyle
Search for SQL injection in Google to find the solution for this problem. As Argyle suggested, I would recommend creating the procedure using WITH RECOMPILE option. If this does not help as well, try using index hints in the query. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Thanks guys, sp_updatestats and adding 2 more indexes helped. Now the stored proc executes twice as faster as the query batch was executing before.
]]>