Index Seek but high cost

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)

tmp578B.pesession (16.6 kB)
avatar image By TheGunners 1 asked Sep 25 at 07:31 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Sep 25 at 07:37 PM

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?

10|10000 characters needed characters left

1 answer: sort voted first

Thanks Aaron I need distinct values how can I do that I tried faltered index it didn't help

avatar image By TheGunners 1 answered Sep 26 at 06:13 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x445
x109

asked: Sep 25 at 07:31 PM

Seen: 42 times

Last Updated: Sep 26 at 06:13 PM