Optimizing...

[bobbo]

I have queries like this in many stored procedures in our database. The entitystatus table has a primary key (on a field not used here) so I want to add another index but based on the query below, should it be on

1) EntityIDCounter, DateEffectiveFrom2, DateEffectiveTo2, IsConfirmed, EntityTypeStatusCounter
or
2) EntityIDCounter, IsConfirmed, EntityTypeStatusCounter
or
3) EntityIDCounter, IsConfirmed with a separate index on EntityTypeStatusCounter

What I'm not sure of is if there is ANY index that will allow optimization on the date fields when we are using IS NULL in the query.

SELECT Entity.EntityCounter, isnull(EntityTypeStatus.StatusDescription,"") as [Status]
from Entity
LEFT OUTER JOIN EntityStatus WITH (NOLOCK) ON Entity.EntityCounter = EntityStatus.EntityIDCounter
AND ((EntityStatus.DateEffectiveFrom2 IS NULL) OR (EntityStatus.DateEffectiveFrom2 <= @DateTo))
AND ((EntityStatus.DateEffectiveTo2 IS NULL) OR (EntityStatus.DateEffectiveTo2 > @DateTo)) AND EntityStatus.IsConfirmed = 1
LEFT OUTER JOIN EntityTypeStatus WITH (NOLOCK) ON EntityStatus.EntityTypeStatusCounter = EntityTypeStatus.EntityTypeStatusCounter
WHERE Entity.EntityCounter = @EntityID

[skhanal]

It is hard to tell without knowing about the data distribution.

How large is the table?. You can start with the first one you listed and see if it helps, then move on.