0

Dears, I run the same query and it takes different execution times as the following: the query is : 

select * from table1 where userID = 2

it scans the non cluster index idx1 which contains the userID as one of its keys, so I get the results in 5 seconds. BUT when I run it again with userID = 5 it scans the non cluster index idx2 which not contains userID as one of its keys, and get the results after 2 HOURS, I think it maybe read the whole table to find the userID = 5

what is the cause of this problem?? maybe the value of userID = 5 is not in the idx1 leaf?? I think the leaf level in NC index stores a range of values and the query run well for userID =4 and for userID = 6 so userID = 5 should be in the leaf level,

please advice

StanislavL
  • 56,971
  • 9
  • 68
  • 98
scass
  • 35
  • 9

2 Answers2

0

Try to use index hint

For the hot fix

select * from table1 with(index(idx1) where userID = 5

Ref: http://blog.sqlauthority.com/2009/02/08/sql-server-introduction-to-force-index-query-hints-index-hint-part2/

For the long term solution

You might need to update statistic of the table or rebuild the index. You can follow this linke: Script for rebuilding and reindexing the fragmented index?

Community
  • 1
  • 1
Ray Krungkaew
  • 6,652
  • 1
  • 17
  • 28
  • 1
    it is not a good idea to force the usage of an index, if the optimizer is not using it.. there is a reason.. – MtwStark Sep 20 '16 at 08:00
  • Thank you Dears, I don't prefer to use index hint because the mentioned query is part of Report Generation application in my company and we cannot edit the code. – scass Sep 20 '16 at 08:25
0

First update statistics of the indexes of that tables. If it does not work and you're using ASE15 or +, you should update statistics of the column userID. The ASE15 optimizer is more sensitive to statistics than ASE12.

Vince
  • 734
  • 1
  • 5
  • 10
  • Thanks a lot Dears, I tried to update index statistics for this table and all tables related to it in the real environment and the problem was solved. and I tried in test environment to update index statistics just for this table but no improvement , then I update column statistics of userID and the problem solved. – scass Sep 25 '16 at 09:42