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

Nope. It's no faster than doing the query with Deleted on. And as I said in another post to you, this is all in spite of the fact that sys(3054,1) says optimized = full when I run the query.

My 'trick' is still waaayyy faster.

I fully believe Sergey when he says that what my little trick does is the same under the hood as his suggestion, but whatever is going on under the hood, it's still slow using your suggestion or his.