Subject Re: [IBO] HDR w/o incremental search??
Author Helen Borrie
At 02:03 AM 14-10-02 -0700, you wrote:
>Is the HDR only applicable to incremental searches?
>If I have a query with multiple Left Outer Joins and the resulting Order
>by uses fields from the joined tables, is there a way that HDR can help
>improve the speed to get the first page of data for my grid?
>Right now it is taking about 75 seconds to get a response in the
>InfoPower grid after pulling together about 90K records in a query that
>runs a sort on the server with all the resulting 90K rows before
>anything is returned to the client.
>As I understand it this is correct behavior and part of the performance
>hit of doing an Order By on such a large result set. The joins are all
>using indexes already.
>I know that 90K records are a lot to bring into a grid and it is not my
>design. The client is insistant about having to load all this into the
>I fisrt moved a Where clause up into the Joins and that improved the
>response from 5 minutes to 75 seconds, but it would be nice to get an
>even faster response.

Your real problem here appears to be your customer's requirement to open
the application with an ordered set of 90,000 records. What is the
rationale behind this, when

select <all 90,000 records> takes a minute and a quarter, whereas

select <a much smaller set>
where <parameterised very steep criteria>
will be sub-second?

IOW, what you need to convince him is that, as long as your app provides
selectors for keys, he can retrieve any record he wants, faster than a
speeding bullet...well, at least quantumly faster than waiting for the
whole set and then scrolling through 90,000 records...

You have my sympathy, if that helps at all...