August 4, 2005 at 10:25 am
I have a stored procedure that performs a Select... from 8 tables joined by Left Outer Joins and a Union All... with a Select from 8 other tables (some of which are duplicated from the first 8 tables), 7 of which are joined by Left Outer Joins and the other is joined by an Inner Join.
The procedure takes 20 seconds to return the results of the select.
Updating the Distribution stats has no effect.
If I run the code from the stored procedure as T-SQL it takes < 1 second to return the results.
I have set IO Stats on and have noticed that, for 2 of the tables, the number of logical reads is 3 and 6 and scan count is 1 and 2 when the raw code is run. When the code is run as the stored procedure, the scan counts are still 1 and 2 but the number of logical reads is now 5655 and 60239.
The Scan Counts and Logical Reads were a lot higher before I added an index hint on one of the joins.
Can anyone give me an explanation for this behaviour?
August 4, 2005 at 10:30 am
it is likely the query optimizer is preparing a goofy plan for the procedure.
Have you tried dbcc dropclean buffers?
or run the procedure the with Recompile option?
Or in the procedure. Modify the in parameters like this for example.
create procedure myproc @param1 int
as
declare @Param1a int
set @Param1a = @Param1
select *
from mytable
where col1 = @Param1a
GO
Edit: A little Explaination perhaps what is going on.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EFAA
August 5, 2005 at 12:54 am
Possible reason is that in t-sql your query is using literals, so the optimizer chosed the correct plan.
With stored procedures you are passing the values in, so the optimizer might have chosen the wrong execution plan.
One way to very is to turn on the execution plan for both cases and observe the difference. If necessary you may want to add hints to your stored procedure to influence the optimizer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply