A query runs fast:
DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
subtree cost: 0.502
But putting the same SQL in a stored procedure runs slow, and with a totally different execution plan
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
EXECUTE ViewOpener @SessionGUID
Subtree cost: 19.2
I've run
sp_recompile ViewOpener
And it still runs the same (badly), and I've also changed the stored procedure to
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *, 'recompile please'
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
And back again, trying to really trick it into recompiling.
I've dropped and recreated the stored procedure in order to get it to generate a new plan.
I've tried forcing recompiles, and prevent parameter sniffing, by using a decoy variable:
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
DECLARE @SessionGUIDbitch uniqueidentifier
SET @SessionGUIDbitch = @SessionGUID
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUIDbitch
ORDER BY CurrencyTypeOrder, Rank
I've also tried defining the stored procedure WITH RECOMPILE:
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier
WITH RECOMPILE
AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
So that it's plan is never cached, and I've tried forcing a recompile at execute:
EXECUTE ViewOpener @SessionGUID WITH RECOMPILE
Which didn't help.
I've tried converting the procedure to dynamic SQL:
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier
WITH RECOMPILE AS
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank'
EXECUTE sp_executesql @SQLString,
N'@SessionGUID uniqueidentifier',
@SessionGUID
Which didn't help.
The entity "Report_Opener" is a view, which is not indexed. The view only references underlying tables. No table contains computed columns, indexed or otherwise.
For the hell of it I tried creating the view with
SET ANSI_NULLS ON
SET QUOTED_IDENTIFER ON
That didn't fix it.
How is it that
I tried moving the definition of the view directly into the stored procedure (violating 3 business rules, and breaking an important encapsulation), and that makes it only about 6x slower.
Why is the stored procedure version so slow? What can possibly account for SQL Server running ad-hoc SQL faster than a different kind of ad-hoc SQL?
I'd really rather not
change the code at all
Microsoft SQL Server 2000 - 8.00.2050 (Intel X86)
Mar 7 2008 21:29:56
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
But what can account for SQL Server being unable to run as fast as SQL Sever running a query, if not parameter sniffing.
My next attempt will be to have StoredProcedureA call StoredProcedureB call StoredProcedureC call StoredProcedureD to query the view.
And failing that, have the stored procedure call a stored procedure, call a UDF, call a UDF, call a stored procedure, call a UDF to query the view.
To sum up, the following run fast from QA, but slow when put into a stored procedure:
The original:
--Runs fine outside of a stored procedure
SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
sp_executesql:
--Runs fine outside of a stored procedure
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank'
EXECUTE sp_executesql @SQLString,
N'@SessionGUID uniqueidentifier',
@SessionGUID
EXEC(@sql):
--Runs fine outside of a stored procedure
DECLARE @sql NVARCHAR(500)
SET @sql = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = '''+CAST(@SessionGUID AS varchar(50))+'''
ORDER BY CurrencyTypeOrder, Rank'
EXEC(@sql)
Execution Plans
The good plan:
|--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
|--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[CurrencyType]
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
|--Filter(WHERE:((([Currencies].[IsActive]<>0 AND [Currencies].[OnOpener]<>0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currencies].
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
| |--Nested Loops(Left Outer Join)
| | |--Bookmark Lookup(BOOKMARK:([Bmk1016]), OBJECT:([GrobManagementSystemLive].[dbo].[Windows]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Openers].[WindowGUID]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
| | | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_SessionGUID]), SEEK:([Openers].[SessionGUID]=[@SessionGUID]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows]), SEEK:([Windows].[WindowGUID]=[Openers].[WindowGUID]) ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Currenc
|--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
|--Stream Aggregate(DEFINE:([Expr1006]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='ctCanadianCoin') OR [
|--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
|--Nested Loops(Inner Join)
| |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
The bad plan
|--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
|--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[Currency
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
|--Filter(WHERE:((([Currencies].[IsActive]<>0 AND [Currencies].[OnOpener]<>0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currenc
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
| |--Filter(WHERE:([Openers].[SessionGUID]=[@SessionGUID]))
| | |--Concatenation
| | |--Nested Loops(Left Outer Join)
| | | |--Table Spool
| | | | |--Hash Match(Inner Join, HASH:([Windows].[WindowGUID])=([Openers].[WindowGUID]), RESIDUAL:([Windows].[WindowGUID]=[Openers].[WindowGUID]))
| | | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows_CageGUID]))
| | | | |--Table Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
| | | |--Table Spool
| | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| | |--Compute Scalar(DEFINE:([Openers].[OpenerGUID]=NULL, [Openers].[SessionGUID]=NULL, [Windows].[UseChipDenominations]=NULL))
| | |--Nested Loops(Left Anti Semi Join)
| | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| | |--Row Count Spool
| | |--Table Spool
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Cu
|--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
|--Stream Aggregate(DEFINE:([Expr1006]=SUM([partialagg1034]), [Expr1007]=SUM([partialagg1035]), [Expr1008]=SUM([partialagg1036]), [Expr1009]=SUM([partialagg1037]), [Expr1010]=SUM([partialagg1038]), [Expr1011]=SUM([partialagg1039]
|--Nested Loops(Inner Join)
|--Stream Aggregate(DEFINE:([partialagg1034]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='
| |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
The bad-one is eager spooling 6 million rows; the other one isn't.
Note: This isn't a question about tuning a query. I have a query that runs lightning fast. I just want SQL Server to run fast from a stored procedure.
This question is related to
sql-server
performance
stored-procedures
Do this for your database. I have the same issue - it works fine in one database but when I copy this database to another using SSIS Import (not the usual restore), this issue happens to most of my stored procedures. So after googling some more, I found the blog of Pinal Dave (which btw, I encountered most of his post and did help me a lot so thanks Pinal Dave).
I execute the below query on my database and it corrected my issue:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
Hope this helps. Just passing the help from others that helped me.
This time you found your problem. If next time you are less lucky and cannot figure it out, you can use plan freezing and stop worrying about wrong execution plan.
I was facing the same issue & this post was very helpful to me but none of the posted answers solved my specific issue. I wanted to post the solution that worked for me in hopes that it can help someone else.
https://stackoverflow.com/a/24016676/814299
At the end of your query, add OPTION (OPTIMIZE FOR (@now UNKNOWN))
This is probably unlikely, but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it.
Are you absolutely sure that all objects are owned by dbo and you don't have a rogue copies owned by yourself or a different user present as well?
Just occasionally when I've seen odd behaviour it's because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as. For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object.
In note that in the text you are running some things without specifying owner, eg
sp_recompile ViewOpener
if for example there where two copies of viewOpener present owned by dbo and [some other user] then which one you actually recompile if you don't specify is dependent upon circumstances. Ditto with the Report_Opener view - if there where two copies (and they could differ in specification or execution plan) then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other.
As I say, it's probably unlikely but it is possible and should be checked because your issues could be that you're simply looking for the bug in the wrong place.
I was facing the same issue & this post was very helpful to me but none of the posted answers solved my specific issue. I wanted to post the solution that worked for me in hopes that it can help someone else.
https://stackoverflow.com/a/24016676/814299
At the end of your query, add OPTION (OPTIMIZE FOR (@now UNKNOWN))
Though I'm usually against it (though in this case it seems that you have a genuine reason), have you tried providing any query hints on the SP version of the query? If SQL Server is preparing a different execution plan in those two instances, can you use a hint to tell it what index to use, so that the plan matches the first one?
For some examples, you can go here.
EDIT: If you can post your query plan here, perhaps we can identify some difference between the plans that's telling.
SECOND: Updated the link to be SQL-2000 specific. You'll have to scroll down a ways, but there's a second titled "Table Hints" that's what you're looking for.
THIRD: The "Bad" query seems to be ignoring the [IX_Openers_SessionGUID] on the "Openers" table - any chance adding an INDEX hint to force it to use that index will change things?
Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.
The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.
I was experiencing this problem. My query looked something like:
select a, b, c from sometable where date > '20140101'
My stored procedure was defined like:
create procedure my_procedure (@dtFrom date)
as
select a, b, c from sometable where date > @dtFrom
I changed the datatype to datetime and voila! Went from 30 minutes to 1 minute!
create procedure my_procedure (@dtFrom datetime)
as
select a, b, c from sometable where date > @dtFrom
This is probably unlikely, but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it.
Are you absolutely sure that all objects are owned by dbo and you don't have a rogue copies owned by yourself or a different user present as well?
Just occasionally when I've seen odd behaviour it's because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as. For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object.
In note that in the text you are running some things without specifying owner, eg
sp_recompile ViewOpener
if for example there where two copies of viewOpener present owned by dbo and [some other user] then which one you actually recompile if you don't specify is dependent upon circumstances. Ditto with the Report_Opener view - if there where two copies (and they could differ in specification or execution plan) then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other.
As I say, it's probably unlikely but it is possible and should be checked because your issues could be that you're simply looking for the bug in the wrong place.
This may sound silly and seems obvious from the name SessionGUID, but is the column a uniqueidentifier on Report_Opener? If not, you may want to try casting it to the correct type and give it a shot or declare your variable to the correct type.
The plan created as part of the sproc may work unintuitively and do an internal cast on a large table.
I've got another idea. What if you create this table-based function:
CREATE FUNCTION tbfSelectFromView
(
-- Add the parameters for the function here
@SessionGUID UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
)
GO
And then selected from it using the following statement (even putting this in your SP):
SELECT *
FROM tbfSelectFromView(@SessionGUID)
It looks like what's happening (which everybody has already commented on) is that SQL Server just makes an assumption somewhere that's wrong, and maybe this will force it to correct the assumption. I hate to add the extra step, but I'm not sure what else might be causing it.
I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.
I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.
Slow way:
CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT *
FROM orders
WHERE customerid = @CustID
END
Fast way:
CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
DECLARE @LocCustID varchar(20)
SET @LocCustID = @CustID
SELECT *
FROM orders
WHERE customerid = @LocCustID
END
Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.
Though I'm usually against it (though in this case it seems that you have a genuine reason), have you tried providing any query hints on the SP version of the query? If SQL Server is preparing a different execution plan in those two instances, can you use a hint to tell it what index to use, so that the plan matches the first one?
For some examples, you can go here.
EDIT: If you can post your query plan here, perhaps we can identify some difference between the plans that's telling.
SECOND: Updated the link to be SQL-2000 specific. You'll have to scroll down a ways, but there's a second titled "Table Hints" that's what you're looking for.
THIRD: The "Bad" query seems to be ignoring the [IX_Openers_SessionGUID] on the "Openers" table - any chance adding an INDEX hint to force it to use that index will change things?
I've got another idea. What if you create this table-based function:
CREATE FUNCTION tbfSelectFromView
(
-- Add the parameters for the function here
@SessionGUID UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
)
GO
And then selected from it using the following statement (even putting this in your SP):
SELECT *
FROM tbfSelectFromView(@SessionGUID)
It looks like what's happening (which everybody has already commented on) is that SQL Server just makes an assumption somewhere that's wrong, and maybe this will force it to correct the assumption. I hate to add the extra step, but I'm not sure what else might be causing it.
Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.
The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.
I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.
I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.
Slow way:
CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT *
FROM orders
WHERE customerid = @CustID
END
Fast way:
CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
DECLARE @LocCustID varchar(20)
SET @LocCustID = @CustID
SELECT *
FROM orders
WHERE customerid = @LocCustID
END
Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.
This may sound silly and seems obvious from the name SessionGUID, but is the column a uniqueidentifier on Report_Opener? If not, you may want to try casting it to the correct type and give it a shot or declare your variable to the correct type.
The plan created as part of the sproc may work unintuitively and do an internal cast on a large table.
This time you found your problem. If next time you are less lucky and cannot figure it out, you can use plan freezing and stop worrying about wrong execution plan.
Though I'm usually against it (though in this case it seems that you have a genuine reason), have you tried providing any query hints on the SP version of the query? If SQL Server is preparing a different execution plan in those two instances, can you use a hint to tell it what index to use, so that the plan matches the first one?
For some examples, you can go here.
EDIT: If you can post your query plan here, perhaps we can identify some difference between the plans that's telling.
SECOND: Updated the link to be SQL-2000 specific. You'll have to scroll down a ways, but there's a second titled "Table Hints" that's what you're looking for.
THIRD: The "Bad" query seems to be ignoring the [IX_Openers_SessionGUID] on the "Openers" table - any chance adding an INDEX hint to force it to use that index will change things?
This is probably unlikely, but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it.
Are you absolutely sure that all objects are owned by dbo and you don't have a rogue copies owned by yourself or a different user present as well?
Just occasionally when I've seen odd behaviour it's because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as. For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object.
In note that in the text you are running some things without specifying owner, eg
sp_recompile ViewOpener
if for example there where two copies of viewOpener present owned by dbo and [some other user] then which one you actually recompile if you don't specify is dependent upon circumstances. Ditto with the Report_Opener view - if there where two copies (and they could differ in specification or execution plan) then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other.
As I say, it's probably unlikely but it is possible and should be checked because your issues could be that you're simply looking for the bug in the wrong place.
-- Here is the solution:
create procedure GetOrderForCustomers(@CustID varchar(20))
as
begin
select * from orders
where customerid = ISNULL(@CustID, '')
end
-- That's it
I've got another idea. What if you create this table-based function:
CREATE FUNCTION tbfSelectFromView
(
-- Add the parameters for the function here
@SessionGUID UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
)
GO
And then selected from it using the following statement (even putting this in your SP):
SELECT *
FROM tbfSelectFromView(@SessionGUID)
It looks like what's happening (which everybody has already commented on) is that SQL Server just makes an assumption somewhere that's wrong, and maybe this will force it to correct the assumption. I hate to add the extra step, but I'm not sure what else might be causing it.
This is probably unlikely, but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it.
Are you absolutely sure that all objects are owned by dbo and you don't have a rogue copies owned by yourself or a different user present as well?
Just occasionally when I've seen odd behaviour it's because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as. For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object.
In note that in the text you are running some things without specifying owner, eg
sp_recompile ViewOpener
if for example there where two copies of viewOpener present owned by dbo and [some other user] then which one you actually recompile if you don't specify is dependent upon circumstances. Ditto with the Report_Opener view - if there where two copies (and they could differ in specification or execution plan) then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other.
As I say, it's probably unlikely but it is possible and should be checked because your issues could be that you're simply looking for the bug in the wrong place.
Though I'm usually against it (though in this case it seems that you have a genuine reason), have you tried providing any query hints on the SP version of the query? If SQL Server is preparing a different execution plan in those two instances, can you use a hint to tell it what index to use, so that the plan matches the first one?
For some examples, you can go here.
EDIT: If you can post your query plan here, perhaps we can identify some difference between the plans that's telling.
SECOND: Updated the link to be SQL-2000 specific. You'll have to scroll down a ways, but there's a second titled "Table Hints" that's what you're looking for.
THIRD: The "Bad" query seems to be ignoring the [IX_Openers_SessionGUID] on the "Openers" table - any chance adding an INDEX hint to force it to use that index will change things?
-- Here is the solution:
create procedure GetOrderForCustomers(@CustID varchar(20))
as
begin
select * from orders
where customerid = ISNULL(@CustID, '')
end
-- That's it
I've got another idea. What if you create this table-based function:
CREATE FUNCTION tbfSelectFromView
(
-- Add the parameters for the function here
@SessionGUID UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
)
GO
And then selected from it using the following statement (even putting this in your SP):
SELECT *
FROM tbfSelectFromView(@SessionGUID)
It looks like what's happening (which everybody has already commented on) is that SQL Server just makes an assumption somewhere that's wrong, and maybe this will force it to correct the assumption. I hate to add the extra step, but I'm not sure what else might be causing it.
I was experiencing this problem. My query looked something like:
select a, b, c from sometable where date > '20140101'
My stored procedure was defined like:
create procedure my_procedure (@dtFrom date)
as
select a, b, c from sometable where date > @dtFrom
I changed the datatype to datetime and voila! Went from 30 minutes to 1 minute!
create procedure my_procedure (@dtFrom datetime)
as
select a, b, c from sometable where date > @dtFrom
Do this for your database. I have the same issue - it works fine in one database but when I copy this database to another using SSIS Import (not the usual restore), this issue happens to most of my stored procedures. So after googling some more, I found the blog of Pinal Dave (which btw, I encountered most of his post and did help me a lot so thanks Pinal Dave).
I execute the below query on my database and it corrected my issue:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
Hope this helps. Just passing the help from others that helped me.
Source: Stackoverflow.com