Why is Stored Proc plan slower than query plan  
Author Message
Muse





PostPosted: Wed Jan 21 01:38:34 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan

I need some advice on how to go about diagnosing why a stored
procedure execution plan is different than running the underlying
query alone. I am not using any parameters and it should not be a
recompilation issue because whenever I take this query and run it
alone it ALWAYS produces a better/faster plan than if I create a new
stored procedure that takes no params and just runs the query.

The query is complex and has multiple sub-queries that contain
aggregates so I assume that may be a contributing factor. Can someone
please give me some direction on finding out why the stored proc is
choosing a different plan.

I won't post the entire plan now as it is very long and I doubt anyone
would want to read them, but the first place they differ is where the
better plan does a "WITH PREFETCH" while the slower plan does not:

Good plan-- Nested Loops(Inner Join, OUTER
REFERENCES:([table1].[column1]) WITH PREFETCH)
bad plan-- Nested Loops(Inner Join, OUTER
REFERENCES:([table1].[column1]))

after that they go in different directions

Thanks for any help.

Using Sql 2000 on Windows 2000

SQL Server263  
 
 
Uri





PostPosted: Wed Jan 21 01:38:34 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan Lou
Did you try to update statistics?




> I need some advice on how to go about diagnosing why a stored
> procedure execution plan is different than running the underlying
> query alone. I am not using any parameters and it should not be a
> recompilation issue because whenever I take this query and run it
> alone it ALWAYS produces a better/faster plan than if I create a new
> stored procedure that takes no params and just runs the query.
>
> The query is complex and has multiple sub-queries that contain
> aggregates so I assume that may be a contributing factor. Can someone
> please give me some direction on finding out why the stored proc is
> choosing a different plan.
>
> I won't post the entire plan now as it is very long and I doubt anyone
> would want to read them, but the first place they differ is where the
> better plan does a "WITH PREFETCH" while the slower plan does not:
>
> Good plan-- Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[column1]) WITH PREFETCH)
> bad plan-- Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[column1]))
>
> after that they go in different directions
>
> Thanks for any help.
>
> Using Sql 2000 on Windows 2000


 
 
Gert-Jan





PostPosted: Wed Jan 21 09:08:34 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan Lou,

there is not enough information in your post to give anything more than
general advice.

When comparing the plans, make sure the procedure cache has been
emptied. You can use
DBCC FREEPROCCACHE
for that.

As Uri mentioned, make sure your statistics are up to date.

Make sure you have an exact comparison. For example, you might have
SELECT * FROM MyTable WHERE SomeColumn = 5
in QA, but

in the stored procedure. For SQL-Server these two are very different.

You may also want to try creating the stored procedure with the WITH
RECOMPILE keyword.

If you need more information, you should post the actual queries and
relevant background information (which might include DDL, sample rows,
output of STATISTICS IO and STATISTICS TIME, actual query plans, etc.)

HTH,
Gert-Jan



>
> I need some advice on how to go about diagnosing why a stored
> procedure execution plan is different than running the underlying
> query alone. I am not using any parameters and it should not be a
> recompilation issue because whenever I take this query and run it
> alone it ALWAYS produces a better/faster plan than if I create a new
> stored procedure that takes no params and just runs the query.
>
> The query is complex and has multiple sub-queries that contain
> aggregates so I assume that may be a contributing factor. Can someone
> please give me some direction on finding out why the stored proc is
> choosing a different plan.
>
> I won't post the entire plan now as it is very long and I doubt anyone
> would want to read them, but the first place they differ is where the
> better plan does a "WITH PREFETCH" while the slower plan does not:
>
> Good plan-- Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[column1]) WITH PREFETCH)
> bad plan-- Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[column1]))
>
> after that they go in different directions
>
> Thanks for any help.
>
> Using Sql 2000 on Windows 2000
 
 
Lou





PostPosted: Wed Jan 21 13:45:30 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan Thank you all for the responses. My mistake for not being clear in my
first post. I have tried all of the usual solutions such as making sure
stats are updated, all caches emptied, and verifying that it is not a
recompilation issue.

There are no differences whatsoever between the standalone query and the
query within the stored pocedure. It is a single SELECT statement (just
with many sub-queries, aggregates, and case statements). Therefore
neither is using any variables or parameters of any kind, no views, no
user functions, no **** variables or user types. And there is no DDL.

I suspect this is an issue that has to do with the internals of the
optimizer. So I am just really curious if there are any known reasons
why the optimizer chooses a different plan when running the EXACT same
select statement within a stored procedure.

