The following SQL query times out for some reason. It doesnt look too complex but perhaps the way it is structured means it cannot be used successfully.
Basically the query looks for EmailAddresses from one table, ensures they're not in another table and also that theyre not in the result of a subquery.
Doesnt sound too bad, but for some reason it times out. I know the constituent parts of the query (such as the sub query) work OK.
The COALESCE is in there because the EmailAddress fields may contain null values. The collate statement is there because the databases for some reason use a different collation setting.
SELECT TempBroadcast.EmailAddress, TempBroadcast.FirstName, TempBroadcast.LastName, TempBroadcast.FirstName + ' ' + TempBroadcast.LastName AS FullName
FROM TempBroadcast LEFT OUTER JOIN
EmailOptOut ON TempBroadcast.EmailAddress = EmailOptOut.EmailAddress collate SQL_Latin1_General_CP1_CI_AS
WHERE (EmailOptOut.EmailAddress IS NULL) AND TempBroadcast.EmailAddress NOT IN (
SELECT COALESCE(XXX.dbo.Quotes.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress
SELECT COALESCE(XXX.dbo.EssentialClicks.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress
SELECT COALESCE(XXX.dbo.Customers.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress