SQL Server——如何优化这个查询?

This query is taking an average of 4 seconds. It will become a subquery in a stored procedure and I need it to take a sub second. Here is the query:

这个查询平均花费4秒。它将成为存储过程中的子查询，我需要它以秒为单位。在这里查询:

(select customercampaignname + ' $' + convert(varchar, cast(amount as numeric(36,2) ) ) As 'Check_Stub_Comment2' from (
    select ROW_NUMBER() OVER (ORDER BY amount desc) as rownumber, customercampaignname, amount from (
        select * from (
               select distinct d.customercampaignname
                   ,sum(d.mastercurrencyamount) As amount

                from bb02_donation d
                      JOIN bb02_donationline dl on d.donationid = dl.donationid
                      JOIN bb02_fundraiserrevenuestream frs on dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid and frs.fundraiserid = 1869

                where d.customercampaignname is not null 
                      and d.customercampaignname != '' 
                group by d.CustomerCampaignName
        ) as x
) as sub ) as y where rownumber = 1)

3 个解决方案

#1

If you don't need to actually use the row number for anything, then I would just go with getting the TOP 1 row. The query could be simplified a lot. I like to start by first selecting from the table that will be filtered out the most by your predicates. Hopefully, you have a good index on frs.fundraiserid and all of the columns participating in the joins.

如果你不需要用行号来做任何事情，那么我只需要得到最上面的一行。这个查询可以简化很多。我喜欢先从表中开始选择，然后根据您的谓词过滤掉。希望您有一个很好的索引，关于frs.募款活动和参与联接的所有列。

SELECT 
 TOP 2 customercampaignname + ' $' + CONVERT(VARCHAR(255), CAST(SUM(d.mastercurrencyamount) AS NUMERIC(36,2) ) ) AS 'Check_Stub_Comment2',
 ROW_NUMBER() OVER(ORDER BY SUM(d.mastercurrencyamount) DESC) as rownumber
FROM bb02_fundraiserrevenuestream frs
JOIN bb02_donationline dl ON
 dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid
JOIN bb02_donation d ON
 d.donationid = dl.donationid          
WHERE frs.fundraiserid = 1869
 AND d.customercampaignname IS NOT NULL
 AND d.customercampaignname != '' 
GROUP BY d.CustomerCampaignName
ORDER BY SUM(d.mastercurrencyamount) DESC

Since you need to be able to select either the 1st or 2nd row, then wrap it up in a CTE or subquery.

因为您需要能够选择第1或第2行，然后在CTE或子查询中结束它。

WITH topcampaign AS (
SELECT 
 TOP 2 customercampaignname + ' $' + CONVERT(varchar(255), CAST(SUM(d.mastercurrencyamount) AS NUMERIC(36,2) ) ) AS 'Check_Stub_Comment2',
 ROW_NUMBER() OVER(ORDER BY SUM(d.mastercurrencyamount) DESC) as rownumber
FROM bb02_fundraiserrevenuestream frs
JOIN bb02_donationline dl ON
 dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid
JOIN bb02_donation d ON
 d.donationid = dl.donationid          
WHERE frs.fundraiserid = 1869
 AND d.customercampaignname IS NOT NULL
 AND d.customercampaignname != '' 
GROUP BY d.CustomerCampaignName
ORDER BY SUM(d.mastercurrencyamount) DESC
)
SELECT * from topcampaign WHERE rownumber = 1

As another possible optimization, I took the CONVERT out of the CTE and put it in the final select. Not sure if that helps much.

作为另一种可能的优化，我将CTE的转换从CTE中取出，并将其放入最终的select中。不确定这是否有帮助。

WITH topcampaign AS (
SELECT 
 TOP 2 
 customercampaignname,
 SUM(d.mastercurrencyamount) AS amount,
 ROW_NUMBER() OVER(ORDER BY SUM(d.mastercurrencyamount) DESC) as rownumber
FROM bb02_fundraiserrevenuestream frs
JOIN bb02_donationline dl ON
 dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid
JOIN bb02_donation d ON
 d.donationid = dl.donationid          
WHERE frs.fundraiserid = 1869
 AND d.customercampaignname IS NOT NULL
 AND d.customercampaignname != '' 
GROUP BY 
 d.CustomerCampaignName
ORDER BY 
 SUM(d.mastercurrencyamount) DESC
)
SELECT 
 rownumber,
 customercampaignname + ' $' + CONVERT(varchar(255), CAST(amount AS NUMERIC(36,2) ) ) AS 'Check_Stub_Comment2'
FROM topcampaign 
WHERE rownumber = 1

#2

I don't know if this will run faster, but by my reckoning, this can be simplified to:

我不知道这是否会跑得更快，但根据我的计算，这可以简化为:

Select top 1 customercampaignname + ' $' + convert(varchar(255), cast(sum(d.mastercurrencyamount) as numeric(36,2) ) ) As amount
from bb02_donation d JOIN
     bb02_donationline dl
     on d.donationid = dl.donationid JOIN
     bb02_fundraiserrevenuestream frs
     on dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid and frs.fundraiserid = 1869
where d.customercampaignname is not null and d.customercampaignname != '' 
group by d.CustomerCampaignName
order by sum(d.mastercurrencyamount)

#3

I don't know if this makes any difference

我不知道这是否有什么区别。

;WITH cte AS 
(
    select *, ROW_NUMBER() OVER (ORDER BY amount desc) as rownumber
    from (
        select distinct d.customercampaignname, sum(d.mastercurrencyamount) As amount
        from bb02_donation d
        INNER JOIN bb02_donationline dl on d.donationid = dl.donationid
        INNER JOIN bb02_fundraiserrevenuestream frs on dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid and frs.fundraiserid = 1869
        where d.customercampaignname is not null 
                and d.customercampaignname != '' 
        group by d.CustomerCampaignName
    ) as x
)
SELECT TOP 1 *
FROM cte 
ORDER BY RowNumber

if the tables are quite you might need to help filtering out things, for example, you could pick only bb02_fundraiserrevenuestream.fundraiserid = 1869 and then do the rest of the query. I don't know, it could be a matter of trying and see the execution plan.

如果这些表是您可能需要帮助过滤掉的东西，例如，您可以只选择bb02_募款服务器。筹集资金= 1869，然后执行剩下的查询。我不知道，这可能是一个尝试和看到执行计划的问题。

;WITH Frs AS 
( SELECT dl.fundraiserrevenuestreamid 
  FROM bb02_fundraiserrevenuestream
  WHERE fundraiserid = 1869
)
, cte AS (
    select d.customercampaignname, sum(d.mastercurrencyamount) As amount
    from Frs
    INNER JOIN bb02_donationline dl  ON dl.fundraiserrevenuestreamid = frs.fundraiserrevenuestreamid
    INNER JOIN bb02_donation d on d.donationid = dl.donationid
    where d.customercampaignname is not null 
            and d.customercampaignname != '' 
    group by d.CustomerCampaignName
)
SELECT TOP 1  customercampaignname + ' $' + convert(varchar, cast(amount as numeric(36,2) ) ) As 'Check_Stub_Comment2' 
FROM cte
ORDER BY sum(d.mastercurrencyamount)

As always, INDEXES could be the solution for these performance problems.

与往常一样，索引可能是这些性能问题的解决方案。