SQL OPTIMIZATION TIPS AND TRICK

Here are some Sql optimization techniques (best practices) to write optimized query in sql server, which are listed below:-

• Don't use "SELECT*" in a SQL query: - Unnecessary columns may get fetched that will add expense to the data retrieval time. So select only columns that you used. Selecting unnecessary columns in a Select query adds overhead to the actual query and thus increases the execution time.

               SELECT * FROM TABLE instead use SELECT COL1, COL2….FROM TABLE

• Avoid unnecessary tables in join conditions: - Including unnecessary tables in join conditions forces the database engine to retrieve and fetch unnecessary data and increases the query execution time.

• Try to avoid joining between two types of columns:-When joining between two columns of different data types, one of the columns must be converted to the type of the other. The column whose type is lower is the one that is converted. If you are joining tables with incompatible types, one of them can use an index, but the query optimizer cannot choose an index on the column that it converts. For example joining between to data type INT and Float as integer column converted to float and it does not use index.

• Try not to use COUNT(*) to obtain the record count in a table:- To count total number of records in a table or dynamic query use COUNT(Column)  instead COUNT(*)  specially those column which have index or primary key which has default clustered index in it. COUNT (*) uses full table scan and does not get benefit of indexes. If no any condition is needed to check in your where clause you can use:-

               SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tblName') AND indid < 2

• Include SET NOCOUNT ON statement:-With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored Procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is generally reduced.

• Try to avoid dynamic SQL: - Unless really required, try to avoid the use of dynamic SQL because: Dynamic SQL is hard to debug and troubleshoot. If the user provides the input to the dynamic SQL, then there is possibility of SQL injection attacks.

• Minimize use of Temporary Table (#table), Table Variable (@table),Subquery,CTE:-

             There are mainly four ways by which we can hold intermediate data temporarily.

• TEMPORARY TABLE: It is also known as # table, it is resided in tempdb system database. It uses          indexes clustered and nonclustered both. You can create temporary table as

CREATE TABLE #TEMP (COL1 DATATYPE1, COL2 DATATYPE2………..)

Use this table when we need to hold large rows data, it is faster because index can be apply on it.

• TABLE VARIABLE: It starts with @ declare as variable as

DECLARE @TABLE TABLE (COL1 DATATYPE1, COL2 DATATYPE2….)

Use it when we need to hold small data. It reside in memory but don, t uses indexes.Clustured indexes can be created as it support column with primary key.

• SUBQUERY: It just hold complex query and can be nested syntax is as follows. SELECT * FROM (SELECT COL1, COL2, -----FROM TABLE) AS X WHERE some condition. It is faster than above two methods as it does not need data to insert.

• COMMON TABLE EXPRESSION: Commonly known as CTE, as it hold complex query and it can be nested as subquery.The syntax is

WITH CTE_Name (COL1, COL2) AS (SELECT COL1, COL2 FROM TABLE)

SELECT * FROM CTE_NAME

Where, CTE_NAME is name of CTE.

• USE SCHEMA NAME WITH OBJECT NAME: - Always use object name with their schema name. Database objects are table, stored procedure, function, trigger and views etc. If database schema name is dbo then use dbo. Object instead of object. SELECT * FROM [DBO].[TABLE] INSTEAD SELECT * FROM [TABLE]. If we not specified schema name it searches on all schemas which slow down query. Always use [dbo].[sp_name or fn_name] when calling from C# code.

• ·DON, T USE STORED PROCEDURE WITH SP_:- As sp_ stored procedure naming convention used by system stored procedure. So don’t use this convention instead use your own. Such stored procedure first search on master database and then current session database cause extra overhead and increase execution time.

• ·TO CHECK EXISTENCE OF RECORD: - To check existence of record, use SELECT 1 FROM TABLE or SLECT COL1 FROM TABLE instead of SELECT * FROM TABLE.

• · EXECUTE DYANAMIC QUERY: - To execute dynamic query we can use whether sp_executesql stored procedure or EXECUTE statement. First sp_executesql support parameter and can be reused but second one cannot be and need to hold on certain table to display result.

• WHEN EXTRACTING DATA: Try to avoid looping while you have to select data and need to display accordingly. If we have lots of record on table, and we loop through table data for some condition to check, it slows down query, Example WHILE.

• · DON,T USE SCALAR FUNCTION IN SELECT STATEMENT:- If possible do not use scalar function in select statement as it have to loop through each rows and process that function. It slow down query performance. Instead use it to set value on single variable. Write inline query in select if it is simple, if not there is no any option alternative to it.

• TABLE COLUMN SIZE:- Table column size be as small as possible, if we have to hold only 3 character  example ‘abc’ always its size be less if possible nvarchar(10)  not nvarchar(256).

• SET STATISTICS TIME ON: - To check how much times consumed by your stored procedure, include it in top and analyze its time.

• USE INDEXES: - Indexes increases the query performance. Some type of indexes are

• Clustered Index
• Non Clustered Index
• Composite Index
• Covered Index
• Use query execution plan to find missing index suggestion or database tuning advisor or use your brain.
• There are Estimate execution plan and actual execution plan.
• EXEC [dbo.].[usp_GetName] 1, 2 ,’en-US’  select this and write click from context menu select Display estimated execution plan or go to Menu at top select query and estimated plan and see what happen with your query and which section of your query is costly.