Thanks,
Lou

Here is the query that is used in both situations (I know that I have a
variety of choices in re-writing the query if need be, but this query
runs fine and in less than one second; but if it is cut and pasted into
a "create procedure" command the resulting stored proc runs in 19
seconds). So mostly I am just wondering if someone knows some of the
black magic of the optimizer with regards to stored procs.


SELECT "MainID" = tShowhotelRooms.ShowHotelID, HotelName, "Name" =
RoomTypeDesc,
"Description" = RTRIM(RoomClassDesc), Night,
MRN.Allocated, MRN.Sold, "Available" = CASE WHEN (ParentID =
Null AND UseAsPool = 0) THEN

Allocated-sold

WHEN (ParentID = Null AND UseAsPool<>0) THEN
Allocated - (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN
tShowHotelRooms SHR
ON SRN.ShowRoomID = SHR.ShowRoomID
WHERE SRN.Night = MRN.Night
AND (SRN.ShowRoomID = tShowHotelRooms.ShowRoomID
OR SHR.ParentID = tShowhotelRooms.ShowRoomID))
WHEN (ParentID <> NULL AND UseAsPool = 0) THEN
(SELECT Allocated FROM tRoomNights SRN
WHERE SRN.ShowrOomID = tShowhotelRooms.ParentID
AND SRN.Night = MRN.Night) -
(Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN tShowHotelRooms SHR
ON SRN.ShowRoomID = SHR.ShowRoomID
WHERE SRN.Night = MRN.Night

AND (SRN.ShowRoomID = tShowHotelRooms.ParentID
OR SHR.ParentID = tShowhotelRooms.ParentID))

ELSE Allocated-Sold
END,
tShowHotelRooms.RoomTypeID, tShowHotelRooms.RoomClassID,
MRN.Rate
FROM tRoomNights MRN
JOIN tShowHotelRooms ON MRN.ShowRoomID =
tSHowHOtelRooms.ShowRoomID
JOIN tRoomTypes ON tShowHotelRooms.RoomTypeID =
tRoomTypes.RoomTypeID
JOIN tRoomClasses ON tShowHotelRooms.RoomClassID =
tRoomClasses.RoomClassID
JOIN tShowhotels ON tShowhotelRooms.ShowhotelID =
tShowhotels.ShowhotelID
WHERE ShowID = 50 ORDER BY Night


>
> Lou,
>
> there is not enough information in your post to give anything more than
> general advice.
>
> When comparing the plans, make sure the procedure cache has been
> emptied. You can use
> DBCC FREEPROCCACHE
> for that.
>
> As Uri mentioned, make sure your statistics are up to date.
>
> Make sure you have an exact comparison. For example, you might have
> SELECT * FROM MyTable WHERE SomeColumn = 5
> in QA, but

> in the stored procedure. For SQL-Server these two are very different.
>
> You may also want to try creating the stored procedure with the WITH
> RECOMPILE keyword.
>
> If you need more information, you should post the actual queries and
> relevant background information (which might include DDL, sample rows,
> output of STATISTICS IO and STATISTICS TIME, actual query plans, etc.)
>
> HTH,
> Gert-Jan
>

> >
> > I need some advice on how to go about diagnosing why a stored
> > procedure execution plan is different than running the underlying
> > query alone. I am not using any parameters and it should not be a
> > recompilation issue because whenever I take this query and run it
> > alone it ALWAYS produces a better/faster plan than if I create a new
> > stored procedure that takes no params and just runs the query.
> >
> > The query is complex and has multiple sub-queries that contain
> > aggregates so I assume that may be a contributing factor. Can someone
> > please give me some direction on finding out why the stored proc is
> > choosing a different plan.
> >
> > I won't post the entire plan now as it is very long and I doubt anyone
> > would want to read them, but the first place they differ is where the
> > better plan does a "WITH PREFETCH" while the slower plan does not:
> >
> > Good plan-- Nested Loops(Inner Join, OUTER
> > REFERENCES:([table1].[column1]) WITH PREFETCH)
> > bad plan-- Nested Loops(Inner Join, OUTER
> > REFERENCES:([table1].[column1]))
> >
> > after that they go in different directions
> >
> > Thanks for any help.
> >
> > Using Sql 2000 on Windows 2000
 
 
Gert-Jan





PostPosted: Wed Jan 21 14:50:48 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan I agree that the query plans should be the same.

The only non-standard thing I noticed about the query was the NULL
comparison (= NULL instead of IS NULL, and <> NULL instead of IS NOT
NULL). In theory these kinds of things (in combination with connection
setting) can influence query plans.

