SQL 性能缓慢（改进插入临时表）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38080337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Performance Slow (Improve Insert Into Temp Table)
提问by Ryan Gavin
I have been working on an old auditing stored procedure that is running slowly, I have had some success from applying indexing and making the query more sargable.
我一直在研究一个运行缓慢的旧审计存储过程，我在应用索引和使查询更加可查询方面取得了一些成功。
However the stored procedure is still taking over one minute to complete. I think the problem lays in the temp table insert. I did try to apply an index to the temp table but this will only reduce performance as:
但是，存储过程仍然需要一分钟多的时间才能完成。我认为问题在于临时表插入。我确实尝试将索引应用于临时表，但这只会降低性能：
The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes. The insert statement is the only operation that cannot directly benefit from indexing because it has no where clause.
表上的索引数是影响插入性能的最主要因素。表的索引越多，执行速度就越慢。insert 语句是唯一不能直接从索引中受益的操作，因为它没有 where 子句。
SQL code
SQL代码
I have posted below the snippet of code from the auditing procedure that is taking the longest time to process and included the execution plan.
我在下面发布了审计程序中花费最长时间来处理并包括执行计划的代码片段。
SELECT dbo.[Audit Result Entry Detail].PK_ID,
dbo.[Audit Result Entry Detail].......
45-50 other columns selected from Audit Result Entry Detail
(Note i need to select all these)
dbo.[Audit Register].Audit_Date,
dbo.[Audit Register].Audit_Type,
dbo.[Audit Register].ContextUser
INTO #temp5
FROM dbo.[Audit Result Entry Detail]
INNER
JOIN dbo.[Audit Register]
ON dbo.[Audit Result Entry Detail].FK_RegisterID = dbo.[Audit Register].PK_ID
INNER
JOIN (
SELECT MAX(Audit_Date) AS DATE,
FK_RegisterID
FROM dbo.[Audit Result Entry Detail]
INNER
JOIN dbo.[Audit Register]
ON dbo.[Audit Result Entry Detail].FK_RegisterID = dbo.[Audit Register].PK_ID
WHERE Audit_Date >= @StartDate AND Audit_Date < DATEADD(dd,1,@EndDate)
--WHERE ((SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) >= @StartDate
-- AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) <= @EndDate)
AND part_number = @ParticipantNumber
GROUP
BY FK_RegisterID
) dt
ON dbo.[Audit Result Entry Detail].FK_RegisterID = dt.FK_RegisterID
AND dbo.[Audit Register].Audit_Date = dt.[date]
WHERE part_number = @ParticipantNumber
Execution Plan:
执行计划：
I believe the bottleneck is the #temp5 table, my question is there a way I can speed up the insert into the temp table or is there a better alternative to a temp table?
我相信瓶颈是#temp5 表，我的问题是有没有一种方法可以加快插入临时表的速度，或者是否有更好的临时表替代方案？
采纳答案by Slava Murygin
I guess there could be few different causes of the problem.
At least, the assumption is that because of the big number of fields in one record can cause Page overflow in Temp Heap table. Along with that there might be contention in tempdb or even it's slowness. So, the general suggestions might be:
1. As already suggested, try to do not use temp table at all.
2. If possible, try to limit record size to fit into one page. Or even better, if you can fit 2-3 records into one page.
3. If it is possible, use "staging" table with clustered index on it, instead of temp table. Do not truncate that table, only do deletes.
4. If using temp table: create table before the insert with clustered index on it.
5. Fallow Paul Randal's suggestions about TempDB: http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
我想问题可能有几个不同的原因。至少，假设是因为一条记录中的大量字段会导致临时堆表中的页面溢出。与此同时，tempdb 中可能存在争用，甚至速度缓慢。因此，一般建议可能是：
1. 正如已经建议的那样，尽量不要使用临时表。
2. 如果可能，尽量限制记录大小以适合一页。或者甚至更好，如果您可以在一页中放入 2-3 条记录。
3. 如果可能，请使用带有聚集索引的“暂存”表，而不是临时表。不要截断那个表，只做删除。
4.如果使用临时表：在插入之前创建表并带有聚集索引。
5. Fallow Paul Randal 关于 TempDB 的建议：http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
For deeper troubleshooting, I'd suggest, during the execution of that query, to capture waits, locks, I/O, memory and CPU activity.
对于更深入的故障排除，我建议在执行该查询期间捕获等待、锁定、I/O、内存和 CPU 活动。
