Thursday, August 12, 2010

Every developer should know SQL

Sooner or later every developer will have to touch a database. Without proper SQL knowledge, the result will be bad performance and bad security. Using an ORM doesn't change the performance part. Unless you know if the generated queries are good or bad, you risk getting a slow application.

I remember one time when helping out a customer who had some performance issues with their help desk application. When they called me, they had 1 300 help desk cases in the database and the performance was terrible. After doing some tracing I found the application to be doing 13 000 database queries on the page that showed all the help desk cases. That's 10 queries per case. When going through the code, I found a for-each loop with lazy loading inside it. After removing the lazy loading and modifying the SQL to use joins, the number of queries went down to 1 and the performance was great.

So how do we reduce the risk of getting issues like this with our software? My advice here is to:
1) Make sure all the team members have some basic knowledge on SQL and how to use a profiling application such as SQL Profiler.
2) Review any data access code and trace the application on a weekly basis.