Search code examples
sqlsap-asesql-execution-plan

different execution time for same query where different values in where clause

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

Solution

  • 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.