Index Seek but high cost

TheGunners 2017-09-25 19:31:02

I have index on filename and i got index seek but the cost is high below is my script

SELECT DISTINCT 'mom', c.ClientID
FROM mam.dbo.Client c
INNER JOIN mam.dbo.Vehicle v ON v.ClientID = c.ClientID
LEFT JOIN mam.dbo.EventLog el ON el.VehicleID = v.VehicleID
INNER JOIN holl.dbo.Client c2 ON c2.ClientID = c.ClientID
LEFT JOIN holl.dbo.Vehicle v2 ON v2.ClientID = v.ClientID
WHERE el.[filename] NOT IN (SELECT [FileName] FROM holl.dbo.EventLog WHERE [FileName] IS NOT NULL)
OR c2.ModDT < c.ModDT OR (v2.VehicleID IS NULL OR v2.ModDT < v.ModDT)

Aaron Bertrand 2017-09-25 19:37:08
SQL Server thinks that Index Seek will yield 5 billion rows. Have you considered a filtered index or not using NOT IN (WHERE NOT NULL) or using UNION ALL instead of OR?
TheGunners 2017-09-26 18:13:18
Thanks Aaron I need distinct values how can I do that I tried faltered index it didn't help