SQL Server: query veloce, ma lenta dalla procedura

Una query è veloce:

DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

costo della sottostruttura: 0,502

Ma mettere lo stesso SQL in una stored procedure viene eseguito lentamente e con un piano di esecuzione completamente diverso

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

Costo della sottostruttura: 19.2

Ho corso

 sp_recompile ViewOpener 

E funziona ancora lo stesso (male), e ho anche cambiato la stored procedure in

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

E di nuovo, cercando di ingannarlo davvero nella ricompilazione.

Ho abbandonato e ricreato la procedura memorizzata al fine di farlo generare un nuovo piano.

Ho provato a forzare le ricompilazioni e a prevenire l’annidamento dei parametri , usando una variabile decoy:

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

Ho anche provato a definire la stored procedure WITH RECOMPILE :

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

In modo che il piano non venga mai memorizzato nella cache, e ho provato a forzare una ricompilazione all’esecuzione:

 EXECUTE ViewOpener @SessionGUID WITH RECOMPILE 

Quale non ha aiutato.

Ho provato a convertire la procedura in SQL dinamico:

 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 

Quale non ha aiutato.

L’ quadro ” Report_Opener ” è una vista, che non è indicizzata. La vista si riferisce solo alle tabelle sottostanti. Nessuna tabella contiene colonne calcolate, indicizzate o altrimenti.

Per il diavolo ho provato a creare la vista con

 SET ANSI_NULLS ON SET QUOTED_IDENTIFER ON 

Questo non l’ha risolto

Come è quello

  • la query è veloce
  • spostando la query su una vista e selezionando dalla vista è veloce
  • la selezione dalla vista da una procedura memorizzata è 40 volte più lenta?

Ho provato a spostare la definizione della vista direttamente nella stored procedure (violando 3 regole di business e interrompendo un’importante incapsulamento), e questo la rende solo di circa 6 volte più lenta.

Perché la versione della stored procedure è così lenta? Che cosa può forse giustificare che SQL Server esegua SQL ad hoc più rapidamente di un diverso tipo di SQL ad hoc?

Preferirei davvero di no

  • incorporare l’SQL nel codice
  • cambiare il codice

     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) 

Ma cosa può tenere conto del fatto che SQL Server non è in grado di funzionare alla stessa velocità di SQL Sever che esegue una query, se non lo sniffing dei parametri.


Il mio prossimo tentativo sarà di avere StoredProcedureA chiamata StoredProcedureB chiamata StoredProcedureC chiamata StoredProcedureD per interrogare la vista.

In caso contrario, fare in modo che la procedura memorizzata richiami una procedura memorizzata, chiamare una UDF, chiamare una UDF, chiamare una stored procedure, chiamare una UDF per interrogare la vista.


Per riassumere, la seguente esecuzione veloce dal QA, ma lenta quando messa in una stored procedure:

L’originale:

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

Piani di esecuzione

Il buon piano:

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

Il cattivo piano

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

Il cattivo è desideroso di spooling di 6 milioni di file; l’altro no.

