代码之家 › 专栏 › 技术社区 › Anand

需要帮助解决查询速度慢的问题吗

query-optimization sql-server sql

Anand · 技术社区 · 7 年前

ReconCollaterlExternal (1194994行)和 ReconCollateralInternal (888060行)。

DECLARE @asofdate DATE = '2018-08-29';
DECLARE @threshold INT = 25

SELECT  A.* FROM (
        SELECT ri.AsOfDate, ri.Portfoliocode, SUM( ABS(ri.netamount)) SumAbsEmcMtm, SUM( ABS(re.netamount)) SumAbsBrokerMtm, 
        100*(SUM( ABS(ri.netamount))- SUM( ABS(re.netamount)))/SUM( ABS(ri.netamount)) PctMtmBreak 
        FROM ReconCollateralExternal ri
        INNER JOIN ReconCollateralInternal re ON re.portfoliocode = ri.portfoliocode AND  re.AsOfDate = ri.AsOfDate 
        WHERE ri.asofdate = @asofdate GROUP BY ri.portfoliocode , ri.AsOfDate  HAVING SUM( ABS(ri.netamount)) != 0 
         ) A
WHERE ABS(A.PctMtmBreak) >= @threshold ORDER BY ABS(A.PctMtmBreak) DESC;

上有索引 AsOfDate , PortfolioCode 在两张桌子上。查询运行需要7秒,我认为这太长了。

我很感激你能帮我加快查询速度。

3 回复 | 直到 7 年前

Ivan Starostin 7 年前

试试这个。因为每个表中都有适当的索引,所以我们可以分别过滤它们,然后聚合,而不是使用sorg+join,然后连接聚合值。

DECLARE @asofdate DATE = '2018-08-29';
DECLARE @threshold INT = 25

SELECT  
  @asofdate AsOfDate, 
  A.Portfoliocode,
  A.SumAbsEmcMtm,
  A.SumAbsBrokerMtm,
  A.PctMtmBreak 
FROM 
  (
     SELECT
        ri.Portfoliocode, ri.SumAbsEmcMtm, re.SumAbsBrokerMtm,
        100*(ri.SumAbsEmcMtm- re.SumAbsBrokerMtm)/ri.SumAbsEmcMtm PctMtmBreak 
     FROM
     (
        SELECT
           ri.portfoliocode,
           SUM(ABS(ri.netamount)) SumAbsEmcMtm
        FROM ReconCollateralExternal ri
        WHERE ri.asofdate = @asofdate 
        GROUP BY ri.portfoliocode
        HAVING SUM( ABS(ri.netamount)) != 0 
     ) ri
     INNER JOIN 
     (
        SELECT
           re.portfoliocode,
           SUM(ABS(re.netamount)) SumAbsBrokerMtm
        FROM ReconCollateralInternal re
        WHERE re.asofdate = @asofdate
        GROUP BY re.portfoliocode
     ) re ON re.portfoliocode = ri.portfoliocode
  ) A
WHERE ABS(A.PctMtmBreak) >= @threshold 
ORDER BY ABS(A.PctMtmBreak) DESC;

试试看。

Gordon Linoff 7 年前

这是您的查询(重新格式化了一点):

SELECT ri.AsOfDate, ri.Portfoliocode, SUM( ABS(ri.netamount)) as SumAbsEmcMtm, SUM( ABS(re.netamount)) as SumAbsBrokerMtm, 
        100*(SUM( ABS(ri.netamount))- SUM( ABS(re.netamount)))/SUM( ABS(ri.netamount)) as PctMtmBreak 
FROM ReconCollateralExternal ri INNER JOIN
     ReconCollateralInternal re 
     ON re.portfoliocode = ri.portfoliocode AND re.AsOfDate = ri.AsOfDate 
WHERE ri.asofdate = @asofdate
GROUP BY ri.portfoliocode, ri.AsOfDate 
HAVING SUM( ABS(ri.netamount)) <> 0 AND
       100*(SUM( ABS(ri.netamount))- SUM( ABS(re.netamount)))/SUM( ABS(ri.netamount)) >= @threshold
ORDER BY PctMtmBreak DESC;

(子查询不影响性能。我只是删除了它,因为它更容易让我形象化的处理。在您的外部文件中使用别名 HAVING 使子查询合理。)

从索引开始 JOIN s和 WHERE 条件。我建议:

ReconCollateralExternal(asofdate, portfoliocode, netamount)
ReconCollateralInternal(portfoliocode, asofdate)

netamount

这可能是,也可能不是一个大的性能提升。这取决于处理的数据量 GROUP BY

deroby 7 年前

我想知道 HAVING SUM( ABS(ri.netamount)) != 0
正如Ivan Starostin已经提到的,没有必要 GROUP BY AsOfDate 列,因为它是一个常数。
因为优化器似乎更喜欢使用合并联接,所以我们可以尝试通过添加覆盖索引来避免这两种排序

例如

CREATE INDEX idx_test ON ReconCollateralExternal (AsOfDate, PortofolioCode) INCLUDE (NetAmount)
CREATE INDEX idx_test ON ReconCollateralInternal (AsOfDate, PortofolioCode) INCLUDE (NetAmount)

请记住,没有免费的午餐:索引可能会使查询运行(稍微)快一点(?)但是它会对其他表上的insert/update/delete操作产生(很小的)性能影响!

DECLARE @asofdate DATE = '2018-08-29';
DECLARE @threshold INT = 25


SELECT  Portfoliocode,
        AsOfDate = @asofdate,
        SumAbsEmcMtm,
        SumAbsBrokerMtm,
        100 * (SumAbsEmcMtm - SumAbsBrokerMtm) / SumAbsEmcMtm PctMtmBreak 

  FROM (SELECT ri.Portfoliocode, 
               SUM( ABS(ri.NetAmount)) SumAbsEmcMtm, 
               SUM( ABS(re.NetAmount)) SumAbsBrokerMtm
               -- 100 * (SUM (ABS(ri.NetAmount)) - SUM( ABS(re.netamount))) / SUM( ABS(ri.netamount)) PctMtmBreak 
          FROM ReconCollateralExternal ri
          JOIN ReconCollateralInternal re 
            ON re.PortfolioCode = ri.PortfolioCode 
           AND re.AsOfDate = @asofdate  -- ri.AsOfDate 
          WHERE ri.asofdate = @asofdate 
          GROUP BY ri.PortfolioCode 
         HAVING SUM( ABS(ri.NetAmount)) != 0 
         ) A
WHERE ABS(100 * (SumAbsEmcMtm - SumAbsBrokerMtm) / SumAbsEmcMtm ) >= @threshold 
ORDER BY ABS(100 * (SumAbsEmcMtm - SumAbsBrokerMtm) / SumAbsEmcMtm ) DESC;

PS:请记住,当您在区分大小写的服务器上部署此代码时,它不会编译,因为例如PortofolioCode!=端口密码