Re: Embedded SQL - performance question

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