Use SET NOCOUNT ON
This statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE.
Ex.
It will remove this extra overhead from the network.
Use schema name before objects.
It helps SQL Server to find the object.
Ex. SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee
If you want to change the schema, you can change.
- IF(NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'emp'))
- BEGIN
- EXEC('CREATE SCHEMA [emp] AUTHORIZATION [dbo]')
- END
- ALTER SCHEMA emp
- TRANSFER dbo.Employees
It will improve the performance of the stored procedure
Use EXISTS () instead of COUNT ()
This SQL optimization technique concerns the use of EXISTS (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. This will give you better performance.
Ex. SELECT Count(1) FROM dbo.Employee
Ex. IF ( EXISTS (SELECT 1 FROM db.Employees))
BEGIN
END
Don’t use functions in the WHERE clause
While writing select query, if you use the function in where condition, it will reduce the performance of your query. Try to avoid the function in where clause.
Use NO LOCK
Use NOLOCK will improve the performance of the select query
- SELECT EmpID,EmpName,EmpSalary
- FROM dbo.Employee WITH(NOLOCK)
- WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
Specify column names instead of using * in SELECT statement
Try to avoid *
- SELECT * FROM dbo.Employee WITH(NOLOCK)
- WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
Best practice is to specify the column name.
- SELECT EmpID,EmpName,EmpSalary
- FROM dbo.Employee WITH(NOLOCK)
- WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
Avoid temp temporary table
Temporary tables usually increase a query’s complexity. It’s suggested to avoid the temporary tables.
Create Proper Index
Proper indexing will improve the speed of the operations in the database.
Use Join query instead of sub-query and co-related subquery
Using JOIN is better for the performance than using subqueries or nested queries
Also, use minimum JOINS (as required) and check whether proper JOIN is used or not.