Recently at my workplace I was asked to provide a briefing on Dos and Donts while writing stored procedures. I have just posted a modified version of it.
> Avoiding sub queries/Converting sub queries to Correlated sub queries
I have discussed the same at length in my previous post which can be found here.
> Replace cursors using while loops
Cursors involve row by row processing which can slowdown the execution. Cursor can effectively replaced by while loop and a temporary table to provide better performance. This can also help one avoid errors that occur due to locking.
Example for the same can be found here
> Usage of order by/ distinct
Order by/distinct clause internally results in a sort operation which can increase the execution time. So, Order by/Distinct should be used minimally and only when it is inevitable.
> Effective usage of temp table / table variable.
Long queries when broken down by storing the temporary results to temporary tables/ table variables provide considerable performance boost. Usage of table variables / temporary tables allows one to reuse the previously obtained results and also reduce the duration of locks held on objects. But, Usage of temp table/ temp variable does cause a load on tempdb and system memory.Getting into the details of choice of selection for table variable/temp table is beyond the scope of this post. Please note that if one has a long transaction/query temp table and table variables are good options to look at.
> Avoiding select *
Column names should be specified instead of specifying ‘*’. This ensures unwanted data is not selected. Using ‘*’ can cause syntax errors when the source or destination table structure is modified.
> Avoid non - Sargable conditions
Using non Sargable conditions on ‘where’ clause causes table scan and hence the index created on the column does not get used. Non Sargable conditions are a list of conditions on usage puts the index in vain. They are 'OR', 'not in', <>, column as a parameter of function., eg: dateadd(dd,1,date_of_birth).
> Following ANSI style join
Outer Joins on tables should follow the ANSI Style join as Microsoft supports only the ANSI style joins from SQL Server 2005.
> SET NOCOUNT ON
SET NOCOUNT ON should be added at the starting of all stored procedures as it reduces the additional overhead of calculating row count on the compiler.
> Transaction handling
Transactions should be kept as short as possible as it increases duration of locks held on the objects. Transaction opened using begin transaction should always commits or rollbacks on all paths of the execution flow.
Nested transactions should always be avoided.
> Error Handling
Error handling should be been done at appropriate places using @@Error variable.
The above listed points were obtained from the most common mistakes committed by the developers.There are many many books which speak about effective T SQL Programming. The above list is just obvious ones that I have come across.
No comments:
Post a Comment