>>>Do you have an index on ALLTRIM(BATCHNO)? I think we should avoid ALLTRIM in index expressions like plague.
>>
>>Yes, Not duplicating the index statement exactly in the SQL clause causes greatly reduced performance. i.e. using just BATCHNO when the index is ALLTRIM(BATCHNO) will not optimize.
>
>You should not use ALLTRIM() in indexes. Far better to INDEX ON PADR(ALLTRIM(BATCHNO),10) and then to SELECT WHERE PADR(ALLTRIM(BATCHNO),10) = "batchno "
>
>>
>>Also getting the SET EXACT and SET ANSI settings correct also impacts greatly.
>>
>>Rushmore is a great piece of technology, once you understand it.

Why batchNo alone would not work? It is because it could be:

     7
  22
123  
   56

etc.?