Better performing SQL for finding mismatches

[andersonjb]

I'm looking for a faster way of finding all records in one table that are not in the other. This code using a subquery is proving to be very slow-

SELECT id, login
FROM dps_user
WHERE ltrim(rtrim(dps_user.login)) NOT IN
(SELECT ltrim(rtrim(login)) FROM tempdb..all_ldap_users)

dps_user contains 85k + rows, all_ldap_users contains 82k + rows.

 

[fluteplr]

The problem is not the subselect, it is the trims which are preventing the server from using any indexes.

Have you considered just trimming the data once in each table and then you can just use:

SELECT id, login
FROM dps_user
WHERE dps_user.login NOT IN
(SELECT login FROM tempdb..all_ldap_users)



Also I assume you are index on the login field in all_ldap_users.