Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Select Loop - Endselect takes longer?

  
   Database Forums (Home) -> Ingres RSS
Next:  [Info-ingres] Bad Constraint?  
Author Message
Tim Ellis

External


Since: Feb 25, 2005
Posts: 21



(Msg. 1) Posted: Thu Oct 27, 2005 7:22 am
Post subject: Select Loop - Endselect takes longer?
Archived from groups: comp>databases>ingres (more info?)
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?

 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Roy Hann3

External


Since: Oct 20, 2004
Posts: 472



(Msg. 2) Posted: Thu Oct 27, 2005 11:55 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
"Tim Ellis" wrote in message

> 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?

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

 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Tim Ellis

External


Since: Feb 25, 2005
Posts: 21



(Msg. 3) Posted: Fri Oct 28, 2005 1:14 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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
 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Tim Ellis

External


Since: Feb 25, 2005
Posts: 21



(Msg. 4) Posted: Fri Oct 28, 2005 5:38 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Back to top
Login to vote
Roy Hann3

External


Since: Oct 20, 2004
Posts: 472



(Msg. 5) Posted: Fri Oct 28, 2005 6:55 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
"Tim Ellis" wrote in message

> 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.

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
 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Tim Ellis

External


Since: Feb 25, 2005
Posts: 21



(Msg. 6) Posted: Wed Nov 02, 2005 5:07 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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 ...
 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Ingres Forums

External


Since: Oct 20, 2009
Posts: 12



(Msg. 7) Posted: Thu May 06, 2010 5:18 am
Post subject: Re: Select Loop - Endselect takes longer? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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
------------------------------------------------------------------------
elizas's Profile: http://community.ingres.com/forum/member.php?userid=35583
View this thread: http://community.ingres.com/forum/showthread.php?t=5467
 >> Stay informed about: Select Loop - Endselect takes longer? 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Strange Select Loop problem... - Open VMS v7.2-1 ii2.0/0308 axm.vms I'm (trying to) test a (cobol using embeded SQL) program that uses a cursor to select individuals from a master table, then a Select Loop to look at individual records from a transaction table, starting at the current...

Process hanging on Endselect - We have a process which needs to produce an average figure over the last 16 qualifying weeks. The way it currently works is to select the rows from the two relevant tables ordered by date descending, then in a select loop check if the week qualifies and...

Sub-select errors - I am trying to write an SQL update statement. It updates certain customers records (top 100 of those with a certain balance.) My first try had a simple Update statement with a sub-select and order-by. Fairly simple. update nb.customers set .... where....

GRANT SELECT FOR DELETE - Hi all, I've a question about grants... I've a group that have only grant for DELETE any table, and when the user execute the comand SQL DELETE the ingres show this message "E_US0DAE SELECT on table: no GRANT or GRANT compatible permit exists.&quo...

[Info-Ingres] When where clause *Really* alters what you s.. - Hi Everyone, I'm running II 9.0.4 (a64.lnx/105)NPTL patch12125. On a brand new database try this...Don't worry it won't crash anything. sql your_new_db << SQL_END \r \continue set autocommit on \g drop view named_object_view; \p\g create view..
   Database Forums (Home) -> Ingres All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]