Need help with Optimizing query

Transact-SQL
1

This runs very slow. I think it might be because I should use Except instead of the way I'm doing it but not sure how to do that or if I can:

Select Name,Username from SystemUsernames
where
Username not in (Select Username from Person where username is not null) and
RemoveFromList = 0

Thanks for any help.

2

Try this. If that does not make any difference, if you don't already have an index on UserName in SystemSuernames and in Person tables, adding one would help.

SELECT
	Name,
	UserName
FROM
	SystemUsernames s
WHERE 
	NOT EXISTS
    (
		SELECT *
		FROM
			Person p
		WHERE
			p.UserName = s.Username
	)
	AND s.RemoveFromList = 0;
3

That code looks OK overall. An index on Username in the Person table should help, but I wouldn't expect it to be a big difference unless the Person table is very wide.

Select Name,Username
from dbo.SystemUsernames
where
Username not in (Select Username from dbo.Person where username is not null) and
RemoveFromList = 0

1 Like
5

This made a huge difference. Thank you.

6 
Select Name,Username from SystemUsernames u
left join Person p
on u.username = p.username
where p.username is null and RemoveFromList = 0