Nota: questa non è una domanda sull’ottimizzazione di una query. Ho una query che funziona veloce come un fulmine. Voglio solo che SQL Server funzioni velocemente da una stored procedure.

    Ho avuto lo stesso problema del poster originale ma la risposta citata non ha risolto il problema per me. La query è ancora molto lenta da una stored procedure.

    Ho trovato un’altra risposta qui “Parametro Sniffing” , Grazie Omnibuzz. Si riduce all’uso delle “variabili locali” nelle query della procedura memorizzata, ma leggi l’originale per maggiore comprensione, è un ottimo resoconto. per esempio

    Modo lento:

     CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20)) AS BEGIN SELECT * FROM orders WHERE customerid = @CustID END 

    Modo veloce:

     CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20)) AS BEGIN DECLARE @LocCustID varchar(20) SET @LocCustID = @CustID SELECT * FROM orders WHERE customerid = @LocCustID END 

    Spero che questo aiuti qualcun altro, riducendo i tempi di esecuzione da 5+ minuti a circa 6-7 secondi.

    Ho trovato il problema, ecco lo script delle versioni lente e veloci della stored procedure:

    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 

    Se non hai notato la differenza, non ti biasimo. La differenza non è affatto nella stored procedure. La differenza che trasforma una query di costo in 0.5 rapida in una che fa uno spione desideroso di 6 milioni di righe:

    Lento: SET ANSI_NULLS OFF

    Veloce: SET ANSI_NULLS ON


    Questa risposta potrebbe anche essere resa sensata, poiché la vista ha una clausola join che dice:

     (table.column IS NOT NULL) 

    Quindi ci sono alcuni NULL coinvolti.


    La spiegazione è ulteriormente dimostrata tornando su Query Analizer e correndo

     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 

    E la query è lenta.


    Quindi il problema non è perché la query viene eseguita da una stored procedure. Il problema è che l’opzione di default della connessione di Enterprise Manager è ANSI_NULLS off , anziché ANSI_NULLS on , che è l’impostazione predefinita di QA.

    Microsoft riconosce questo fatto in KB296769 (ERRORE: imansible utilizzare SQL Enterprise Manager per creare stored procedure contenenti oggetti server collegati). La soluzione alternativa include l’opzione ANSI_NULLS nella finestra di dialogo della stored procedure:

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

    Fallo per il tuo database. Ho lo stesso problema: funziona bene in un database ma quando copio questo database su un altro utilizzando SSIS Import (non il solito ripristino), questo problema si verifica nella maggior parte delle mie stored procedure. Quindi, dopo aver cercato su Google, ho trovato il blog di Pinal Dave (che tra l’altro ho incontrato la maggior parte del suo post e mi ha aiutato molto, grazie a Pinal Dave) .

    Eseguo la query qui sotto sul mio database e ha corretto il mio problema:

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

    Spero che questo ti aiuti. Sto solo passando l’aiuto di altri che mi hanno aiutato.

    Stavo affrontando lo stesso problema e questo post mi è stato di grande aiuto, ma nessuna delle risposte postate ha risolto il mio problema specifico. Volevo pubblicare la soluzione che ha funzionato per me nella speranza che possa aiutare qualcun altro.

    https://stackoverflow.com/a/24016676/814299

    Alla fine della query, aggiungi OPTION (OPTIMIZE FOR (@now UNKNOWN))

    Questa volta hai trovato il tuo problema. Se la prossima volta sei meno fortunato e non riesci a capirlo, puoi usare il blocco dei piani e smettere di preoccuparti del piano di esecuzione sbagliato.

    Stavo vivendo questo problema. La mia richiesta assomigliava a qualcosa:

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

    La mia stored procedure è stata definita come:

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

    Ho cambiato il datatype in datetime e voilà! Sono andato da 30 minuti a 1 minuto!

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

    Hai provato a ribuild le statistiche e / o gli indici nella tabella Report_Opener. Tutte le ricompense dell’SP non valgono nulla se le statistiche mostrano ancora i dati di quando il database è stato inaugurato per la prima volta.

    La query iniziale funziona rapidamente perché l’ottimizzatore può vedere che il parametro non sarà mai nullo. Nel caso di SP, l’ottimizzatore non può essere sicuro che il parametro non sarà mai nullo.

    Anche se di solito sono contrario (anche se in questo caso sembra che tu abbia una vera ragione), hai provato a fornire suggerimenti per le query sulla versione SP della query? Se SQL Server sta preparando un piano di esecuzione diverso in queste due istanze, è ansible utilizzare un suggerimento per indicare quale indice utilizzare, in modo che il piano corrisponda al primo?

    Per alcuni esempi, puoi andare qui .

    EDIT: Se puoi pubblicare qui il tuo piano di query, forse possiamo identificare alcune differenze tra i piani che stanno raccontando.

    SECONDO: aggiornato il link per essere specifico per SQL-2000. Dovrai scorrere verso il basso in alcuni modi, ma c’è un secondo “Table Hints” che è quello che stai cercando.

    TERZO: La query “Ctriggers” sembra ignorare il [IX_Openers_SessionGUID] nella tabella “Openers” – qualsiasi possibilità di aggiungere un suggerimento INDEX per costringerlo a usare quell’indice cambierà le cose?

    Questo è probabilmente improbabile, ma dato che il comportamento osservato è inusuale, deve essere controllato e nessun altro lo ha menzionato.

    Sei assolutamente sicuro che tutti gli oggetti sono di proprietà di dbo e non hai copie di tua proprietà o un altro utente presente?

    Solo occasionalmente quando ho visto un comportamento strano è perché c’erano in realtà due copie di un object e quale si ottiene dipende da cosa viene specificato e da chi si è connessi. Ad esempio, è perfettamente ansible avere due copie di una vista o una procedura con lo stesso nome ma di proprietà di proprietari diversi: una situazione che può sorgere quando non si è connessi al database come dbo e si dimentica di specificare dbo come proprietario dell’object quando tu crei l’object.

    Da notare che nel testo si stanno eseguendo alcune cose senza specificare il proprietario, ad es

     sp_recompile ViewOpener
    

    se per esempio ci sono due copie di viewOpener presenti di proprietà di dbo e [qualche altro utente], allora quale ricompensa effettivamente se non lo si specifica dipende dalle circostanze. Idem con la vista Report_Opener – se ci sono due copie (e potrebbero differire nella specifica o nel piano di esecuzione), ciò che viene utilizzato dipende dalle circostanze e, poiché non si specifica il proprietario, è perfettamente ansible che la query adhoc ne utilizzi una e la la procedura compilata potrebbe usare l’altra.

    Come ho detto, è probabilmente improbabile, ma è ansible e dovrebbe essere controllato perché i tuoi problemi potrebbero essere che stai semplicemente cercando il bug nel posto sbagliato.

    Questo può sembrare sciocco e sembra ovvio dal nome SessionGUID, ma la colonna è un identificatore univoco su Report_Opener? In caso contrario, si consiglia di provare a trasmettere il tipo corretto e dare un colpo o dichiarare la variabile al tipo corretto.

    Il piano creato come parte dello sproc può funzionare in modo non intuitivo e fare un cast interno su un grande tavolo.

    Ho un’altra idea. Cosa succede se si crea questa funzione basata su tabella:

     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 

    E poi selezionato da esso usando la seguente dichiarazione (anche inserendo questo nel tuo SP):

     SELECT * FROM tbfSelectFromView(@SessionGUID) 

    Sembra che quello che sta succedendo (che tutti hanno già commentato) è che SQL Server fa solo un’assunzione da qualche parte che è sbagliato, e forse questo lo costringerà a correggere l’ipotesi. Detesto aggiungere il passaggio in più, ma non sono sicuro di quale altro potrebbe causarlo.

    – Ecco la soluzione:

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

    — Questo è tutto