1)Go for views and stored procedures
Rather than writing the heavy-duty queries, we can use stored procedures and views, Stored procedure will be compiled only once, hence it will not create execution plan every time. Also it will reduce network traffic, also we can restrict the user from accessing tables by using views. this will give more security to data.
2)Use Table Variables
At the maximum, go for table variable rather than opting for Temporary variable because table variable requires less locking when comparing to Temporary variable. Logging is also less when compare to Temporary tables.
3)Distinct Clause
This causes additional overhead, this first get all the data related to the condition specified then removes the duplicate rows,. this leads to performance degradation on large set of data. Apply distinct only if it's necessary. if u have duplicate rows, first try to remove the duplicate rows from the table.
4)use constraints
Instead of working with triggers, the best thing is to use constraints at the maximum, this will boost the performance. A proper handling of constraints will restrict unwanted data, entering into the database.
5)Use Union All
In the performance wise observation, Union All is faster, it will not consider the duplicate rows, hence fetches the records more faster. where as Union statement does a distinct over the result set.
6)SQL Server Cursors
It will occupy the memory available for other resources, hence degrades the performance. for fetching a 100 rows, it will execute 100 individual queries.
Use cursor only in the place which is unavoidable. Instead use correlated sub-queries or derived tables in case of performing row-by-row operations.
7)Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used
8)Avoid using Having Clause
The HAVING clause is used to restrict the result set which is returned by the
GROUP BY clause. When one use GROUP BY with the HAVING clause, the GROUP BY clause will divide the rows into the set of grouped rows and aggregates their values, and then the HAVING clause eliminates the unwanted aggregated groups. In many cases, you can write your select statement, so that it will contain only WHERE and GROUP BY clauses without HAVING clause. This will improve the performance of your query.
9)Row Count
If you want to return the total row count, Use the below query to get the row count of a particular table, it is much faster than count(*).
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('table_name') AND (indid < 2) because the count(*) will involve in a full table scan, hence degrades the performance, also it will take more and more time for larger set of data.
10)SET NOCOUNT ON
Do Include SET NOCOUNT ON statement on your stored procedures, this will stop
the message indicating the number of rows affected by a T-SQL statement.
This will reduce network traffic, because your client will not receive
the message indicating the number of rows affected by a T-SQL statement.
11)Use Where Clause
Use where clause, it will restrict the result set and will increase the performance because SQL Server will return to the client only the particular rows instead of all rows from the table. hence it would reduce the network traffic and boost the overall performance of the query.
12)Use Top
Use top keyword if you want to get a particular set of results.
Also you can make use of SET Rowcount. this will improve the performance by reducing the traffic between the client and server.
Select Top 1 Col from Table
This will return only one row as a result.
13)Use Selected Columns
Don't use select *, instead select the fields which you want to retrieve. The server will give the client only the selected columns, this will also reduce the traffic and increase the speed of your query.
14)Index Optimization
Each and every index will increase the time it takes to perform INSERT, UPDATE
and DELETE, So the number of index in a table must be less to have good performance. At the maximum use 4-5 indexes on one table
In case if your table is read only then the index can be more.
Try to put index on the correct column, there is of no use if you apply the index in the Gender column, where there will be only two options, Male or female. Use index in the integer field, than applying the index in characters
Clustered indexes are preferable than non-clustered indexes. Apply the covering index, if your application performs the same query repeatedly
15) stored procedures prefixed with 'sp_'
Any stored procedures prefixed with 'sp_' are first searched for in the Master database rather than the one it is created in. This will cause a delay in the stored procedure being executedAny stored procedures prefixed with 'sp_' are first searched for in the Master database rather than the one it is created in. This will cause a delay in the stored procedure being executed.
16)all stored procedures referred to as dbo.sprocname
When calling a stored procedure you should include the owner name in the call, i.e. use EXEC dbo.spMyStoredProc instead of EXEC spMyStoredProc.Prefixing the stored procedure with the owner when executing it will stop SQL Server from placing a COMPILE lock on the procedure while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
17)Transactions being kept as short as possible
If you are use SQL transactions, try to keep them as short as possible. This will help db performance by reducing the number of locks. Remove anything that doesn't specifically need to be within the transaction like setting variables, select statements etc.
