- Call stored procedure using its fully qualified name.
- The complete name of an object consists of four identifiers: the server name ( Optional Only for linked server ), 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 procedure's execution plans if they were executed using fully qualified names.
- Use IF EXISTS (SELECT 1) instead of (SELECT *):
- To check the existence of a record in another table, we uses 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.
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
- The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements.
- Use TRY-Catch for error handling
- Optimize table access with NOLOCK
- Avoid implicit conversation
- implicit conversions prevent an index seek
- Always be alert for implicit conversions, particularly when there are character strings storing numeric keys.
- This problem when varchar columns are compared to nvarchar columns.
- Fixing the problem is straight forward, just make sure you are performing the comparison on like data types.
- Avoid nchar and nvarchar
- only use nchar and nvarchar if there is a special characters we are storing.
- Avoid * in SELECT statement
- Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.
- Avoid Cursors
- Keep Transaction small
- Practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks.
- Create Clustered and Non-Clustered Indexes
- Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.
- Use table variables or temporary tables based on the use.
- The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
- Though Table variable was updated within the transaction, it is not a part of the transaction itself. If Temp variable was updated within the transaction,it's the part of the transaction.
- The data set is fairly large a temporary table is the best option.
- To help identify performance problems with Stored Procedures, use the SQL Server’s Profiler Create Trace Wizard to run the “Profile the Performance of a Stored Procedure” trace to provide you with the data you need to identify poorly performing stored procedures.
- Check Query Execution plan
Pages
▼
No comments:
Post a Comment