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

Yes