时间戳

首页 > 解决方案 > 在 SQL Server 中使用插入语句优化必要的 while 循环

sql - 在 SQL Server 中使用插入语句优化必要的 while 循环

问题描述

我有一系列在 while 循环中运行一些数学的程序。他们正在计算运行平均值，其中知道先前计算的值对于获得下一个值是绝对必要的。一个项目与一组值配对，按日期排序，并使用 10 种不同的方法来计算运行平均值。

提前回答一些问题：每次运行的每个 id 的数组长度都是相同的，但如果数据丢失，可能会有空值。我事先不知道正在计算滚动平均值的 id 数量，并且每次都会有所不同。我事先不知道必要的迭代次数，每次都会有所不同。尽管在微积分上工作了一年多，但我还没有找到一种数学方法来将系统扁平化为一个选择/加入——5 个是非线性的，而且我在大学里只有 4 个学期的微积分，所以我无法理解出一个扁平化的解决方案（我为我正在使用的 10 种方法中的 5 种做了，

我的代码运行了，使用 while 循环的 5 个方法确实完成了，但是每个方法运行 5000 个项目（所有项目都有自己的迭代和计算）大约需要 15 分钟到 30 分钟。我需要能够扩展到 300000 个项目，所以 15 分钟是站不住脚的，特别是因为这是在数据库中运行的几十个程序之一。

下面是我的其中一种方法的代码示例。我不是在数学部分寻求帮助（因为需要循环的 5 个过程中的每一个的数学都不同），而是在用作循环背后意图的插入语句中：

create proc analysis.calculateavg_logarithmicshifted
    @maxi int --max number of iterations determined by separate proc
as
    
declare @icount int = 1;
declare @n int;
select @n = max(n) from #temp_pink; --comes from the procedure that calls this one, and is a way to identify the max array size
    
drop table if exists analysis.avglogarithmicshifted;
    
create table analysis.avglogarithmicshifted(    
    id nvarchar(64),    
    i int,  
    mu decimal(19, 6),  
    insertdate datetime,    
    avgname nvarchar(64)    
);
    
drop table if exists #temp_k;

--Pull only the historic data from the source
select  
    id, 
    k,  
    price
into #temp_k
from #temp_bbfull --original list of data, n elements per id
where history = 1;
    
while @icount <= @maxi  
    begin;      

    drop table if exists #temp_premu;
    
    --Calculate one sub-section of the rolling average
    select          
        id,         
        sum(log(price)) / (@n - sum(case when price is null then 1 else 0 end)) as premu        
    into #temp_premu        
    from #temp_k        
    where k between @icount and @icount + @n
    group by id;        
    
    drop table if exists #temp_f;           
    
    --Calculate the main component of the rolling average
    select          
        k.id,           
        @icount + @n as k,          
        exp(p.premu + (sum(power(log(k.price) - p.premu, 2)) / (2 * (@n - sum(case when k.price is null then 1 else 0 end))))) as price     
    into #temp_f        
    from #temp_k k          
    join #temp_premu p              
        on p.id= k.id           
    join #temp_bbfull bb                
        on bb.id= k.id              
        and bb.k = k.k      
    where k.k between @icount 
        and @icount + @n
    group by k.id, p.premu;         
    
    --Insert this iteration's rolling average into the table with incremented identifier k
    insert into #temp_k (id, k, price)      
    select          
        *       
    from #temp_f        
    where price is not null;        
    
    select @icount = @icount + 1;   
end;

--Insert final aggregated data into destination table
insert into analysis.avglogarithmicshifted (id, i, mu, insertdate, avgname)
select  
    k.id,   
    k.k - @n as I,  
    k.price as mu,  
    getdate() as insertdate,    
    'Logarithmic Shifted' as avgname
from #temp_k k
where k.k > @n  
    and k.price is not null;

名称和标识符已从我的原始代码更改，但没有其他任何内容。任何帮助将不胜感激。

我正在使用 SQL Server 17.9.1

标签： sqlsql-serverwhile-loopsql-insert

解决方案

总体而言，如果不对代码进行重大重新设计，我认为您将无法获得您正在寻找的接近 100 倍（2 个数量级）的改进。我不了解您的代码逻辑，因此无能为力，只能说 SQL Server 2016 支持允许基于集合计算移动平均线的窗口函数。此链接可能会有所帮助

以下是一些优化它的提示：

  • 连接许多临时表时，请确保具有大量数据的表具有索引。
  • 尽量减少用于存储中间计算结果的临时表的数量。
  • 确保中间临时表中的行数尽可能少。

从您给出的示例中，我可以看到一些可能的优化。由于我没有数据，我无法检查它们是否有帮助：

0 #temp_bbfull- 看起来这个表被用作数据源并且有很多数据。您必须有正确的索引。

CREATE INDEX IX_temp_bbfull ON #temp_bbfull( id, k )

1 尽可能限制你的工作集

select  id, k, price
into #temp_k
from #temp_bbfull --original list of data, n elements per id
-- As you are restricting iterations you might as well restrict the number of rows upfront
where history = 1 and k between @icount and @maxi;

2 这可能有帮助，也可能没有帮助。

CREATE INDEX IX_temp_k ON temp_k( id )

3 组合查询

select          
    k.id, @icount + @n as k,          
    exp(p.premu + (sum(power(log(k.price) - p.premu, 2)) / (2 * (@n - sum(case when k.price is null then 1 else 0 end))))) as price     
into #temp_f        
from #temp_k as k          
    join (
         select id, sum(log(price)) / (@n - sum(case when price is null then 1 else 0 end)) as premu        
         from #temp_k        
         where k between @icount and @icount + @n
         group by id ) as p
     on p.id= k.id
    join #temp_bbfull as bb on bb.id= k.id and bb.k = k.k      
where k.k between @icount and @icount + @n
group by k.id, p.premu;

等等。

推荐阅读