Best Practices
My job as a .NET consultant takes me to a lot of different companies and sometimes I'm asked to fix an existing performance problem. 99% of the time, the performance problem is related to a poorly written stored procedure, trigger or sql statement.
I'm amazed how often I see stored procedures and triggers that are > 1000 lines, which in and of itself is not the worst practice, but definitely a flag that maybe there's too much business logic in the stored procedure/trigger.
The other thing I see quite often are excessive use of cursors and temporary tables. I can understand this trend because it's really easy to create and use cursors and temporary tables in SQL Server.
Whenever I have to fix a problem at a client and I find it's related to poorly written SQL Server stored procs/triggers, I make it a priority to educate the developers who wrote the original code and show them alternative options.
For example, most cursors can be re-written with a while loop and temporary tables can (most of the time) be replaced with Table variables instead. Sometimes, its just a matter of re-writing the SQL statements to use a sub-select.
I don't understand why more developers don't know these basic best practices for SQL Server:
Bottom Line:
Avoid cursors when you can: they cause locking and server resources are consumed
Avoid temporary tables: they cause more locking and more stored procedure recompiles
I'm amazed how often I see stored procedures and triggers that are > 1000 lines, which in and of itself is not the worst practice, but definitely a flag that maybe there's too much business logic in the stored procedure/trigger.
The other thing I see quite often are excessive use of cursors and temporary tables. I can understand this trend because it's really easy to create and use cursors and temporary tables in SQL Server.
Whenever I have to fix a problem at a client and I find it's related to poorly written SQL Server stored procs/triggers, I make it a priority to educate the developers who wrote the original code and show them alternative options.
For example, most cursors can be re-written with a while loop and temporary tables can (most of the time) be replaced with Table variables instead. Sometimes, its just a matter of re-writing the SQL statements to use a sub-select.
I don't understand why more developers don't know these basic best practices for SQL Server:
Bottom Line:
Avoid cursors when you can: they cause locking and server resources are consumed
Avoid temporary tables: they cause more locking and more stored procedure recompiles
Another method to avoid cursors is using Sql functions. I really like using them because you can optimize the query you would normally perform as a stored proc and use it like a subselect. With everything, you have to be careful of overuse.
A good example would be an order basket for an ecommerce site. I have, in the past, used sql functions to subselect to obtain an item/quantity subtotal or a pretax total for the basket. It's a lot less code to handle than updating a temp table with values for an order basket.
Posted by
Suman Chakrabarti |
11:45 AM
You're right! sql functions are another method, but you pay a performance penalty when you use them, so like you've already mentioned "you have to be careful of overuse".
Posted by
Jorge Matos |
11:51 AM