投影次数对查询性能的影响 [英] effect of number of projections on query performance

问题描述

我希望提高从表中选择多个列的查询的性能。

解决方案

我可能会误解这个问题，但这里无论如何：

您选择的列的绝对编号不会产生巨大的差异。但是，您选择的列可能会根据表的索引方式产生显着的差异。

如果您只选择覆盖的列通过索引，那么数据库引擎可以仅使用查询的索引，而无需提取表数据。如果你甚至使用一个没有覆盖的列，它必须获取整个行（键查找），这将显着降低性能。有时它会杀死性能，所以数据库引擎选择做一个完整的扫描，而不是甚至麻烦的索引;它取决于所选择的行数。

因此，如果通过删除列，您可以将其转换为覆盖查询，那么它可以提高性能。否则，可能不是。

SQL Server 2005+的快速示例 - 假设这是您的表：

  ID int NOT NULL IDENTITY PRIMARY KEY CLUSTERED，
名称varchar（50）NOT NULL，
状态tinyint NOT NULL 
  pre> 
 
 
如果我们创建此索引：
  CREATE INDEX IX_MyTable 
 ON MyTable（Name）
  
然后这个查询会很快：
  SELECT ID 
 From MyTable 
 WHERE Name ='Aaron'
  
但这个查询会很慢（呃）：
  SELECT ID，名称，状态
 From MyTable 
 WHERE Name ='Aaron'
  
如果我们将索引更改为覆盖索引，即
  CREATE INDEX IX_MyTable 
在MyTable b INCLUDE（S​​tatus）
  
然后第二个查询再次变快，因为DB引擎不需要读取
 
I am looking to improve the performance of a query which selects several columns from a table. was wondering if limiting the number of columns would have any effect on performance of the query.
解决方案
I might be misunderstanding the question, but here goes anyway:

The absolute number of columns you select doesn't make a huge difference.  However, which columns you select can make a significant difference depending on how the table is indexed.

If you are selecting only columns that are covered by the index, then the DB engine can use just the index for the query without ever fetching table data.  If you use even one column that's not covered, though, it has to fetch the entire row (key lookup) and this will degrade performance significantly.  Sometimes it will kill performance so much that the DB engine opts to do a full scan instead of even bothering with the index; it depends on the number of rows being selected.

So, if by removing columns you are able to turn this into a covering query, then yes, it can improve performance.  Otherwise, probably not.  Not noticeably anyway.

Quick example for SQL Server 2005+ - let's say this is your table:
ID int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
Name varchar(50) NOT NULL,
Status tinyint NOT NULL
If we create this index:
CREATE INDEX IX_MyTable
ON MyTable (Name)
Then this query will be fast:
SELECT ID
FROM MyTable
WHERE Name = 'Aaron'
But this query will be slow(er):
SELECT ID, Name, Status
FROM MyTable
WHERE Name = 'Aaron'
If we change the index to a covering index, i.e.
CREATE INDEX IX_MyTable
ON MyTable (Name)
INCLUDE (Status)
Then the second query becomes fast again because the DB engine never needs to read the row.

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