SQL Server : Requête rapide, mais lente à partir de la procédure

Question

Une requête s'exécute rapidement :

DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

coût du sous-arbre : 0,502

Mais si l'on place le même SQL dans une procédure stockée, il s'exécute lentement, et avec un plan d'exécution totalement différent.

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

EXECUTE ViewOpener @SessionGUID

Coût des sous-arbres : 19,2

J'ai couru

sp_recompile ViewOpener

et il s'exécute toujours de la même manière (mal). pour

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *, 'recompile please'
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

et vice-versa, pour essayer de le pousser à recompiler.

J'ai abandonné et recréé la procédure stockée pour qu'elle génère un nouveau plan.

J'ai essayé de forcer les recompilations, et empêcher le reniflage des paramètres en utilisant une variable leurre :

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS

DECLARE @SessionGUIDbitch uniqueidentifier
SET @SessionGUIDbitch = @SessionGUID

SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUIDbitch
ORDER BY CurrencyTypeOrder, Rank

J'ai également essayé de définir la procédure stockée AVEC RECOMPILE :

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier 
WITH RECOMPILE
AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

pour que son plan ne soit jamais mis en cache, et j'ai essayé de forcer une recompilation à l'exécution :

EXECUTE ViewOpener @SessionGUID WITH RECOMPILE

ce qui n'a pas aidé.

J'ai essayé de convertir la procédure en sql dynamique :

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

ce qui n'a pas aidé.

L'entité "Report_Opener" est une vue, qui n'est pas indexée. La vue ne fait référence qu'aux tables sous-jacentes. Aucune table ne contient de colonnes calculées, indexées ou non.

Pour le plaisir de le faire, j'ai essayé de créer la vue avec

SET ANSI_NULLS ON
SET QUOTED_IDENTIFER ON

ça ne l'a pas arrangé.

Comment se fait-il que

• la requête est rapide
• le déplacement de la requête vers une vue et la sélection à partir de cette vue sont rapides.
• la sélection de la vue à partir d'une procédure stockée est 40x plus lente ?

J'ai essayé de déplacer la définition de la vue directement dans la procédure stockée (en violant 3 règles de gestion, et en brisant une encapsulation importante), et cela ne la rend que 6 fois plus lente.

Pourquoi la version de la procédure stockée est-elle si lente ? Qu'est-ce qui peut expliquer que SQL Server exécute le sql ad-hoc plus rapidement qu'un autre type de sql ad-hoc ?

Je ne préfère vraiment pas

• intégrer le SQL dans le code

• changer le code du tout au tout

  Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) 7 mars 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition sur Windows NT 5.2 (Build 3790 : Service Pack 2)

Mais qu'est-ce qui peut expliquer que SQL Server ne puisse pas fonctionner aussi vite que SQL Sever exécutant une requête, si ce n'est le reniflage des paramètres.

---

Ma prochaine tentative sera de faire en sorte que StoredProcedureA appelle StoredProcedureB appelle StoredProcedureC appelle StoredProcedureD pour interroger la vue.

Et à défaut, faites en sorte que la procédure stockée appelle une procédure stockée, une UDF, une UDF, une procédure stockée, une UDF pour interroger la vue.

---

Pour résumer, les éléments suivants fonctionnent rapidement depuis l'AQ, mais lentement lorsqu'ils sont placés dans une procédure stockée :

L'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)

---

Plans d'exécution

