[解決済み】SQL Server。クエリーは高速ですが、プロシージャから遅い

2022-03-31 23:43:22

質問

クエリが高速に実行される。

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

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

サブツリー・コスト: 0.502

しかし、同じSQLをストアドプロシージャに入れると、実行速度が遅くなり、実行計画も全く異なるものになります。

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

EXECUTE ViewOpener @SessionGUID

サブツリー・コスト：19.2

私が実行したのは

sp_recompile ViewOpener

そして、まだ同じように（ひどく）実行されます。また、ストアドを変更しました。プロシージャを

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

そしてまた戻って、リコンパイルするように本当に騙してみる。

新しい計画を生成させるために、ストアドプロシージャを削除して再作成しました。

強制的にリコンパイルしてみました。 とパラメータスニッフィングを防ぐ 囮の変数を使用することによって。

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS

DECLARE @SessionGUIDbitch uniqueidentifier
SET @SessionGUIDbitch = @SessionGUID

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

ストアドプロシージャの定義も試してみました。 WITH RECOMPILE :

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

そのため、そのプランがキャッシュされることはなく、実行時に強制的にリコンパイルするようにしてみました。

EXECUTE ViewOpener @SessionGUID WITH RECOMPILE

というのは、何の役にも立たなかった。

プロシージャをダイナミック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

というのは、何の役にも立たなかった。

エンティティ " Report_Opener はビューであり、インデックスが作成されません。ビューは基礎となるテーブルを参照するだけです。テーブルには、インデックス付きかどうかに関わらず、計算された列は含まれていません。

念のため、以下の方法でビューを作成してみました。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFER ON

それでも直らなかった。

というのはどうなんでしょう？

クエリが速い
クエリをビューに移動し、ビューから選択するのが速い。
ストアドプロシージャからビューを選択すると、40倍も遅くなるのですか？

ビューの定義を直接ストアドプロシージャに移動してみましたが（3つのビジネスルールに違反し、重要なカプセル化も壊しています）、6倍程度しか遅くなりません。

ストアドプロシージャ版はなぜこんなに遅いのですか？SQL ServerがアドホックなSQLを別の種類のアドホックなSQLより速く実行する理由は何でしょうか？

本当にやめてほしい

SQLをコードに埋め込む

コードを全く変更しない

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)

しかし、パラメータ・スニッフィングでなければ、SQL Server がクエリを実行する SQL Sever ほど高速に実行できない理由は何でしょう。

私の次の試みは StoredProcedureA コール StoredProcedureB コール StoredProcedureC コール StoredProcedureD を使用してビューにクエリを発行します。

そして、ストアドプロシージャがストアドプロシージャを呼び出し、UDFを呼び出し、UDFを呼び出し、ストアドプロシージャを呼び出し、UDFを呼び出してビューにクエリを実行するようにします。

まとめると、QAからは以下のように高速に動作するが、ストアドプロシージャに入れると遅くなる。

原文のままです。

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

実行計画

は良いを計画します。

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

は悪い計画

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

Bad-1は600万行を熱心にスプールしていますが、もう一方はそうではありません。

注意してください。 これはクエリのチューニングに関する質問ではありません。私は光速で走るクエリを持っています。ストアドプロシージャからSQL Serverを高速に実行させたいだけです。

どのように解決するのですか？

問題が見つかりました。以下は、ストアドプロシージャの低速版と高速版のスクリプトです。

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

もし、あなたが違いに気づかなかったとしても、私はあなたを責めないわ。違いはストアドプロシージャには全くないのです。0.5コストの高速クエリを、600万行のイーガー・スプールを行うクエリに変えてしまう違いです。

遅い。 SET ANSI_NULLS OFF

速い。 SET ANSI_NULLS ON

この回答も、ビューがjoin句を持っているので、意味をなすようにすることができます。

(table.column IS NOT NULL)

ということで、いくつかの NULL が絡んできます。

この説明は、Query Analizerに戻り、次のように実行することでさらに証明されます。

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

そして、クエリが遅い。

ということで、問題点 ではありません。 クエリがストアドプロシージャから実行されているからです。問題は、Enterprise Manager の接続のデフォルトオプションが ANSI_NULLS off ではなく ANSI_NULLS on QA のデフォルトである。

マイクロソフトは、この事実を KB296769 (BUG: SQL Enterprise Manager を使用して、リンクされたサーバーオブジェクトを含むストアドプロシージャを作成できません）。回避策としては ANSI_NULLS オプションをストアドプロシージャダイアログに追加しました。

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

[解決済み】SQL Server。クエリーは高速ですが、プロシージャから遅い

質問

どのように解決するのですか？

関連

[解決済み] 等値演算で "SQL_Latin1_General_CP1_CI_AS" と "Latin1_General_CI_AS" の照合の競合を解決できない

[解決済み] データセットに対するSSRSクエリの実行に失敗しました

[解決済み] SQL ServerでSELECTからUPDATEする方法とは？

[解決済み] SQL Server で複数行のテキストを 1 つのテキスト文字列に連結する方法

[解決済み] SQL Server テーブルにカラムが存在するかどうかを確認する方法は？

[解決済み] SQL Server の DateTime データ型から日付だけを返す方法

[解決済み] SQL ServerにおけるLEFT JOINとLEFT OUTER JOINの比較

[解決済み] SQL Serverでストアドプロシージャ内のテキストを検索する

[解決済み] SQL Serverにおける関数とストアドプロシージャの比較

[解決済み】SQL Serverで既存のテーブルにデフォルト値を持つカラムを追加する

最新

nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)

htmlページでギリシャ文字を使うには

ピュアhtml+cssでの要素読み込み効果

純粋なhtml + cssで五輪を実現するサンプルコード

ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード

タイピング効果を実現するピュアhtml+css

htmlの選択ボックスのプレースホルダー作成に関する質問

html css3 伸縮しない画像表示効果

トップナビゲーションバーメニュー作成用HTML+CSS

html+css 実装サイバーパンク風ボタン

おすすめ

[解決済み】フラットファイルの日付カラムで「キャスト指定に無効な文字値があります」を修正する方法とは？

[解決済み] SQL Server データベース復元エラー: 指定されたキャストは無効です。(SqlManagerUI)

[解決済み] SQL Agent を使用して SSIS パッケージを実行する際の問題 - "DTSER_FAILURE(1)" で失敗します。

[解決済み] SQL Server : ログインに成功しましたが、「データベース [dbName] にアクセスできません。(ObjectExplorer) "と表示されました。

[解決済み] EF: テキストデータ型は比較できないため、DISTINCT として選択できません。

[解決済み] UPDATEエラーです。"REPLACEの引数1の引数データ型textは無効です"

[解決済み] 文字列から特定の文字を削除する

[解決済み] 日付から年内の週番号を取得する

[解決済み] SQL Serverでマテリアライズド・ビューを作成する方法は？

[解決済み] SQL Serverでdatetimeを切り捨てるにはどうすればよいですか？