T-SQL optimization tips (2): Limits data size

The less data the T-SQL that SQL Server executes, the less resources it takes to complete the T-SQL. Therefore, the T-SQL statement should only return the required data, which helps improve the overall performance of SQL Server:

• Full use of indexes to avoid full table scanning;
• Reduce I/O operations for reading data;
• Saves cache for other possible SQL server operations;
• Avoid unnecessary network transmission;
• Reduce memory usage caused by useless data on the client.

And so on.

 

1) Limit the number of rows in the result set

In actual development, there are very few operations to return data from all rows in a table, so the number of rows in the returned result set should be limited, the where clause, top operator, and set rowcount options are possible options. It is worth noting that when the top operator and the Set rowcount option are used, the T-SQL stops when the number of rows in the result set reaches a certain number (determined by the two selected parameters, that is, the latter two options are more efficient than the WHERE clause, but obviously the WHERE clause applies more situations. If both the top operator and the Set rowcount option are used in the T-SQL, although superfluous, efficiency is not affected, and the Set rowcount option is higher than the top operator in terms of priority.

 

2) Limit the number of columns in the result set

For a two-dimensional table structure, apart from limiting the number of rows, it is to limit the number of columns. Avoid using this function unless all columns are required.Select *Select data.

 

In addition, you should avoid the Select Operation on the view, because many views may return much more data than we need, and the more efficient way is to bypass the view and directly select data from the table.

DOS & donts--
3. Avoid using select *;

[Note: All dos & donts summarized in this series areWith certain prerequisitesThe general premise is "unless necessary" and "unless necessary ".]