SQL Performance Improvement Techniques

Original address: Http://www.codeproject.com/Tips/1023621/SQL-Performance-Improvement-Techniques

This article provides various options to improve the performance in database. 1) Re-write Query:

If Any, the is taking much time-to-execute then the first step was to rewrite the query. Perform thorough analysis and move towards identifying the root cause. The below is few guidelines to improve the performance of a query.

• Avoid * in SELECT and specify the column names when dealing with JOINs on multiple tables.
• Avoid repeated logic, unnecessary subqueries and unnecessary JOINs
• Some cases EXIST would benefit instead of JOIN
• Use UNION ALL instead of union
• Use EXISTS instead by when necessary
• Use with clause (Oracle) or Common Table Expressions (SQL Server)
• Order or position of the columns in WHERE clause would play vital role to improve the performance and ensure the proper in Dex is being used by the query.
• In SQL Server, the adopt using SET TRANSACTION Isolation level READ uncommitted or with (NOLOCK).
• Use hints if necessary. There is table hints, query hints and plan hints.

2) Create a highly selective Index:

Index helps to retrieve the data fast and basically to speed up the searches/queries. The below is the few guide, lines to create index.

• When the table was large and frequent selectivity of a table is less than 10%.
• Do not create index on low cardinality columns and also the index was not required for small tables.
• Frequently used columns in the WHERE clause and columns used in joins for multiple tables.
• Order or position of a column in an index also plays a vital role. In general, you should put the column expected to being used most often first in the index.
• Limit the number of indexes on a table. The more indexes, overhead as the indexes need to being updated on every DML operation.

3) Limit the number of Columns and Rows:

In some cases the applications is not a use of all the columns and rows fetched from the database. Means pull is only the required columns and required rows.

Example if query (SELECT *) is pulling more than, and the application may not be use those ation. And if any application are displaying data in page wise format then better to retrieve the corresponding records of the Pag e instead of retrieving all the records.

4) Temporary tables:

The temporary tables should was used when there was a strong reason. Basically if any long running query was used in many places in procedure/function then better we store the results of long Running query in a temporary table and reuse it later. Once It is completed then delete it to free the memory and does not wait for the table to was automatically deleted when the Connection is ended. Using index on the temporary tables would help when you deal with very large tables. The temporary table can be used as the alternative for the cursors.

5) pre-stage Data:

There is some applications do large imports from database and perform selectivity based on the information stored in flat File. The data import is do for every 1000/2000 records which would result in to perform SELECT with JOINs on large tables Mul Tiple times. These JOINs would be executed multiple times i.e. the same operation is executing several times. This can is improved by loading the flat file data to a stage table and then perform SELECT with JOINs only once based O n the data exist in stage table. This would significantly improve the performance.

6) indexed/materialized Views:

This technique was very much helpful when there was a search operation on multiple large tables and on various columns. O bviously when you perform search in multiple tables would take lot of time to complete the search operation. This can is improved by creating a indexed/materialized view which would load and consolidate the key columns data in To one or both columns. Here search is performed the one/two columns of large view instead of various columns on multiple large tables. In case of materialized views a index needs to being created on the key columns explicitly to get better performance. 7) Index Optimization:

Over a period of time the data size keep on increasing and at the same time the index size is keep on increasing. The index would become more fragmented and database engine would perform unnecessary data reads. The heavy fragmentation of an index would leads to slow down the performance. There is the options to reduce the fragmentation of the index.

• Rebuild:rebuild would drop the existing index and create a new index with updated data in the columns. It takes more servers resources to perform the rebuild.
• Reorganize:reorganize is more light weight and performs the defragmentation of the index. The existing index is used to update the leaf pages. It is better to does reorganize on periodic basis instead of rebuild.

8) Index Statistics:

The creation of statistics would enable the database engine to use a highly efficient execution plan for a query. Basically index statistics maintains the distribution of the values of an index column i.e. the cardinality of different C Olumn values. This information was used by the database engine to determine execution plan, can be used for processing a query. The statistics would need regular/periodical updates as the distribution of the values changes.

9) Archive Key tables:

As you are already know, the data and index size would keep on increasing day. When the application was functioning since many years then index optimization may not be a good choice to improve the PERFO Rmance as each key table might has billions of records with different indexes on those tables. It is time to archive the key tables and this solution are suitable only if the application are not using the ' very old ' Recor Ds. Create A new archive table for storing the very old records with the same structure of the key table and then move all the Old records to the new archive table.

Better to rebuild/reorganize the indexes on all the key tables once the old records is moved to the newly created archive Table as it would free IoT of memory occupied for storing indexes. This activity can performed periodically (Once/twice on year) during the off hours. Enjoy Faster sql!!

Please remember to evaluate each situation individually to see which method works best. License

This article, along with any associated source code and files, is licensed under the Code Project Open License (Cpol)

SQL Performance Improvement Techniques (GO)