0

Here is my query

Declare @StartDateTime datetime, @EndDateTime datetime

Select @StartDateTime = '2010-11-15', @EndDateTime = '2010-11-16'

Select PracticeCode, AccountNo, ProcCd, Modifier , ChargeDos, Paid as Amt, CreatedDate,
     case When Paid > 0 then 'P' 
          When Paid = 0 and WrittenOff = DueAmt then 'A'
          Else 'O' 
     End as Status
     From Trn_Postings
     Where CreatedDate >= @StartDateTime and CreatedDate <= @EndDateTime
            --and ManualOverride in ('S','F','X','G','O')
            and ManualOverride in ('N','U')

Edit : Created date is a datetime column which contains the date & time of the record created

I have individual indexes on both CreatedDate and ManualOverride. But the execution plan shows clustered index scan. The table has nearly a million record and can grow 4 to 5 times in near future.

The most surprising part is if I change the where clause like below, it uses both the indexes. I just dont know why.

     Where CreatedDate >= @StartDateTime and CreatedDate <= @EndDateTime
            and ManualOverride in ('S','F','X','G','O')
            --and ManualOverride in ('N','U')

How do I make Sql to use the indexes...

Further if I use a Not in clause wont the index be used.

The King
  • 4,600
  • 3
  • 39
  • 58
  • As it stands, the optimizer *does consider* the index. It *could be wrong* in not using the index for your first case but more than likely, it's not. Can you post the plan for both cases? – Lieven Keersmaekers Nov 16 '10 at 07:49
  • 1
    Using an index does not always gives you a better performance. A simple case where forcing the compiler to use an index is going to be slower is by doing a select that returns **all** rows. – Lieven Keersmaekers Nov 16 '10 at 07:54
  • `select ManualOverride,COUNT(*) from Trn_Postings group by ManualOverride`. If N and U represent the majority of values in this column, it means the index isn't going to be that useful for the query, so the optimizer is unlikely to use it. – Damien_The_Unbeliever Nov 16 '10 at 08:02
  • Maybe decomposition into 5 UNIONs would yield better results. It worked for me in some situations. See the first case of Quassnoi's answer in: http://stackoverflow.com/questions/1069346/general-rules-for-simplifying-sql-statements – MicSim Nov 16 '10 at 08:10
  • @Damien. Of course N and U constitutes major portion, but what stops it from using index on "CreatedDate" when the data is spread over 5 months but I'm just asking for a single day. – The King Nov 16 '10 at 08:44

3 Answers3

0

Given that for every row found that matches the where clause it will have to go back to the clustered index to get the data, if there is lots of data being returned, it may be more efficient to just step through it all and pull out the rows it needs.

cjk
  • 45,739
  • 9
  • 81
  • 112
0

Consider updating statistics:

update statistics Trn_Postings with fullscan
Arvo
  • 10,349
  • 1
  • 31
  • 34
0

I'd suggest creating a composite index over CreatedDate and ManualOverride. When both conditions can be satisfied using indexes, that's what the optimizer appears to be doing.

But once it knows that it has to use the clustered index to satisfy part of the query, it would appear that it's ignoring other indexes. It has decided that COST(Use CreatedDate index + CI lookup + condition) > COST(Scan CI). It's wrong, but sometimes this happens. An index over both columns (CreatedDate first) would probably be used. Or you can try to force it's hand by using a INDEX query hint. It may still choose to ignore the hint.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Looks like I have to live with it :-(... The "Using Index" Table hint is only available in Sql Server 2008... We still use Sql server 2005... – The King Nov 16 '10 at 09:40
  • @The King - under 2005, you can still provide an INDEX hint - using the OPTION clause, and specifying a table hint. http://msdn.microsoft.com/en-us/library/ms187373(v=SQL.90).aspx – Damien_The_Unbeliever Nov 16 '10 at 10:37