提高SQL Server中插入的速度

问题描述：

我试图在SQL Server 2008中优化以下查询。执行计划显示此查询相对于批处理为90％。插入显示75％。我正在寻找一个选项来进行批量插入，并发插入等。有人可以抛开光速加快这一进程。我可以做SqlBulkCopy或使用OpenRowset功能吗？我已确保有所有在那里的条件提高SQL Server中插入的速度

INSERT INTO [xxxxxxx].[dbo].[xxxxxxx] WITH (Tablock) 
    SELECT 
     s.companyId, 
     ti.tickerSymbol, 
     s.securityName, 
     ex.exchangeName, 
     cur.currencyName, 
     primaryFlag = CASE 
         WHEN ti.primaryFlag = 1 AND s.primaryFlag = 1 
          THEN 1 
         ELSE 0 
         END, 
     ti.tradingItemId, 
     peq.pricingDate, peq.priceOpen, 
     peq.priceHigh, peq.priceLow, peq.priceMid, 
     peq.priceClose, peq.priceBid, peq.priceAsk, 
     peq.volume, peq.adjustmentFactor, peq.VWAP, 
     mc.marketCap, mc.TEV, mc.sharesOutstanding 
    FROM 
     ciqsecurity s 
    INNER JOIN 
     CoreReferenceStaging.dbo.MarketDataTemp1 a ON a.companyId = s.companyId 
    INNER JOIN 
     ciqtradingitem ti ON s.securityid = ti.securityid 
    LEFT JOIN 
     ciqpriceequity peq ON peq.tradingitemid = ti.tradingitemid 
    LEFT JOIN 
     CoreReferenceStaging.dbo.MarketDataTemp2 mc ON (mc.companyId= s.companyId AND mc.pricingDate = peq.pricingDate) 
    INNER JOIN 
     ciqExchange ex ON ex.exchangeId = ti. exchangeId 
    INNER JOIN 
     ciqCurrency cur ON cur.currencyid = ti.currencyId 
    ORDER BY 
     peq.pricingDate DESC

我试图切片INSERT INTO批次字段的索引，但它似乎需要更长的时间。对于e.g以下

DECLARE @BatchSize int = 1000 

WHILE 1 = 1 
BEGIN 

INSERT INTO [xxxxxx].[dbo].[xxxxxxx] with (Tablock) 
select 
    s.companyId, 
    ti.tickerSymbol, 
    s.securityName, 
    ex.exchangeName, 
    cur.currencyName, 
    primaryFlag = case when ti.primaryFlag = 1 and s.primaryFlag = 1 then 1 else 0 end, 
    ti.tradingItemId, 
    peq.pricingDate, 
    peq.priceOpen, 
    peq.priceHigh, 
    peq.priceLow, 
    peq.priceMid, 
    peq.priceClose, 
    peq.priceBid, 
    peq.priceAsk, 
    peq.volume, 
    peq.adjustmentFactor, 
    peq.VWAP, 
    mc.marketCap, 
    mc.TEV, 
    mc.sharesOutstanding 
from ciqsecurity s 
    inner join CoreReferenceStaging.dbo.MarketDataTemp1 a on a.companyId = s.companyId 
    inner join ciqtradingitem ti on s.securityid = ti.securityid 
    left join ciqpriceequity peq on peq.tradingitemid = ti.tradingitemid 
    left join CoreReferenceStaging.dbo.MarketDataTemp2 mc on (mc.companyId= s.companyId and mc.pricingDate = peq.pricingDate) 
    inner join ciqExchange ex on ex.exchangeId = ti. exchangeId 
    inner join ciqCurrency cur on cur.currencyid = ti.currencyId 
order by peq.pricingDate desc 

IF @@ROWCOUNT < @BatchSize BREAK END

结果与组统计上

Table 'ciqExchange'. Scan count 17, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'ciqCurrency'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'MarketDataTemp1'. Scan count 10, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'ciqSecurity'. Scan count 500, logical reads 53703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'ciqTradingItem'. Scan count 17, logical reads 33082, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 11382, logical reads 1193682, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'ciqPriceEquity'. Scan count 15681, logical reads 96425, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'MarketDataTemp2'. Scan count 17, logical reads 1648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

执行计划1

执行计划2

索引会减慢插入。你需要你创建的所有索引吗？ – Leonidas199x

所以请等待...... INSERT部分缓慢，还是选择？加插插入具有挑战性。加强选择以获得所有记录插入内存......是瓶颈。并且是select上的索引而不是目标表上的索引？禁用日志记录/恢复......这是事务性系统的所有开销，可以使插入缓慢。 – xQbert

是按需插入的顺序？ – GuidoG

答

该查询似乎很好地调整，似乎它没有临界点返工。它只是提取超过40-50万条记录，只需要11秒就可以执行。在我看来，你不能提高更多的查询... 你可以对索引碎片进行一些检查，如果重建它们，但你不会在5秒以内...我认为查询它确定，并有不是瓶颈...

提高SQL Server中插入的速度

相关推荐