SQL Server:如何每小时运行一次查询，并将结果存储在表中?更新时替换它们 [英] SQL Server: How to run a query every hour, and store results in a table? Replace them on update

问题描述

我有一个耗时 5 分钟的繁重查询，我希望将其结果存储在一个表中，供后端查询.

I have this taxing query that takes 5 minutes, and I'd like to have its results stored in a table, which will be queried by a backend.

此外，表格应该每小时更新一次，并用新数据完全替换表格的内容.

Also, the table should be updated every hour and completely replace the table's content with the new data.

我找到了这个服务器代理解决方案，根据我的理解，我应该这样做:

I found this Server Agent solution, and from my understanding I should to this:

1. 创建结果表.
2. 创建一个存储过程，用于删除结果表数据、运行 5 分钟查询并插入新数据.
3. 创建间隔运行该过程的服务器代理作业.

这是最佳方式吗?

这是有问题的查询.它跨越了大量的行，我认为这是主要的速度冲击器.

Here is the query in question. It goes over a ton of rows, which I think is the main speed impactor.

WITH 
salesCTE AS (
    select
        concat(year(m.addate), '-', format(m.addate, 'MM')) AS ym,
        year(m.addate) as y,
        format(m.addate, 'MM') as m,
        sum(M.anvalue) as salesRev
    FROM tHE_Move m
    WHERE ( 
        RIGHT(LEFT(M.acKey,5),3) = '300'
        OR RIGHT(LEFT(M.acKey,5),3) = '305'
        OR RIGHT(LEFT(M.acKey,5),3) = '319'
        OR RIGHT(LEFT(M.acKey,5),3) = '380'
        OR RIGHT(LEFT(M.acKey,5),3) = '355'
        OR RIGHT(LEFT(M.acKey,5),3) = '360'
        OR RIGHT(LEFT(M.acKey,5),3) = '3X1'
        OR RIGHT(LEFT(M.acKey,5),3) = '395'
    ) and m.adDate between '01.01.2014' and '01.01.2030'
    GROUP BY 
        concat(year(m.addate), '-', format(m.addate, 'MM')),
        year(m.addate),
        format(m.addate, 'MM')
),
retailCTE AS (
    select
        concat(year(m.addate), '-', format(m.addate, 'MM')) AS ym,
        year(m.addate) as y,
        format(m.addate, 'MM') as m,
        sum(M.anvalue) as retailRev
    FROM tHE_Move m
    where (
        RIGHT(LEFT(M.acKey,5),3) = '321'
        OR RIGHT(LEFT(M.acKey,5),3) = '322'
        OR RIGHT(LEFT(M.acKey,5),3) = '323'
        OR RIGHT(LEFT(M.acKey,5),3) = '324'
        OR RIGHT(LEFT(M.acKey,5),3) = '325'
        OR RIGHT(LEFT(M.acKey,5),3) = '326'
        OR RIGHT(LEFT(M.acKey,5),3) = '327'
        OR RIGHT(LEFT(M.acKey,5),3) = '328'
        OR RIGHT(LEFT(M.acKey,5),3) = '329'
        OR RIGHT(LEFT(M.acKey,5),3) = '331'
        OR RIGHT(LEFT(M.acKey,5),3) = '332'
        OR RIGHT(LEFT(M.acKey,5),3) = '333'
        OR RIGHT(LEFT(M.acKey,5),3) = '334'
        OR RIGHT(LEFT(M.acKey,5),3) = '335'
        OR RIGHT(LEFT(M.acKey,5),3) = '336'
        OR RIGHT(LEFT(M.acKey,5),3) = '337'
        OR RIGHT(LEFT(M.acKey,5),3) = '338'
        OR RIGHT(LEFT(M.acKey,5),3) = '339'
        OR RIGHT(LEFT(M.acKey,5),3) = '341'
        OR RIGHT(LEFT(M.acKey,5),3) = '342'
        OR RIGHT(LEFT(M.acKey,5),3) = '343'
        OR RIGHT(LEFT(M.acKey,5),3) = '344'
        OR RIGHT(LEFT(M.acKey,5),3) = '345'
        OR RIGHT(LEFT(M.acKey,5),3) = '346'
        OR RIGHT(LEFT(M.acKey,5),3) = '347'
        OR RIGHT(LEFT(M.acKey,5),3) = '348'
        OR RIGHT(LEFT(M.acKey,5),3) = '349'
        OR RIGHT(LEFT(M.acKey,5),3) = '352'
        OR RIGHT(LEFT(M.acKey,5),3) = '353'
        ) and m.adDate between '01.01.2014' and '01.01.2030'
    GROUP BY 
        concat(year(m.addate), '-', format(m.addate, 'MM')),
        year(m.addate),
        format(m.addate, 'MM')
)
SELECT 
    s1.ym,
    s1.salesRev,
    (s1.salesRev / s2.salesRev - 1) * 100 salesDelta,
    r1.retailRev,
    (r1.retailRev / r2.retailRev - 1) * 100 retailDelta,
    s1.salesRev + r1.retailRev totalRev,
    ((s1.salesRev + r1.retailRev) / (s2.salesRev + r2.retailRev) - 1) * 100 totalDelta
FROM salesCTE s1
    left join salesCTE s2
        on s2.y = s1.y - 1 and s1.m = s2.m
    left join retailCTE r1
        on s1.ym = r1.ym
    left join retailCTE r2
        on r2.y = r1.y - 1 and r1.m = r2.m
order by s1.ym desc

推荐答案

正如评论中提到的，FORMAT 是一个巨大的性能打击者；而我的大我真的意味着大.拿这个 DB<>Fiddle 使用 FORMAT 和CONVERT 更改日期的值.使用 CONVERT 的查询执行时间为 46 毫秒(在 db fiddle 上)，而 FORMAT 查询花费了 1218 毫秒！慢了 26 倍.

As mentioned in the comment FORMAT is a massive performance hitter; and my massive i really do mean massive. Take this DB<>Fiddle which uses FORMAT and CONVERT to change the value of a date. The query using CONVERT executes if 46ms (on db fiddle), yet the FORMAT query took 1218 ms! That's 26 times slower.

将 format(m.addate, 'MM') 更改为 RIGHT('00' + CONVERT(varchar(2),DATEPART(MONTH,DATEADD(DAY,N-1,0))),2) 将对您的查询产生显着的性能优势.虽然后者看起来更复杂，但它会(如小提琴所示)以显着的优势执行 FORMAT.老实说，我建议永远不要使用FORMAT，微软在这个功能上确实有问题.

Changing format(m.addate, 'MM') to RIGHT('00' + CONVERT(varchar(2),DATEPART(MONTH,DATEADD(DAY,N-1,0))),2) will have significant performance benefits on your query. Although the latter looks more complex, it will (as the fiddle shows) out perform FORMAT by a significant margin. Honestly, I recommend never using FORMAT, Microsoft got things really wrong with that function.

不过，我也建议将 RIGHT(LEFT(M.acKey,5),3) 作为持久列添加到您的表中:

I do, also, however, suggest adding RIGHT(LEFT(M.acKey,5),3) as a persisted column to your table:

ALTER TABLE tHE_Move ADD {Meaningful Name} AS RIGHT(LEFT(M.acKey,5),3) PERSISTED;

然后您还可以将该值添加到索引(新的或现有的)中，这也将极大地提高查询的性能；也许将它推到只有几秒钟.

Then you can also add that value to an index (new or existing) and it'll also greatly benefit the performance of your query; maybe pushing it to only a few seconds.

这篇关于SQL Server:如何每小时运行一次查询，并将结果存储在表中?更新时替换它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！