This seems somewhat counter-intuitive to me

We have a Transaction table that contains 1 record per person per week,
and holds a 5 year history

We have a process that needs to take values from the last 16 qualifying
weeks (not every week is a qualifying week).

The (embedded SQL) process uses a cursor to select the individuals in
turn, and for each record returned from the cursor, uses a select loop
to select the transaction records in descending date order, adding the
values for Qualifying Weeks until 16 weeks have been processed - In
most cases it will find the 16 Qualifying Weeks in the first 16-25
records - there are no endselects in the process, so the selectloop
returns all 250+ rows to the program for each person.

For around 1200 individuals the process runs in about half an hour.

If you add an Endselect into the selectloop at the point that the 16th
qualifiying week has been read, however the process grinds to a halt!
(in around 4 hours it only produced 5% of the total output)

Ignoring that changing the process to be set based rather than row at
a time would probably be the best approach, I am puzzled that asking
the program to return fewer rows should take so much longer -
presumably there is an overhead in processing the endselect - so is it
ever better to use endselect rather than just not processing the
remainder of the records?

"Tim Ellis" <tellis3 (AT) csc (DOT) com> wrote

Was adding an ENDSELECT the only thing you changed? There is plainly
something wrong. Ending the select loop (obviously) should be faster. (I
take it you are using embedded SQL in a host language such as C or Cobol?)

Roy

Yes it was the only thing I added (well no it wasn't, but it was the
only thing I took out again to return to the previous level of
performance) and yes it is embedded in Cobol.

Karl managed to suggest a solution though. The Selectloop was a
REPEATED query, and taking the REPEATED clause out fixed the problem -
the suggestion is that (a) because the query is REPEATED the optimiser
takes longer to produce a query plan and (b) the ENDSELECT in some way
invalidates the plan, causing it to be re-optimised every time

"Tim Ellis" <tellis3 (AT) csc (DOT) com> wrote

I disagree with Karl only cautiously (well, this time anyway). But all that
sounds like baloney. In the first place I've never heard of such a thing,
it certainly isn't desirable, and it couldn't happen without someone writing
a lot of code to make it work wrong. And in the second place, even if the
optimizer took *up to* 10 times longer because it is a repeated query, it
still wouldn't account for the scale of slow-down you saw.

A much more plausible explanation is that the first query has atypical
parameters and the optimizer chooses and caches a plan that actually stinks
for typical cases. Why it only started to be a problem when you added the
ENDSELECT is a mystery but I'd rather think it was a coincidence than think
that I've misunderstood repeated queries for 20 years.

(I guess my idea of "cautiously" includes calling something baloney. It is
really only a cunning plan to provoke Karl into giving chapter and verse on
it.)

Roy

At 10:56 AM +0100 10/28/05, Roy Hann wrote:

Well, I can be provoked, but in this case it would be useful to know
a couple of things: first, does a printqry in the REPEATED case actually
show the selectloop query being defined over and over, and second, how
complicated is that query and might it be using the extra 10x timeout
allowance that repeated queries get?

If one assumes some sort of magic condition whereby the REPEATED select
loop bound a crappy query plan just when the ENDSELECT was taken off,
then Roy makes a perfectly valid point. REPEATED queries can lock in a
plan that runs poorly for some parameter values.

I don't have any proof that ENDSELECT invalidates a stored plan. The code
in that area is not exactly easy to follow, but ENDSELECT does go through as
an attention "interrupt" and it's possible that a side effect is to lose
the saved plan out of QSF.

Tim, any chance of a followup with REPEATED and printqry turned on?

Karl

Yes, but not immediately I'm afraid...

I managed to get halfway through a test run yesaterday before the
Network decided to disconnect me, but it did get as far showing that
the query was only defined once. It didn't appear to show any of the
queries as taking an inordinat amount of time either ...

We can modify/update the record data of a form by looping through its
records. So, we are generally following the below 'code'
(http://tinyurl.com/2dzgckv) to do the same.





// loop through the records


var count = 1;

var maxCount = controller.getMaxRecordIndex();

for(var count=1; count<=maxCount; count++) {

// select the record

controller.setSelectedIndex(count);

/* Do any operations with the

selected record */

}

But, there is an issue with this method. You may face the issue when
your foundset contain more than 200 records. Servoy only fetches the
first 200 records at the start up and then when you select the 200th
record; it fetches another set of 200 records to make the foundset of
400 records. Like wise, it will fetch the records. So, if your foundset
have 500+ records and you will follow the above method, your script will
run only for first 200 records as because the
controller.getMaxRecordIndex() method will return 200 at the start up.


--
elizas