>>>>I think it's a bad idea. If somebody changes where condition of the query or makes query not fully optimizable some other way, you'll get wrong result.
>>>
>>>Fortunately (or not, depending on your pov), the SQL statement is hard coded and not subject to user interference. It is used for a very specific purpose which would become worthless anyway if somebody changed it. It's a real simple statement, but even though there is an index exactly matching the where clause, and an index on DELETED(), when it runs on a table of about 100,000 records, it takes about 10 seconds to run. Doing what I'm doing brings it down to sub 1 second. I'm going to continue trying to find a better option, but I think for now, I may just have to treat this as a temporary fix.
>>
>>Why not explain what you're really trying to accomplish? If you're looking for deleted records using a seek on that deleted tag will get you the deleted records faster than any other process.
>
>I'm just not saying this well at all. The original code was doing a select into an array, and wants only non-deleted records returned. Deleted is set ON. On a table of about 100,000 records, this takes about 10 seconds even though there is an index exactly matching the where clause and an index on DELETED(). I was trying to speed it up, and found that if I set deleted off, select into a cursor, and then turn deleted back on and do a select on the cursor (only a handful of records now) into that array, everything was now down to sub 1 second.
>
>Next to try is to go back to the original code, do the select into a cursor with NOFILTER, and then select from there into the array and see if that speeds it up at all. I have no desire at all to change the code so that the array is not required. I haven't got the time to find all the ramifications of that throughout the system and to try to fix all the problems that might cause.

I'm suggesting you can create the array you're after without all the confusing behaviors of SQL producing a filter. You're getting the equivalent of

SCAN for sql condition
  DIMENSION ARRAY
  store fields to array elements
ENDSCAN