× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 30-May-2011 04:15 , Jerry C. Adams wrote:
<<SNIP>> a year or so back I needed to write an order inquiry.
For the most part I used SQL Selects, and it worked reasonably well
(i.e. sub-second response). Part of the inquiry, though, was to
potentially (if the user selected it) display the details for a
billed order in the history. Maybe I was just inefficient, but that
puppy took forever (i.e. > 15 minutes) to return the records I
needed. The history details was around 1,000,000 records at the time
(and growing). I changed it to RLA, and got back the sub-second
response. The index used for the RLA, which was equivalent to the
SELECT, existed before I started writing the program. <<SNIP>>

And like the OP having a couple of seconds versus nearly a minute, for that scenario there was almost surely something /wrong/ either with how the SQL was coded or invoked, or how the SQL was constrained in the ability to implement in a more efficient manner [e.g. required to run with the CQE]. Of course the possibility of a defect in the optimizer or the SQL run-time could also exist, but such ridiculously disparate results IMO are better understood than ignored; then reality sets in, and decisions are made not on ideals, but on practicality :-( FWiW such wildly poor query results with CQE can often become significantly better when changing what is likely the default of OPTIMIZE FOR ALL ROWS to explicitly specify OPTIMIZE FOR 15 ROWS, where 15 is a number suggesting the expected number of rows for a typical detail matching result set; i.e. influencing the decisions by the optimizer, how to implement the query.

But if the problematic SQL was being done on old unsupported releases, there is little point in knowing more, unless the query optimization and implementation rules as origin for such a difficulty would remain unchanged in a newer release where that SQL might still be run ;-)

Regards, Chuck

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.