SQL Server SQL语句调优技巧

核心提示： 这个选项不只在于其输出的装饰效果，它减少了从服务器端到客户端传递的信息量，SQL Server SQL语句调优技巧(4)，因此，它帮助降低了网络通信量并提高了你的事务整体响应时间，实耗时间是1620毫秒，如果不需要输出中的行数信息，传递单个信息的时间可以忽略，但考虑到这种情况

这个选项不只在于其输出的装饰效果。它减少了从服务器端到客户端传递的信息量。因此，它帮助降低了网络通信量并提高了你的事务整体响应时间。传递单个信息的时间可以忽略，但考虑到这种情况，一个脚本在一个循环里执行一些查询并且发送好几千字节无用的信息给用户。

为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。

-- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales
SET NOCOUNT ON
DECLARE @separator VARCHAR(25),
@message VARCHAR(25),
@counter INT,
@ord_nbr VARCHAR(20),
@order_date DATETIME,
@store_nbr INT,
@qty_sold INT,
@terms VARCHAR(12),
@title CHAR(6),
@starttime DATETIME
SET @STARTTIME = GETDATE()
SELECT @counter = 0,
@separator = REPLICATE( '-', 25 )
WHILE @counter < 9999
BEGIN
SET @counter = @counter + 1
SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5))
SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999')
SET @store_nbr =
CASE WHEN @counter < 999 THEN '6380'
WHEN @counter BETWEEN 1000 AND 2999 THEN '7066'
WHEN @counter BETWEEN 3000 AND 3999 THEN '7067'
WHEN @counter BETWEEN 4000 AND 6999 THEN '7131'
WHEN @counter BETWEEN 7000 AND 7999 THEN '7896'
WHEN @counter BETWEEN 8000 AND 9999 THEN '8042'
ELSE '6380'
END
SET @qty_sold =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11
WHEN @counter BETWEEN 3000 AND 5999 THEN 23
ELSE 37
END
SET @terms =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30'
WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60'
ELSE 'On Invoice'
END
-- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty)
FROM big_sales))
SET @title =
CASE WHEN @counter < 999 THEN 'MC2222'
WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222'
WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026'
WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106'
WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777'
WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218'
ELSE 'PS1372'
END
-- PRINT @separator
-- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT,
@counter ) ), 10, 4 )
-- PRINT @message
BEGIN TRAN
INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date],
[qty], [payterms], [title_id])
VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold,
@terms, @title)
COMMIT TRAN
END
SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20))
PRINT @message
/*
TRUNCATE table big_sales
INSERT INTO big_sales
SELECT * FROM sales
SELECT title_id, sum(qty)
FROM big_sales
group by title_id
order by sum(qty)
SELECT * FROM big_sales
*/

当带SET NOCOUNT OFF命令运行，实耗时间是5176毫秒。当带SET NOCOUNT ON命令运行，实耗时间是1620毫秒。如果不需要输出中的行数信息，考虑在每一个存储过程和脚本开始时增加SET NOCOUNT ON 命令将。