Thinking In Sets

When writing SQL queries always remember – “Think in sets”!

Remember that your RDBMS is actually a set processor, so let it do it’s work and avoid ‘procedural’ queries!

For example, when writing queries to derive interrow values it appears “obvious” that you must use sub-queries (ignoring the possibility that your RDBMS product provides the MODEL operator). However, you will quickly find that the execution time goes up rapidly as the number of rows in your database increases!

For example, the following execution times were noted for a sub-query method on a table with 18 columns, properly indexed, using MySQL 5.0:

Rows	Execute time
1000	1.21 seconds
2000	2.72 seconds
3000	6.33 seconds
4000	12.99 seconds
5000	1 minute 38.77 seconds

The query was rewritten to obtain the same result by creating then joining two temporary tables (ie, without sub-queries), with the following results:

Rows	Execute time
76999	4.22 seconds