17:07
Unknown
Query Optimization Tips:
• Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results in some very slow queries.
• Try to restrict the queries result set by using the WHERE clause. This can reduce network traffic and boost the overall performance of the query.
• Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
• Use views and stored procedures instead of heavy-duty queries. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.
• Try to avoid using SQL Server cursors, whenever possible. It can result in performance degradation when compared to select statement.
• If you need to return the total table's row count, you can use alternative way instead of
• SELECT COUNT (*) statement.
• You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement
• SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
• Use table variables instead of temporary tables, if your resultset has less than 100 rows.
• Try to avoid the HAVING clause, whenever possible.
• Try to avoid using the DISTINCT clause, whenever possible.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
• Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
• Try to use UNION ALL statement instead of UNION, whenever possible.
• Do not use optimizer hints in your queries.
• Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results in some very slow queries.
• Try to restrict the queries result set by using the WHERE clause. This can reduce network traffic and boost the overall performance of the query.
• Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
• Use views and stored procedures instead of heavy-duty queries. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.
• Try to avoid using SQL Server cursors, whenever possible. It can result in performance degradation when compared to select statement.
• If you need to return the total table's row count, you can use alternative way instead of
• SELECT COUNT (*) statement.
• You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement
• SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
• Use table variables instead of temporary tables, if your resultset has less than 100 rows.
• Try to avoid the HAVING clause, whenever possible.
• Try to avoid using the DISTINCT clause, whenever possible.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
• Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
• Try to use UNION ALL statement instead of UNION, whenever possible.
• Do not use optimizer hints in your queries.
Posted in:
SQL Server Tutorial
0 comments:
Post a Comment