Other than that, I don't have a clue...

Gert-Jan



>
> Thank you all for the responses. My mistake for not being clear in my
> first post. I have tried all of the usual solutions such as making sure
> stats are updated, all caches emptied, and verifying that it is not a
> recompilation issue.
>
> There are no differences whatsoever between the standalone query and the
> query within the stored pocedure. It is a single SELECT statement (just
> with many sub-queries, aggregates, and case statements). Therefore
> neither is using any variables or parameters of any kind, no views, no
> user functions, no **** variables or user types. And there is no DDL.
>
> I suspect this is an issue that has to do with the internals of the
> optimizer. So I am just really curious if there are any known reasons
> why the optimizer chooses a different plan when running the EXACT same
> select statement within a stored procedure.
>
> Thanks,
> Lou
>
> Here is the query that is used in both situations (I know that I have a
> variety of choices in re-writing the query if need be, but this query
> runs fine and in less than one second; but if it is cut and pasted into
> a "create procedure" command the resulting stored proc runs in 19
> seconds). So mostly I am just wondering if someone knows some of the
> black magic of the optimizer with regards to stored procs.
>
> SELECT "MainID" = tShowhotelRooms.ShowHotelID, HotelName, "Name" =
> RoomTypeDesc,
> "Description" = RTRIM(RoomClassDesc), Night,
> MRN.Allocated, MRN.Sold, "Available" = CASE WHEN (ParentID =
> Null AND UseAsPool = 0) THEN
>
> Allocated-sold
>
> WHEN (ParentID = Null AND UseAsPool<>0) THEN
> Allocated - (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN
> tShowHotelRooms SHR
> ON SRN.ShowRoomID = SHR.ShowRoomID
> WHERE SRN.Night = MRN.Night
> AND (SRN.ShowRoomID = tShowHotelRooms.ShowRoomID
> OR SHR.ParentID = tShowhotelRooms.ShowRoomID))
> WHEN (ParentID <> NULL AND UseAsPool = 0) THEN
> (SELECT Allocated FROM tRoomNights SRN
> WHERE SRN.ShowrOomID = tShowhotelRooms.ParentID
> AND SRN.Night = MRN.Night) -
> (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN tShowHotelRooms SHR
> ON SRN.ShowRoomID = SHR.ShowRoomID
> WHERE SRN.Night = MRN.Night
>
> AND (SRN.ShowRoomID = tShowHotelRooms.ParentID
> OR SHR.ParentID = tShowhotelRooms.ParentID))
>
> ELSE Allocated-Sold
> END,
> tShowHotelRooms.RoomTypeID, tShowHotelRooms.RoomClassID,
> MRN.Rate
> FROM tRoomNights MRN
> JOIN tShowHotelRooms ON MRN.ShowRoomID =
> tSHowHOtelRooms.ShowRoomID
> JOIN tRoomTypes ON tShowHotelRooms.RoomTypeID =
> tRoomTypes.RoomTypeID
> JOIN tRoomClasses ON tShowHotelRooms.RoomClassID =
> tRoomClasses.RoomClassID
> JOIN tShowhotels ON tShowhotelRooms.ShowhotelID =
> tShowhotels.ShowhotelID
> WHERE ShowID = 50 ORDER BY Night
>
<snip>
 
 
lgo88





PostPosted: Tue Jan 27 11:12:46 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan Thank you for the replies Gert-Jan.

I did an interesting test and replaced my non-standard Null comparison
syntax with the proper IS NULL and IS NOT NULL. By doing this the
standalone query now uses the same inefficient plan that the stored
procedure was using before. So my assumption is that the stored
procedure compilation process seems to internally treat the older
"column = null" syntax as if it were "column IS NULL". [can anyone
confirm this to be true?].

But I still don't see why the standalone query chooses a better plan
when using the non-standard syntax. This would only make sense if
those comparisons were being short-circuited because the optimizer was
assuming that the comparison would always return FALSE. But that is
not happening because the results of the query clearly indicate to me
that each branch of the CASE statement is returning true at least
once.

Is it possible that the optimizer has more flexibility in finding a
better plan when using the non-standard "column = null" syntax?



> I agree that the query plans should be the same.
>
> The only non-standard thing I noticed about the query was the NULL
> comparison (= NULL instead of IS NULL, and <> NULL instead of IS NOT
> NULL). In theory these kinds of things (in combination with connection
> setting) can influence query plans.
>
> Other than that, I don't have a clue...
>
> Gert-Jan
>
>

