sql-server - union效能 - 涵蓋索引




SQL Server:查詢速度快,但程序緩慢 (9)

查詢運行速度很快:

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相比,運行臨時SQL的SQL Server可能會更快嗎?

我真的不想

  • 在代碼中嵌入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)

糟糕的是,他們急於捲起600萬行; 另一個不是。

注意:這不是關於調整查詢的問題。 我有一個快速運行的查詢。 我只想讓SQL Server從存儲過程中快速運行。


- 這是解決方案:

create procedure GetOrderForCustomers(@CustID varchar(20))

as

begin

select * from orders

where customerid = ISNULL(@CustID, '')

end

- 而已


您是否嘗試過重建Report_Opener表中的統計信息和/或索引。 如果統計數據仍顯示數據庫首次啟動時的數據,那麼所有SP的重新編譯都不值得。

初始查詢本身可以快速工作,因為優化器可以看到參數永遠不會為空。 在SP的情況下,優化器不能確定該參數永遠不會為空。


我有另一個想法。 如果你創建這個基於表格的函數呢?

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

然後使用以下語句從中選擇(甚至將其放入SP中):

SELECT *
FROM tbfSelectFromView(@SessionGUID)

看起來發生了什麼(每個人都已經評論過),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成本查詢轉換成一個包含6百萬行的熱切假脫機:

慢: SET ANSI_NULLS OFF

快速: SET ANSI_NULLS ON

這個答案也可以做出來,因為視圖確實有一個連接子句,它說:

(table.column IS NOT NULL)

所以有一些NULL參與。

通過返回查詢分析器並運行,進一步證明了這一解釋

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

查詢很慢。

所以問題在於查詢是從存儲過程運行的。 問題是企業管理器的連接默認選項是ANSI_NULLS off ,而不是ANSI_NULLS on ,這是QA的默認選項。

Microsoft在KB296769承認這一事實(錯誤:不能使用SQL Enterprise Manager創建包含鏈接服務器對象的存儲過程)。 解決方法是在存儲過程對話框中包含ANSI_NULLS選項:

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

我面臨著同樣的問題,這篇文章對我非常有幫助,但是沒有任何發布的答案能解決我的具體問題。 我想發布對我有用的解決方案,希望它能幫助別人。

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

在查詢結束時,添加OPTION(OPTIMIZE FOR(@now UNKNOWN))


為您的數據庫執行此操作。 我有同樣的問題 - 它在一個數據庫中工作正常,但是當我使用SSIS導入(而不是通常的恢復)將此數據庫複製到另一個時,這個問題發生在我的大多數存儲過程中。 所以在google搜索一下之後,我找到了Pinal Dave博客(順便說一下,我遇到了他的大部分帖子,並且非常感謝Pinal Dave)

我在我的數據庫上執行下面的查詢,它糾正了我的問題:

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

希望這可以幫助。 只是傳遞幫助我的其他人的幫助。


這可能聽起來很愚蠢,並且從SessionGUID這個名字看起來很明顯,但該列是Report_Opener的唯一標識符嗎? 如果沒有,你可能想嘗試將它轉換為正確的類型,並給它一個鏡頭或聲明你的變量為正確的類型。

創建為計劃一部分的計劃可能不直觀,並在大型桌子上進行內部演員。


這次你發現你的問題。 如果下一次你不那麼幸運並且無法弄清楚,你可以使用計劃凍結並停止擔心錯誤的執行計劃。


雖然我通常反對它(儘管在這種情況下似乎你有一個真正的原因),你是否嘗試過在查詢的SP版本上提供任何查詢提示? 如果SQL Server在這兩個實例中準備了不同的執行計劃,您是否可以使用提示來告訴它要使用哪個索引,以便計劃與第一個匹配?

對於一些例子, 你可以去這裡

編輯:如果你可以在這裡發布你的查詢計劃,也許我們可以識別出所告訴的計劃之間的一些差異。

SECOND:將鏈接更新為特定於SQL-2000。 您必須向下滾動某種方式,但還有另一個標題為“表格提示”,這就是您要查找的內容。

THIRD:“壞”查詢似乎忽略了“Openers”表上的[IX_Openers_SessionGUID] - 任何添加INDEX提示以強制它使用該索引的機會都會改變什麼?





stored-procedures