为什么如果我放置额外的空间或评论Select语句中的列 - 所有查询运行较慢 - Why if I put extra space or comment out a column from SELECT statement

发生了奇怪的事情。下面的查询不到一秒钟，但是如果我更改任何内容(例如额外的空间(，则整个查询运行7秒。或者如果我评论其中一条线(SELECT ISNULL(SUM(...) ...

从未见过...

SELECT...
...
FROM ...
--If I put extra space after SELECT - the whole query running 7 seconds
    WHERE EXISTS
        (SELECT * FROM tblClaims_ReservePayments RP 
            WHERE 
            CASE WHEN @coverageTypeId IS NULL THEN 1 
            ELSE 
                CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1 
        AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND RP.ClaimId = C.ClaimId)
    AND
        CASE WHEN @companyGuid IS NULL THEN 1 ELSE CASE WHEN CPI.CompanyGuid = @companyGuid THEN 1 ELSE 0 END END = 1
    AND 
        EXISTS (SELECT * FROM tblClaims_Claimants CLMNT WHERE CLMNT.ClaimID = C.ClaimID AND CLMNT.StatusId = 0)
    AND EXISTS (SELECT * FROM @Adjusters WHERE adjuster = C.InhouseAdjuster)
    ORDER BY
        CPI.PolicyNumber

发生了什么事？是参数嗅吗？

整个语句如下：

DECLARE
@dateFrom DATETIME = '2017-02-01',
@dateTo DATETIME = '2017-05-31',
@companyGuid UNIQUEIDENTIFIER = 'A95645F7-74CF-4551-BD28-5CBC5AD77EF2',-- NULL, Philadelphia
@coverageTypeId INT = NULL,
@inhouseAdjuster varchar(8000) = NULL
DECLARE @Adjusters TABLE(adjuster uniqueidentifier)
IF(@inhouseAdjuster IS NOT NULL AND @inhouseAdjuster <> '')
    BEGIN
    INSERT INTO @Adjusters
    SELECT Guid FROM dbo.StringOfGuidsToTable(@inhouseAdjuster, ',')
    END
ELSE
    BEGIN
    INSERT INTO @Adjusters
    SELECT InhouseAdjuster FROM tblClaims_Claim
    END

DECLARE @AdjusterString varchar(500)
SET @AdjusterString = ''
DECLARE @CoveringTable TABLE (Covering varchar(1000))
INSERT INTO @CoveringTable
    SELECT DISTINCT tblUsers.FirstName + ' ' + tblUsers.LastName FROM tblClaims_Claim
    inner join tblUsers on tblUsers.userguid = tblClaims_Claim.userguid
    WHERE tblClaims_Claim.InhouseAdjuster IN (SELECT adjuster FROM @Adjusters)
SELECT @AdjusterString = @AdjusterString + CASE WHEN @AdjusterString = '' THEN '' ELSE ', ' END  + Covering FROM @CoveringTable

SELECT
    CPI.PolicyNumber,
    CC.DateReported,--------------------------------------added by Oleg 5/1/2017
    C.LossDate as ForfeitureDate,
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription <> 'Expense' WHERE RP.ClaimId = C.ClaimId AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [BondAmount],
    dbo.GetEntityName(CPI.InsuredGuid) as Defendant,
    CC.UserDef_ClaimantId as CaseNumber,
    CC.CorporationName, -----------------------------------------------------------------------------------------------------------------------added by Oleg 5/1/2017
    PRIM_ADDRESS.City,-----------------------------------------------------------------------------------------------------------------------added by Oleg 5/1/2017
    PRIM_ADDRESS.State,-----------------------------------------------------------------------------------------------------------------------added by Oleg 5/1/2017
    PRIM_ADDRESS.County,-----------------------------------------------------------------------------------------------------------------------added by Oleg 5/1/2017
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription <> 'Expense' WHERE RP.ClaimId = C.ClaimId AND IsPayment = 1 AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [ClaimsPaid],
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription <> 'Expense' WHERE RP.ClaimId = C.ClaimId AND IsPayment = 0 AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [ClaimsReserveRemaining],
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription = 'Expense' WHERE RP.ClaimId = C.ClaimId AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0)AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [AllocatedReserved],
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription = 'Expense' WHERE RP.ClaimId = C.ClaimId AND IsPayment = 1 AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [AllocatedPaid],
    (SELECT ISNULL(SUM(ResPayAmount), 0) FROM tblClaims_ReservePayments RP INNER JOIN lstClaims_ReservePaymentTypes RPT ON RPT.ResPayTypeId = RP.ResPayTypeId AND RPT.ResPayTypedescription = 'Expense' WHERE RP.ClaimId = C.ClaimId AND IsPayment = 0 AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND CASE WHEN @coverageTypeId IS NULL THEN 1 ELSE CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1) AS [AllocatedReserveRemaining],
    (SELECT ISNULL(SUM(Amount), 0) FROM tblClaims_ClaimExpenses CE WHERE CE.ClaimId = C.ClaimId AND (DATEDIFF(d, @dateFrom, CE.DateEntered) >= 0 AND DATEDIFF(d, @dateTo, CE.DateEntered) <= 0)) AS [UnAllocated]
FROM
    tblClaims_Claim C
INNER JOIN 
    tblClaims_PolicyInformation CPI
ON
    CPI.ClaimId = C.ClaimId
INNER JOIN tblClaims_Claimants CC ON C.ClaimId = CC.ClaimId  --AND CC.StatusId = 0  -----------------------------------added by Oleg 5/1/2017
INNER JOIN tblClaims_Addresses PRIM_ADDRESS ON PRIM_ADDRESS.AddressId = CC.AddressId ----------------------------------added by Oleg 5/1/2017
CROSS APPLY (SELECT TOP 1 QuoteGUID FROM tblQuotes WHERE tblQuotes.ControlNo = C.ControlNo) t  ------------------------added by Oleg 5/1/2017
WHERE EXISTS
    (SELECT * FROM tblClaims_ReservePayments RP 
        WHERE 
        CASE WHEN @coverageTypeId IS NULL THEN 1 
        ELSE 
            CASE WHEN RP.CoverageTypeId = @coverageTypeId THEN 1 ELSE 0 END END = 1 
    AND (DATEDIFF(d, @dateFrom, RP.DateCreated) >= 0 AND DATEDIFF(d, @dateTo, RP.DateCreated) <= 0) AND RP.ClaimId = C.ClaimId)
AND
    CASE WHEN @companyGuid IS NULL THEN 1 ELSE CASE WHEN CPI.CompanyGuid = @companyGuid THEN 1 ELSE 0 END END = 1
AND 
    EXISTS (SELECT * FROM tblClaims_Claimants CLMNT WHERE CLMNT.ClaimID = C.ClaimID AND CLMNT.StatusId = 0)
AND EXISTS (SELECT * FROM @Adjusters WHERE adjuster = C.InhouseAdjuster)
ORDER BY
    CPI.PolicyNumber

更新：如果我在任何行中放置额外的空间 - 它将性能更改为7秒。

运行查询时，SQL Server将其编译为查询计划。

为了将相同的SQL查询与此计划匹配，数据库基于查询文本生成了查询的哈希代码(称为"查询哈希"(。

如果您以任何方式更改查询 - 即使是看似安全的事情，例如大写(其中不影响查询(，添加空格，添加评论 - 查询文本现在有所不同。SQL Server将为查询生成不同的查询哈希。

给出了一个新的查询哈希，将找不到此查询哈希的现有查询计划。因此，SQL优化器将创建一个新的查询计划。新的查询计划很可能与旧查询计划非常不同 - 可能与您建议的参数嗅探相比。

。

评论查询的功能部分确实会更改查询。例如，在SELECT子句中评论一行可以大大改变查询的性能，因为现在SQL Server可能不需要该列中的列。这意味着它可以选择其他索引或跳过书签查找，因此性能可以从根本上改变。

为什么如果我放置额外的空间或评论Select语句中的列 - 所有查询运行较慢

相关内容

最新更新

热门标签：