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

>
>I guess I can try it. You really think that'll be faster (or as fast)? As I say, I'll try anything, but I have to admit, I'm going into it a bit skeptically.

It will be every bit as fast as your current sneaky tricky SQL, but way more obvious and less prone to adding/removing an index breaking the SQL.