> >
> > Thank you all for the responses. My mistake for not being clear in my
> > first post. I have tried all of the usual solutions such as making sure
> > stats are updated, all caches emptied, and verifying that it is not a
> > recompilation issue.
> >
> > There are no differences whatsoever between the standalone query and the
> > query within the stored pocedure. It is a single SELECT statement (just
> > with many sub-queries, aggregates, and case statements). Therefore
> > neither is using any variables or parameters of any kind, no views, no
> > user functions, no **** variables or user types. And there is no DDL.
> >
> > I suspect this is an issue that has to do with the internals of the
> > optimizer. So I am just really curious if there are any known reasons
> > why the optimizer chooses a different plan when running the EXACT same
> > select statement within a stored procedure.
> >
> > Thanks,
> > Lou
> >
> > Here is the query that is used in both situations (I know that I have a
> > variety of choices in re-writing the query if need be, but this query
> > runs fine and in less than one second; but if it is cut and pasted into
> > a "create procedure" command the resulting stored proc runs in 19
> > seconds). So mostly I am just wondering if someone knows some of the
> > black magic of the optimizer with regards to stored procs.
> >
> > SELECT "MainID" = tShowhotelRooms.ShowHotelID, HotelName, "Name" =
> > RoomTypeDesc,
> > "Description" = RTRIM(RoomClassDesc), Night,
> > MRN.Allocated, MRN.Sold, "Available" = CASE WHEN (ParentID =
> > Null AND UseAsPool = 0) THEN
> >
> > Allocated-sold
> >
> > WHEN (ParentID = Null AND UseAsPool<>0) THEN
> > Allocated - (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN
> > tShowHotelRooms SHR
> > ON SRN.ShowRoomID = SHR.ShowRoomID
> > WHERE SRN.Night = MRN.Night
> > AND (SRN.ShowRoomID = tShowHotelRooms.ShowRoomID
> > OR SHR.ParentID = tShowhotelRooms.ShowRoomID))
> > WHEN (ParentID <> NULL AND UseAsPool = 0) THEN
> > (SELECT Allocated FROM tRoomNights SRN
> > WHERE SRN.ShowrOomID = tShowhotelRooms.ParentID
> > AND SRN.Night = MRN.Night) -
> > (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN tShowHotelRooms SHR
> > ON SRN.ShowRoomID = SHR.ShowRoomID
> > WHERE SRN.Night = MRN.Night
> >
> > AND (SRN.ShowRoomID = tShowHotelRooms.ParentID
> > OR SHR.ParentID = tShowhotelRooms.ParentID))
> >
> > ELSE Allocated-Sold
> > END,
> > tShowHotelRooms.RoomTypeID, tShowHotelRooms.RoomClassID,
> > MRN.Rate
> > FROM tRoomNights MRN
> > JOIN tShowHotelRooms ON MRN.ShowRoomID =
> > tSHowHOtelRooms.ShowRoomID
> > JOIN tRoomTypes ON tShowHotelRooms.RoomTypeID =
> > tRoomTypes.RoomTypeID
> > JOIN tRoomClasses ON tShowHotelRooms.RoomClassID =
> > tRoomClasses.RoomClassID
> > JOIN tShowhotels ON tShowhotelRooms.ShowhotelID =
> > tShowhotels.ShowhotelID
> > WHERE ShowID = 50 ORDER BY Night
> >
> <snip>
 
 
Gert-Jan





PostPosted: Tue Jan 27 14:10:59 CST 2004 Top

SQL Server Developer >> Why is Stored Proc plan slower than query plan Lou,

you realize that you have now changed your question completely.
Apparently, the reasonn for the difference in query plan was caused by
the non-standard handling of NULLs.

You might want to play around with the SET ANSI_NULLS command and see
where that leads you.

Another approach is to review the indexing of the tables. Using the
Index Tuning Wizard could be a start for that.

Last tip: if possible, then simplify the query. SQL-Server might be able
to better optimize the statement if it did not have to process the CASE
expression, but could execute the 'Allocated' and 'Sold' calculation for
all (relevant) rows.

Hope this helps,
Gert-Jan



