44042.htm
来自「一本很基础的SQL讲解」· HTM 代码 · 共 89 行 · 第 1/2 页
HTM
89 行
<P><A href="/files/uploadimg/20070330/1740059.jpg" target=_blank><IMG height=303 alt="" src="/files/uploadimg/20070330/1740059.jpg" width=450 border=0></A> </P>
<P><STRONG>SET NOCOUNT ON</STRONG></P>
<P>使用SET NOCOUNT ON 提高T-SQL代码速度的现象使SQL Server开发者和数据库系统管理者惊讶难解。你可能已经注意到成功的查询返回了关于受影响的行数的系统信息。在很多情况下，你不需要这些信息。这个SET NOCOUNT ON命令允许你禁止所有在你的会话事务中的子查询的信息，直到你发出SET NOCOUNT OFF。<BR> <BR>这个选项不只在于其输出的装饰效果。它减少了从服务器端到客户端传递的信息量。因此，它帮助降低了网络通信量并提高了你的事务整体响应时间。传递单个信息的时间可以忽略，但考虑到这种情况，一个脚本在一个循环里执行一些查询并且发送好几千字节无用的信息给用户。</P>
<P>为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> -- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales <BR> SET NOCOUNT ON <BR> DECLARE @separator VARCHAR(25),<BR> @message VARCHAR(25), <BR> @counter INT, <BR> @ord_nbr VARCHAR(20), <BR> @order_date DATETIME, <BR> @store_nbr INT, <BR> @qty_sold INT, <BR> @terms VARCHAR(12), <BR> @title CHAR(6), <BR> @starttime DATETIME <BR> SET @STARTTIME = GETDATE() <BR> SELECT @counter = 0, <BR> @separator = REPLICATE( '-', 25 ) <BR> WHILE @counter < 9999 <BR> BEGIN <BR> SET @counter = @counter + 1 <BR> SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5)) <BR> SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999') <BR> SET @store_nbr = <BR> CASE WHEN @counter < 999 THEN '6380' <BR> WHEN @counter BETWEEN 1000 AND 2999 THEN '7066' <BR> WHEN @counter BETWEEN 3000 AND 3999 THEN '7067' <BR> WHEN @counter BETWEEN 4000 AND 6999 THEN '7131' <BR> WHEN @counter BETWEEN 7000 AND 7999 THEN '7896' <BR> WHEN @counter BETWEEN 8000 AND 9999 THEN '8042' <BR> ELSE '6380' <BR> END <BR> SET @qty_sold = <BR> CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11 <BR> WHEN @counter BETWEEN 3000 AND 5999 THEN 23<BR> ELSE 37 <BR> END <BR> SET @terms = <BR> CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30' <BR> WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60' <BR> ELSE 'On Invoice' <BR> END <BR> -- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty) <BR> FROM big_sales)) <BR> SET @title = <BR> CASE WHEN @counter < 999 THEN 'MC2222' <BR> WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222' <BR> WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026' <BR> WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106' <BR> WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777' <BR> WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218' <BR> ELSE 'PS1372' <BR> END <BR> -- PRINT @separator <BR> -- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT, <BR> @counter ) ), 10, 4 ) <BR> -- PRINT @message <BR> BEGIN TRAN <BR> INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date], <BR> [qty], [payterms], [title_id]) <BR> VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold, <BR> @terms, @title) <BR> COMMIT TRAN <BR> END <BR> SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20)) <BR> PRINT @message <BR> /* <BR> TRUNCATE table big_sales <BR> INSERT INTO big_sales <BR> SELECT * FROM sales <BR> SELECT title_id, sum(qty) <BR> FROM big_sales <BR> group by title_id <BR> order by sum(qty) <BR> SELECT * FROM big_sales <BR> */ </P></PRE></TD></TR></TBODY></TABLE></P>
<P>当带SET NOCOUNT OFF命令运行，实耗时间是5176毫秒。当带SET NOCOUNT ON命令运行，实耗时间是1620毫秒。如果不需要输出中的行数信息，考虑在每一个存储过程和脚本开始时增加SET NOCOUNT ON 命令将。</P>
<P>#p# </P>
<P><STRONG>TOP 和 SET ROWCOUNT</STRONG></P>
<P>SELECT 语句中的TOP子句限制单个查询返回的行数，而SET ROWCOUNT限制所有后续查询影响的行数。在很多编程任务中这些命令提供了高效率。</P>
<P>SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE语句中设置可以被影响的最大行数。这些设置在命令执行时马上生效并且只影响当前的会话。为了移除这个限制执行SET ROWCOUNT 0。<BR>一些实际的任务用TOP or SET ROWCOUNT比用标准的SQL命令对编程是更有效率的。让我们在几个例子中证明：</P>
<P><STRONG>TOP n</STRONG></P>
<P>在几乎所有的数据库中最流行的一个查询是请求一个列表中的前N项。在 pubs数据库案例中，我们可以查找销售最好CD的前五项。比较用TOP，SET ROWCOUNT和使用ANSI SQL的三种方案。</P>
<P><STRONG>纯 ANSI SQL：</STRONG></P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> Select title,ytd_sales<BR> From titles a<BR> Where (select count(*)<BR> From titles b<BR> Where b.ytd_sales>a.ytd_sales<BR> )<5<BR> Order by ytd_sales DESC</P></PRE></TD></TR></TBODY></TABLE></P>
<P>这个纯ANSI SQL方案执行一个效率可能很低的关联子查询，特别的在这个例子中，在ytd_sales上没有索引支持。另外，这个纯的标准SQL命令没有过滤掉在ytd_sales的空值,也没有区别多个CD间有关联的情况。</P>
<P><STRONG>使用 SET ROWCOUNT：</STRONG></P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> SET ROWCOUNT 5<BR> SELECT title, ytd_sales<BR> FROM titles<BR> ORDER BY ytd_sales DESC<BR> SET ROWCOUNT 0</P></PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>使用 TOP n：</STRONG></P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> SELECT TOP 5 title, ytd_sales<BR> FROM titles<BR> ORDER BY ytd_sales DESC</P></PRE></TD></TR></TBODY></TABLE></P>
<P>第二个方案使用SET ROWCOUNT来停止SELECT查询，而第三个方案是当它找到前五行时用TOP n来停止。在这种情况下，在获得结果之前我们也要有一个ORDER BY子句强制对整个表进行排序。两个查询的查询计划实际上是一样的。然而，TOP优于SET ROWCOUNT的关键点是SET必须处理ORDER BY子句所需的工作表，而TOP 不用。</P>
<P>在一个大表上，我们可以在ytd_sales上创建一个索引以避免排序。查询将使用该索引找到前5行并停止。与第一个方案相比较，其扫描了整个表，并对每一行执行了一个关联子查询。在小表上，性能的差异是很小的。但是在一个大表上，第一个方案的处理时间可能是数个小时，而后两个方法是数秒。</P>
<P>当确定查询需要时，请考虑是否只需要其中几行，如果是，使用TOP子句将节约大量时间。</P>
<DIV align=right>【责任编辑：<A class=ln href="mailto:sunsj@51cto.com">火凤凰</A> TEL：（010）68476606-8007】</DIV></td> </tr> <tr> <td class="d_font4"> </td> </tr> </table>⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?