« Home | Resources to help jump-start your .NET Development » | Agile Programming » | My First Blog Post »

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

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.

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".

Post a Comment