>
> Thank you for the replies Gert-Jan.
>
> I did an interesting test and replaced my non-standard Null comparison
> syntax with the proper IS NULL and IS NOT NULL. By doing this the
> standalone query now uses the same inefficient plan that the stored
> procedure was using before. So my assumption is that the stored
> procedure compilation process seems to internally treat the older
> "column = null" syntax as if it were "column IS NULL". [can anyone
> confirm this to be true?].
>
> But I still don't see why the standalone query chooses a better plan
> when using the non-standard syntax. This would only make sense if
> those comparisons were being short-circuited because the optimizer was
> assuming that the comparison would always return FALSE. But that is
> not happening because the results of the query clearly indicate to me
> that each branch of the CASE statement is returning true at least
> once.
>
> Is it possible that the optimizer has more flexibility in finding a
> better plan when using the non-standard "column = null" syntax?
>

> > I agree that the query plans should be the same.
> >
> > The only non-standard thing I noticed about the query was the NULL
> > comparison (= NULL instead of IS NULL, and <> NULL instead of IS NOT
> > NULL). In theory these kinds of things (in combination with connection
> > setting) can influence query plans.
> >
> > Other than that, I don't have a clue...
> >
> > Gert-Jan
> >
> >

> > >
> > > Thank you all for the responses. My mistake for not being clear in my
> > > first post. I have tried all of the usual solutions such as making sure
> > > stats are updated, all caches emptied, and verifying that it is not a
> > > recompilation issue.
> > >
> > > There are no differences whatsoever between the standalone query and the
> > > query within the stored pocedure. It is a single SELECT statement (just
> > > with many sub-queries, aggregates, and case statements). Therefore
> > > neither is using any variables or parameters of any kind, no views, no
> > > user functions, no **** variables or user types. And there is no DDL.
> > >
> > > I suspect this is an issue that has to do with the internals of the
> > > optimizer. So I am just really curious if there are any known reasons
> > > why the optimizer chooses a different plan when running the EXACT same
> > > select statement within a stored procedure.
> > >
> > > Thanks,
> > > Lou
> > >
> > > Here is the query that is used in both situations (I know that I have a
> > > variety of choices in re-writing the query if need be, but this query
> > > runs fine and in less than one second; but if it is cut and pasted into
> > > a "create procedure" command the resulting stored proc runs in 19
> > > seconds). So mostly I am just wondering if someone knows some of the
> > > black magic of the optimizer with regards to stored procs.
> > >
> > > SELECT "MainID" = tShowhotelRooms.ShowHotelID, HotelName, "Name" =
> > > RoomTypeDesc,
> > > "Description" = RTRIM(RoomClassDesc), Night,
> > > MRN.Allocated, MRN.Sold, "Available" = CASE WHEN (ParentID =
> > > Null AND UseAsPool = 0) THEN
> > >
> > > Allocated-sold
> > >
> > > WHEN (ParentID = Null AND UseAsPool<>0) THEN
> > > Allocated - (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN
> > > tShowHotelRooms SHR
> > > ON SRN.ShowRoomID = SHR.ShowRoomID
> > > WHERE SRN.Night = MRN.Night
> > > AND (SRN.ShowRoomID = tShowHotelRooms.ShowRoomID
> > > OR SHR.ParentID = tShowhotelRooms.ShowRoomID))
> > > WHEN (ParentID <> NULL AND UseAsPool = 0) THEN
> > > (SELECT Allocated FROM tRoomNights SRN
> > > WHERE SRN.ShowrOomID = tShowhotelRooms.ParentID
> > > AND SRN.Night = MRN.Night) -
> > > (Select Sum(SRN.Sold) FROM tRoomNights SRN JOIN tShowHotelRooms SHR
> > > ON SRN.ShowRoomID = SHR.ShowRoomID
> > > WHERE SRN.Night = MRN.Night
> > >
> > > AND (SRN.ShowRoomID = tShowHotelRooms.ParentID
> > > OR SHR.ParentID = tShowhotelRooms.ParentID))
> > >
> > > ELSE Allocated-Sold
> > > END,
> > > tShowHotelRooms.RoomTypeID, tShowHotelRooms.RoomClassID,
> > > MRN.Rate
> > > FROM tRoomNights MRN
> > > JOIN tShowHotelRooms ON MRN.ShowRoomID =
> > > tSHowHOtelRooms.ShowRoomID
> > > JOIN tRoomTypes ON tShowHotelRooms.RoomTypeID =
> > > tRoomTypes.RoomTypeID
> > > JOIN tRoomClasses ON tShowHotelRooms.RoomClassID =
> > > tRoomClasses.RoomClassID
> > > JOIN tShowhotels ON tShowhotelRooms.ShowhotelID =
> > > tShowhotels.ShowhotelID
> > > WHERE ShowID = 50 ORDER BY Night
> > >
> > <snip>