I haven't fully read the article but I plan to at a later time. 2 things.

1. I've used what you call partial optimization and what I have found is that if the first SQL pulls down a substantial number of records 50,000, since you will be forced to use a NOFILTER clause it will have to write this out to disk. Because of the extra disk access, it seems to be faster in these cases to do it in one SQL as opposed to two. In the example you show that one SQL actually brings back 100 records. In this case the partial optimization would work.

2. I've just recently heard that when building a SQL you want to place the most restrictive clause first and so on with the last being the least restrictive. I'm curious to see how the results of the partial optimization work if you do it in one SQL and change the order in the where clause.

If you talk about #2 in your FAQ, I apologize I did not fully read the article.

>Please check in FAQ - I recently posted some suggestions about query optimization. Specifically, I think you might want to:
>
>a) Try a partial optimization instead of a full optimization (sounds weird, I know, but if you read my text, it will start to make sense),
>
>b) Divide one large query into several smaller ones.
>
>HTH, Hilmar.
>
>>We have a web app which needs to support up to 100 plus users. We have multiple web sites that all use basically the same code. One of the websites has a table with 2.5 million records about 350mb. There is one summary report which if pre-digested will run very quickly on the web.
>>
>>Unfortunately there are 11 fields they want to be able to filter on. We have tried to get them to reduce the number of filterable fields but they won't budge on this. The report takes somewhere between 60 and 90 seconds to run. If we can limit them to 2 or 3 fields, I can pre-digest it further and then the speed would be reasonable.
>>
>>The machine is a dual 550 and currently running west-wind 3.15.
>>
>>Is there a solution to this problem?
>>
>>TIA