SQL Query Performance Tip

When writing SQL queries to identify data that is "Missing" use a left join where a value is null instead of using NOT IN. This will improve performance by using fewer reads and return results faster.

In this example, the query was written to find the clients who do not have an address entered. It assumes that all clientkeys have values and that none are null:

SELECT FD__CLIENTS.CLIENTKEY, FD__CLIENTS.FULLNAME FROM FD__CLIENTS WHERE CLIENTKEY NOT IN (SELECT CLIENTKEY FROM FD__CLIENT_ADDRESS)

For better performance, rewrite the query using a left join:

SELECT FD__CLIENTS.CLIENTKEY, FD__CLIENTS.FULLNAMEFROM FD__CLIENTSLEFT JOIN FD__CLIENT_ADDRESS ONFD__CLIENTS.CLIENTKEY = FD__CLIENT_ADDRESS.CLIENTKEYWHERE FD__CLIENT_ADDRESS.CLIENTKEY IS NULL

Both of these queries return the same results, but if you run each of these with SQL Profiler turned on, you will notice that the second query returns rows faster and does fewer reads. The same thing works using “in” as well.

SELECT STAFFKEY, FULLNAME FROM FD__STAFF WHERE STAFFKEY IN (SELECT STAFFKEY FROM FD__CREDENTIALS)

This example uses an inner join to get all staff that has credentials entered:

SELECT FD__STAFF.STAFFKEY, FD__STAFF.FULLNAME FROM FD__STAFF INNER JOIN FD__CREDENTIALS ON FD__STAFF.STAFFKEY = FD__CREDENTIALS.STAFFKEY