性能调优SQL查询 [英] Performance tuning SQL query
本文介绍了性能调优SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好。我有下一个查询:
DECLARE @StartDate DATETIME = GETDATE();
DECLARE @i INT = 0;
DECLARE @Result TABLE(
OrderID INT,
OrderDetailID INT,
Date DATETIME,
Qty SMALLINT,
ProductID INT,
UnitPrice MONEY
)
WHILE @ i< 10000
BEGIN
INSERT INTO @Result
(
OrderID,
OrderDetailID,
日期,
数量,
ProductID,
UnitPrice
)
SELECT
OrderID,
OrderDetailID,
日期,
数量,
ProductID,
UnitPrice
FROM OrderDetail
WHERE ProductID = 201
PRINT @i;
SET @i = @i + 1;
END
PRINT'执行时间:'+ CONVERT(varchar,GETDATE() - @StartDate,114);
运行时间 约2分钟。我怎样才能加快
几秒钟
此查询?
插入数据量:15行。
因此,我必须得到:
(15行(s)受影响)1; (15行受影响) 2 ...(15行(s)受影响)10000。
解决方案
首先,检查执行计划和指数状态
是字段"ProducID" PK或指数内部?
这里的第二个问题是你运行while循环的原因吗?
Hi all. I have next query:
DECLARE @StartDate DATETIME = GETDATE(); DECLARE @i INT = 0; DECLARE @Result TABLE( OrderID INT, OrderDetailID INT, Date DATETIME, Qty SMALLINT, ProductID INT, UnitPrice MONEY ) WHILE @i < 10000 BEGIN INSERT INTO @Result ( OrderID, OrderDetailID, Date, Qty, ProductID, UnitPrice ) SELECT OrderID, OrderDetailID, Date, Qty, ProductID, UnitPrice FROM OrderDetail WHERE ProductID = 201 PRINT @i; SET @i = @i + 1; END PRINT 'Execution time : ' + CONVERT(varchar, GETDATE() - @StartDate, 114);
Run time about 2 minutes. How I can speed up
a few seconds
this query?
Amount of inserted data:15 rows.
As a result, I have to get :
(15 row(s) affected) 1 ; (15 row(s) affected) 2 ... (15 row(s) affected) 10000.
解决方案
well the first place is to check the excecution plan and the index state
is the Field "ProducID" the PK or inside a Index ?
and the second Question here is why you run a while loop ?
这篇关于性能调优SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文