[sql-server] SQL Server : 쿼리 속도는 빠르지 만 프로 시저 속도가 느림

쿼리가 빠르게 실행됩니다.

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

다시 다시, 실제로 재 컴파일하도록 속이려고합니다.

새 계획을 생성하기 위해 저장 프로 시저를 삭제하고 다시 만들었습니다.

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

또한 저장 프로 시저를 정의하려고 시도했습니다 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보다 ad-hoc 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 Server를 실행할 수없는 이유는 무엇입니까?

나의 다음 시도해야하는 것입니다 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)

나쁜 사람은 6 백만 줄을 열망하고있다. 다른 하나는 그렇지 않습니다.

참고 : 이것은 쿼리 조정에 관한 질문이 아닙니다. 번개처럼 빠르게 실행되는 쿼리가 있습니다. SQL Server가 저장 프로 시저에서 빠르게 실행되기를 원합니다.

답변

나는 원래 포스터와 같은 문제가 있었지만 인용 된 답변으로 문제가 해결되지 않았습니다. 저장 프로 시저에서 쿼리가 여전히 느리게 실행되었습니다.

여기에 다른 답변이 있습니다. “Parameter Sniffing” , Thanks Omnibuzz. 저장 프로 시저 쿼리에서 “로컬 변수”를 사용하는 것으로 요약하지만 더 이해하기 위해 원본을 읽으면 훌륭한 글입니다. 예 :

느린 길 :

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

빠른 방법 :

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT *
    FROM orders
    WHERE customerid = @LocCustID
END

이것이 다른 누군가에게 도움이되기를 바랍니다.이 작업을 수행하면 실행 시간이 5 분에서 6-7 초로 줄었습니다.

답변

문제를 발견했습니다. 저장 프로 시저의 느리고 빠른 버전의 스크립트는 다음과 같습니다.

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가 관련되어 있습니다.

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 onQA의 기본이다.

Microsoft는 KB296769 에서이 사실을 인정합니다 (버그 : SQL Enterprise Manager를 사용하여 연결된 서버 개체를 포함하는 저장 프로 시저를 만들 수 없음). 해결 방법은 ANSI_NULLS저장 프로 시저 대화 상자에 옵션 이 포함되어 있습니다.

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

답변

데이터베이스에이를 수행하십시오. 같은 문제가 있습니다-한 데이터베이스에서는 정상적으로 작동하지만 SSIS 가져 오기 (일반적인 복원 아님)를 사용 하여이 데이터베이스를 다른 데이터베이스에 복사하면이 문제는 대부분의 저장 프로 시저에서 발생합니다. 그래서 더 인터넷 검색을 한 후, 나는 Pinal Dave 의 블로그를 찾았습니다 (btw, 나는 그의 게시물의 대부분을 만났고 Pinal Dave에게 많은 도움을주었습니다) .

내 데이터베이스에서 아래 쿼리를 실행하고 문제가 해결되었습니다.

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

도움이 되었기를 바랍니다. 나에게 도움이 된 다른 사람들의 도움을 전달하십시오.

답변

나는 같은 문제에 직면하고 있었고이 게시물은 나에게 매우 도움이되었지만 게시 된 답변 중 어느 것도 내 특정 문제를 해결하지 못했습니다. 다른 사람을 도울 수 있기를 희망하는 나를 위해 일한 솔루션을 게시하고 싶었습니다.

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

검색어 끝에 OPTION (OPTIMIZE FOR (@now UNKNOWN))을 추가하십시오.

답변

이번에는 문제를 발견했습니다. 다음에 운이 좋지 않고 파악할 수 없으면 계획 동결을 사용 하고 잘못된 실행 계획에 대해 걱정하지 않아도됩니다.

답변

이 문제가 발생했습니다. 내 쿼리는 다음과 같습니다.

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

내 저장 프로 시저는 다음과 같이 정의되었습니다.

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

데이터 유형을 datetime 및 voila로 변경했습니다! 30 분에서 1 분으로 갔다!

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

답변

Report_Opener 테이블에서 통계 및 / 또는 인덱스를 다시 작성해 보셨습니까? 통계가 데이터베이스가 처음 취임했을 때의 데이터를 표시하는 경우 SP의 모든 재 준수는 아무 가치가 없습니다.

옵티마이 저가 매개 변수가 널이 아님을 알 수 있기 때문에 초기 쿼리 자체가 빠르게 작동합니다. SP의 경우 옵티마이 저는 매개 변수가 널이되지 않을 것이라고 확신 할 수 없습니다.