This article describes the following:
- How to reduce long query for good performance (Query Optimization)
- How to reduce Stored Procedure for good performance (Stored Procedure Optimization)
- How to reduce use of temporary table for good performance (Temporary Tables Optimization)
How to reduce a long query for good performance (Query Optimization)
- Don't select all the columns, select only required columns.
Example- select * from tablename (Incorrect)
Select field1,field2 from tablename
- Try to use a UNION ALL statement instead of UNION, whenever possible.
- Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.
We should Use EXISTS instead of DISTINCT to improve Query Performance
Example:
SELECT distinct D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM DEPARTMENT D INNER JOIN EMPLOYEE E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
I modified the query with the EXISTS clause.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM DEPARTMENT D WHERE EXISTS (SELECT 1 FROM EMPLOYEE E WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
- Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub-query or derived tables, if you need to perform row-by-row operations.
SQL Writing Convention
It is a good to use a standard syntax for writing SQL queries.
I recommend following standards when writing SQL queries.
Write all standard SQL TEXT in upper case.
For example:
SELECT ename, sal
FROM emp
WHERE deptno = 20;
Write all non standard SQL TEXT (Table name, Column name and so on) in lower case:
For example:
SELECT ename, sal
FROM emp
WHERE deptno = 20;
- Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so that it will contain only WHERE and GROUP BY clauses without a HAVING clause. This can improve the performance of your query.
- Try to restrict the query result set by using the WHERE clause.
This can result in good performance benefits, because SQL Server will return to the client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.
- If you need to return the total table's row count then you can use an alternative way instead of the SELECT COUNT(*) statement.
Because the SELECT COUNT(*) statement does a full table scan to return the total table's row count, it can take a long time for the large table. There is another way to determine the total row count in a table. You can use the sysindexes system table, in this case. There is a 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 instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <>
Use table alias: Always use a table alias and prefix all column names with the aliases when you are using more than one table.
- Avoid using arithmetic operators or functions on WHERE clause columns.
Using an arithmetic operator or functions on a WHERE clause column prevents the optimizer from choosing the index on the column. Also, try to avoid using exclusion conditions (example! =, NOT EXISTS, NOT IN, OR) in the WHERE clause. Exclusion conditions can force the optimizer to ignore indexes on the columns in the WHERE clause.
- Avoid implicit data type conversions in the query.
Implicit conversions can prevent the indexes from being used by the optimizer and will also add overhead by costing extra CPU cycles for data type conversions.
- Avoid any operations on the fields, where possible.
Some operations will prevent the use of the index on the field even if it exists. For example, instead of using the condition cast(DateField as varchar(20)) = @dateString, try to convert @dateString to an expression of datetime type first, and then compare it to the DateField.
- Views are a special type of query.
They are not tables. They are logical and not physical so every time you run select * from MyView, you run the query that makes the view and your query on the view.
If you always need the same information then views can be good.
If you need to filter the view then it's like running a query on a query; it's slower.
Example (views):
Create View v_Employees AS
Select * From Employees
Select * From v_Employees
This is just like running Select * From Employees twice.
You should not use the view in that case.
How to reduce Stored Procedure for good performance
(Stored Procedure Optimization)
We will go over how to optimize a Stored Procedure by making simple changes in the code. Please undestand that there are many more tips.
- 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 most useful in debugging the code, but it is 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 greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO.
- Use schema name with object name
The object name is qualified if used with schema name. Schema name should be used with the Stored Procedure name and with all objects referenced inside the Stored Procedure. This helps in directly finding the complied plan instead of searching the objects in other possible schemas before finally deciding to use a cached plan, if available. This process of searching and choosing a schema for an object leads to COMPILE lock on Stored Procedure and decreases the Stored Procedure's performance. Therefore, always refer to objects with a qualified name in the Stored Procedure as in the following:
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the Stored Procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method
- Do not use the prefix "sp_" in the Stored Procedure name
If a Stored Procedure name begins with "SP_," then SQL Server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another Stored Procedure with the same name is found in the master database.
- Try to avoid using Data Definition Language (DDL) statements inside your Stored Procedure
Using DDL statements inside a Stored Procedure reduces the chance to reuse the execution plan.
- Use IF EXISTS (SELECT 1) instead of (SELECT *)
To check the existence of a record in another table, we use the IF EXISTS clause. The IF EXISTS clause returns true if any value is returned from an internal statement, either a single value "1" or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use "1" in the SELECT clause of an internal statement, as in the following:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'MyTable' AND type = 'U')
- Try to avoid using SQL Server cursors whenever possible
Cursor use many resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with a SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation veryquickly. Again, please note that a cursor is also a kind of WHILE Loop.
- Use TRY-Catch for error handling
Prior to SQL Server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every T-SQL statement. More code always consumes more resources and time. In SQL Server 2005 & 2008, a new simple way is introduced for the same purpose.
The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH
- If you have a very large Stored Procedure, try to break up the Stored Procedure into several sub-procedures, and call them from a controlling Stored Procedure.
The Stored Procedure will be recompiled when any structural changes were made to a table or view referenced by the Stored Procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a Stored Procedure. So, if you break down a very large Stored Procedure into several sub-procedures then you get a chance that only a single sub-procedure will be recompiled, but other sub-procedures will not be.
- Keep the Transaction as short as possible
The length of a transaction affects blocking and deadlocking. An exclusive lock is not released until the end of the transaction. At a higher isolation level, the shared locks are also aged with the transaction. Therefore, a lengthy transaction results in locks for longer times and locks for longer times turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
- Use Stored Procedures instead of heavy-duty queries
This can reduce network traffic, because your client will send to the server only the Stored Procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored Procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the Stored Procedure to work with the restricted set of the columns and data.
- Call Stored Procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which Stored Procedure you want to run and can boost performance because SQL Server has a better chance to reuse the Stored Procedures execution plans if they were executed using fully qualified names.
How to reduce use of temporary table for good performance
(Temporary Tables Optimization)
- Consider using the table-valued parameters instead of creating a temporary table
SQL Server 2008 introduces a new user-defined table type that supports representing table structures for use as parameters in a Stored Procedure or user-defined function. So, table-valued parameters can send multiple rows of data to a SQL Server 2008 statement or Stored Procedure/function without creating a temporary table.
Example
USE AdventureWorks2008R2;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50) ,
CostRate INT ); GO
/* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE() FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a Stored Procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
Limitations to passing table parameters
You must use the READONLY clause when passing in the table valued variable into the procedure. Data in the table variable cannot be modified; you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters, you can only use table variables as input parameters.
- Try to avoid using temporary tables inside your Stored Procedure
Using temporary tables inside Stored Procedure reduces the chance to reuse the execution plan.
- Use Table data type instead of temporary tables whenever possible.
A Table data type is a special data type used for temporary storage of a set of rows. In comparison with the temporary tables, table variables have some benefits.
For example, table variables are cleaned up automatically at the end of the Stored Procedure or batch in which they are defined and table variables require less locking and logging resources in comparison with the temporary tables.
- Avoid creating temporary tables from within a transaction.
Creation of temporary tables inside a transaction requires many locking and logging resources, can lock some system tables and can decrease the overall SQL Server 2008 performance.
- Use the sp_executesql Stored Procedure instead of temporary Stored Procedures
Microsoft recommends using temporary Stored Procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 2008 should use the sp_executesql system Stored Procedure instead of temporary Stored Procedures to have a better chance to reuse the execution plans.
- Consider creating indexes on the very large temporary tables
The temporary tables provide a much more efficient way to access their data in comparison with general tables, but if you work with very large temporary tables, using indexes can greatly improve performance.
- Consider creating a permanent table instead of using temporary tables
If your queries contain a loop and use temporary tables requently then consider creating a permanent table to store the intermediate results. In this case, you will save some time because you will not create the table and drop the table many times.
- Use local temporary tables instead of SQL Server 2008 cursors
Because using temporary tables usually are more efficient than using cursors, you should use temporary tables instead of cursors whenever possible.
- Consider using a Common Table Expression (CTE) to simplify query logic by replacing the use of temporary tables
CTEs were first introduced in SQL Server 2005. A CTE is a result set that is stored temporarily during the execution of a SELECT, INSERT, UPDATE, DELETE statement. By using Common Table Expressions, you can simplify query logic by replacing the use of temporary tables.
Examples of CTEs
1.
With CTEselect as
(
SELECT distinct D.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM DEPARTMENT D INNER JOIN EMPLOYEE E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
)
select CTEselect.DEPARTMENT_ID,CTEselect.DEPARTMENT_NAME from CTEselect
2.
WITH CTEforInsert (Empid,Empname)
AS
(
select eid,ename from EMPLOYEE
)
select Empid,Empname from CTEforInsert
Paging with ROW_NUMBER() Using CTE
In MSSQL 2000 we would do paging either by dynamic SQL or by some advanced techniques.
In MSSQL 2005 and 2008, with the introduction of the ROW_NUMBER function, Paging is much easier.
Example
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 1;
SET @PageSize = 10;
WITH CTEforPaging AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Ename) AS RowNum
,Ename
FROM dbo.EMPLOYEE
)
SELECT * FROM CTEforPaging
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY cteforpaging.RowNum;
- Avoid using global temporary tables
SQL Server supports local and global temporary tables. Both of these table types are stored in the tempdb system databases. The local temporary tables are visible only in the current session and their names are prefixed with a single number sign (#table_name), while the global temporary tables are visible to all sessions and their names are prefixed with a double number sign (##table_name).
Because the local temporary tables are dropped automatically at the end of the current session, while the global temporary tables are dropped automatically only when all tasks have stopped referencing them, you should avoid using global
temporary tables.
Because all temporary tables are stored in the tempdb database, consider spending some time on the tempdb database optimization.