SQL Server性能 [英] SQL Server Performance

问题描述

有人可以向我解释为什么返回0行的查询需要很长时间才能执行吗？

它返回大约50列...似乎返回的列数具有巨大的影响因为我没有行，所以比我预期的要多得多。除非我减少列的数量，否则查询需要一整秒才能运行...

附加信息没有列是大数据类型，主要是整数和小字符串。

解决方案

虽然它返回0行但要获得50列，它必须去所有那些并阅读，因此问题。



你可能需要参考提高SQL Server性能



并在上面的链接中查找主题仅返回所需的行和列。

SQL查询的执行方式是取决于很多因素 - 它返回的数据量不是其中之一（它会影响网络流量）......

1.加入查询

2.涉及的表的索引

3.可用索引的类型（群集，非群集）

4.选择连接上的子查询，反之亦然...

5.实际建筑物所涉及的表格的数量（数据页面大小，索引页面大小......）

这种智慧不能像这里的类似答案那样教，但你可以研究这个主题：

http://technet.microsoft.com/ en-us / library / ms190610（v = SQL.105）.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ff647793.aspx [ ^ ]

没有足够的信息给你一个正确的答案，但我可以指出你文章 [ ^ ]来自其中一位老法师小号

Can anyone explain to me why a query that returns 0 rows takes a long time to execute?
It returns about 50 columns... It seems that the number of columns returned has a HUGE effect much more than I would expect since I am getting no rows. The query takes a full second to run unless I slim the number of columns...
Additional information none of the columns are large data types mostly integers and small strings.

解决方案

Though it returns 0 rows but to get 50 columns it has to go to all those and read and hence the issue.

you may wanna refer to Improving SQL Server Performance

and look for topic Return Only the Rows and Columns Needed in above link.

How a SQL query performs is depend on a lot of factors - the volume of the data it returns not one of them (it affects the network traffic)...
1. Joins in the query
2. Indexes on involved tables
3. Type of available indexes (clustered, non-clustered)
4. Choose sub-query over join and vica versa...
5. The actual build-up of the tables involved (data-page size, index-page size...)
This wisdom can not be taught in a sort answer like here, but you can research the subject:
http://technet.microsoft.com/en-us/library/ms190610(v=SQL.105).aspx[^]
http://msdn.microsoft.com/en-us/library/ff647793.aspx[^]

There's not enough info to give you a proper answer, but I can point you to an article[^] from one of the old Masters

这篇关于SQL Server性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！