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

Avez-vous essayé de reconstruire les statistiques et/ou les index de la table Report_Opener ? Toutes les recomplications du SP ne serviront à rien si les statistiques montrent toujours des données datant de la première mise en service de la base de données.

La requête initiale elle-même fonctionne rapidement car l'optimiseur peut voir que le paramètre ne sera jamais nul. Dans le cas de la PS, l'optimiseur ne peut pas être sûr que le paramètre ne sera jamais nul.

Answer 2

Bien que je sois généralement contre (mais dans ce cas, il semble que vous ayez une raison valable), avez-vous essayé de fournir des conseils de requête sur la version SP de la requête ? Si le serveur SQL prépare un plan d'exécution différent dans ces deux instances, pouvez-vous utiliser un indice pour lui dire quel index utiliser, afin que le plan corresponde au premier ?

Quelques exemples, vous pouvez aller ici .

EDIT : Si vous pouvez poster votre plan de requête ici, nous pourrons peut-être identifier une différence entre les plans qui est révélatrice.

DEUXIÈMEMENT : Mise à jour du lien pour être spécifique à SQL-2000. Vous devrez faire défiler un peu vers le bas, mais il y a une seconde intitulée "Table Hints" qui est ce que vous recherchez.

TROISIÈMEMENT : La "mauvaise" requête semble ignorer le [IX_Openers_SessionGUID] sur la table "Openers" - y a-t-il une chance que l'ajout d'un INDEX hint pour la forcer à utiliser cet index change les choses ?

Answer 3

C'est probablement peu probable, mais étant donné que le comportement que vous avez observé est inhabituel, il faut le vérifier et personne d'autre ne l'a mentionné.

Est-ce que vous absolument vous êtes sûr que tous les objets appartiennent à dbo et qu'il n'y a pas de copies indésirables appartenant à vous-même ou à un autre utilisateur ?

Parfois, lorsque j'ai observé un comportement étrange, c'est parce qu'il y avait en fait deux copies d'un objet et que celle que vous obtenez dépend de ce qui est spécifié et de la personne sous laquelle vous êtes connecté. Par exemple, il est parfaitement possible d'avoir deux copies d'une vue ou d'une procédure avec le même nom mais appartenant à des propriétaires différents - une situation qui peut se produire lorsque vous n'êtes pas connecté à la base de données en tant que dbo et que vous oubliez de spécifier dbo comme propriétaire de l'objet lorsque vous créez l'objet.

Notez que dans le texte, vous exécutez certaines choses sans spécifier le propriétaire, par ex.

sp\_recompile ViewOpener

si, par exemple, il existe deux copies de viewOpener appartenant à dbo et à [un autre utilisateur], celle que vous recompilez si vous ne le spécifiez pas dépend des circonstances. Idem avec la vue Report_Opener - s'il y avait deux copies (et elles pourraient différer dans la spécification ou le plan d'exécution) alors ce qui est utilisé dépend des circonstances - et comme vous ne spécifiez pas le propriétaire, il est parfaitement possible que votre requête adhoc utilise l'une et que la procédure compilée utilise l'autre.

Comme je l'ai dit, c'est probablement peu probable, mais c'est possible et il faut le vérifier, car il se peut que vous cherchiez simplement le bogue au mauvais endroit.

Answer 4

-- Voici la solution :

create procedure GetOrderForCustomers(@CustID varchar(20))

as

begin

select * from orders

where customerid = ISNULL(@CustID, '')

end

-- C'est tout.

Answer 5

J'ai une autre idée. Et si tu créais cette fonction basée sur un tableau :

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

Puis, vous avez effectué une sélection en utilisant l'instruction suivante (en la mettant même dans votre SP) :

SELECT *
FROM tbfSelectFromView(@SessionGUID)

Il semble que ce qui se passe (ce que tout le monde a déjà commenté), c'est que le serveur SQL fait une supposition erronée quelque part, et peut-être que cela va le forcer à corriger cette supposition. Je déteste ajouter une étape supplémentaire, mais je ne suis pas sûr de ce qui pourrait causer ce problème.