Advice on how to improve query efficiency when Filtering on nvarchar(4000)

[ions]

> -- Recreate the unique index<br>
> CREATE UNIQUE NONCLUSTERED INDEX IX_tblManifestDetailsFWC_NaturalKey<br>
> ON dbo.tblManifestDetailsFWC (ManifestLineID ASC, FederalWasteCode ASC);

What I don't see here is an index on FederalWasteCode, so your lookup "where FederalWasteCode = 'K061') is not using this index. You can prove that by running the query in SSMS (bypassing for a moment any smarts that LINQ might add) and turning on the Execution Plan.

Mind you, an index on ManifestLineID is important for joining with the parent table, but you need an additional index.

Great catch Tom. I missed that index. The query now runs under 3 seconds.

 

[Isaac]

When displaying the execution plan, SSMS usually graciously offers you fresh DDL for suggested indexes, too.