Le site bon 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:([CashierManagementSystemLive].[dbo].[Windows]))
                     |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Openers].[WindowGUID]))
                     |         |    |         |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([CashierManagementSystemLive].[dbo].[Openers]))
                     |         |    |         |    |--Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[Openers].[IX_Openers_SessionGUID]), SEEK:([Openers].[SessionGUID]=[@SessionGUID]) ORDERED FORWARD)
                     |         |    |         |--Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[Windows].[IX_Windows]), SEEK:([Windows].[WindowGUID]=[Openers].[WindowGUID]) ORDERED FORWARD)
                     |         |    |--Clustered Index Scan(OBJECT:([CashierManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
                     |         |--Clustered Index Seek(OBJECT:([CashierManagementSystemLive].[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:([CashierManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                    |    |--Clustered Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                    |--Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)

Le site mauvais 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:([CashierManagementSystemLive].[dbo].[Windows].[IX_Windows_CageGUID]))
                      |         |         |    |         |--Table Scan(OBJECT:([CashierManagementSystemLive].[dbo].[Openers]))
                      |         |         |    |--Table Spool
                      |         |         |         |--Clustered Index Scan(OBJECT:([CashierManagementSystemLive].[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:([CashierManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
                      |         |                   |--Row Count Spool
                      |         |                        |--Table Spool
                      |         |--Clustered Index Seek(OBJECT:([CashierManagementSystemLive].[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:([CashierManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                     |         |--Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
                                     |--Index Seek(OBJECT:([CashierManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)

Le mauvais est avide de 6 millions de rangs, l'autre ne l'est pas.

Note : Il ne s'agit pas d'une question sur le réglage d'une requête. J'ai une requête qui s'exécute très rapidement. Je veux simplement que le serveur SQL s'exécute rapidement à partir d'une procédure stockée.

Answer 1

J'ai eu le même problème que l'affiche originale mais la réponse citée n'a pas résolu le problème pour moi. La requête s'exécutait toujours très lentement à partir d'une procédure stockée.

J'ai trouvé une autre réponse ici "Parameter Sniffing" Merci Omnibuzz. Cela se résume à l'utilisation de "variables locales" dans vos requêtes de procédures stockées, mais lisez l'original pour mieux comprendre, c'est un excellent article. par exemple.

Lentement :

create procedure GetOrderForCustomers(@CustID varchar(20))
as
begin
  select * from orders
  where customerid = @CustID
end

C'est rapide :

create procedure GetOrderForCustomersWithoutPS(@CustID varchar(20))
as
begin
  declare @LocCustID varchar(20)
  set @LocCustID = @CustID

  select * from orders
  where customerid = @LocCustID
end

J'espère que cela aidera quelqu'un d'autre. En faisant cela, j'ai réduit mon temps d'exécution de 5+ minutes à environ 6-7 secondes.

Answer 2

J'ai trouvé le problème, voici le script des versions lente et rapide de la procédure stockée :

dbo.ViewOpener__RenamedForCruachan__Slow.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow
    @SessionGUID uniqueidentifier
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

dbo.ViewOpener__RenamedForCruachan__Fast.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast
    @SessionGUID uniqueidentifier 
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Si vous n'avez pas vu la différence, je ne vous en veux pas. La différence n'est pas du tout dans la procédure stockée. La différence qui transforme une requête rapide de 0,5 coût en une requête qui fait un spool rapide de 6 millions de lignes :

Lentement : SET ANSI_NULLS OFF

Rapide : SET ANSI_NULLS ON

---

Cette réponse pourrait également avoir un sens, puisque la vue a une clause de jonction qui dit :

(table.column IS NOT NULL)

Il y a donc des NULLs impliqués.

---

L'explication est encore prouvée en retournant à Query Analizer, et en exécutant

SET ANSI_NULLS OFF

.

DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

.

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

Et la requête est lente.

---

Donc le problème n'est pas car la requête est exécutée à partir d'une procédure stockée. Le problème est que l'option de connexion par défaut d'Enterprise Manager est ANSI_NULLS off, et non ANSI_NULLS on, qui est la valeur par défaut de QA.

Microsoft reconnaît ce fait dans KB296769 (BOGUE : Impossible d'utiliser SQL Enterprise Manager pour créer des procédures stockées contenant des objets serveur liés). La solution consiste à inclure l'option ANSI_NULLS dans la boîte de dialogue de la procédure stockée :

Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....

Answer 3

Faites-le pour votre base de données. J'ai le même problème - il fonctionne bien dans une base de données, mais quand je copie cette base de données à une autre en utilisant SSIS Import (pas la restauration habituelle), ce problème se produit à la plupart de mon stored proc. Après avoir cherché un peu plus, j'ai trouvé le blog de Pinal Dave (dont j'ai rencontré la plupart des articles et qui m'a beaucoup aidé, donc merci Pinal Dave). http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

J'ai exécuté la requête ci-dessous sur ma base de données et cela a corrigé mon problème :

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO 

J'espère que cela vous aidera. Je ne fais que transmettre l'aide des autres qui m'a aidé :)

Answer 4

J'ai rencontré ce problème. Ma requête ressemblait à quelque chose comme :

select a, b, c from sometable where date > '20140101'

Ma procédure stockée était définie comme suit :

create procedure my_procedure (@dtFrom date)
as
select a, b, c from sometable where date > @dtFrom

J'ai changé le type de données en datetime et voilà ! On est passé de 30 minutes à 1 minute !

create procedure my_procedure (@dtFrom datetime)
as
select a, b, c from sometable where date > @dtFrom

Answer 5

Cette fois, vous avez trouvé votre problème. Si la prochaine fois vous êtes moins chanceux et ne pouvez pas le résoudre, vous pouvez utiliser gel du plan et arrêter de s'inquiéter d'un mauvais plan d'exécution.