Re: 4gl experts ( Mr. Leffler?) please help, how can I speed up this 4gl?

You do have a lot of OPEN/FETCH calls within WHILE loops that are expected to return either 0 or 1 rows. I'd move fetching that data to an OUTER join that is part of the main FETCH for the loop. That will reduce the OPEN/FETCH overhead and allow the DB engine to run more efficiently. This also assume's that your DBA has done a good job. S.W. Respectful <please_reply@newsgroup.com> wrote in message news:7oufiu$6di@newsstand.cit.cornell.edu... > Informix 4gl 7.20.UE1, Informix Dynamic Server Version 7.30.UC5 , AIX > > I attach a 4gl program which is running slower than I want it to. It > inserts about 200 rows per second and takes 6 hours. > > For every row in a master table it selects rows in corresponding child > tables and for every child row, having retrieved an extra key from a > reference table, it inserts a row into a detail table. If you sum up all > the child rows, that is the numbers of rows inserted in the detail table > (about 3.4 million). > > I thought I'd written it as efficiently as possible with prepares and such, > but I'm sure it could be speeded up. > > The target table has no indexes and all other tables have indexes for the > necessary joins. > > Excuse me for not posting my email address, it's an anti-spam measure. > > Thanks!

Cheers Steven Steven Wilcoxon <swilcoxon@iqmktg.com> wrote in message news:204285895F88D1118FAC00A0C933CDDF31593F@mail.silverstream.com... > You do have a lot of OPEN/FETCH calls within WHILE loops that are > expected to return either 0 or 1 rows. I'd move fetching that data to an > OUTER join that is part of the main FETCH for the loop. That will > reduce the OPEN/FETCH overhead and allow the DB engine to run > more efficiently. > > This also assume's that your DBA has done a good job. > > S.W. > > Respectful <please_reply@newsgroup.com> wrote in message > news:7oufiu$6di@newsstand.cit.cornell.edu... > > Informix 4gl 7.20.UE1, Informix Dynamic Server Version 7.30.UC5 , AIX > > > > I attach a 4gl program which is running slower than I want it to. It > > inserts about 200 rows per second and takes 6 hours. > > > > For every row in a master table it selects rows in corresponding child > > tables and for every child row, having retrieved an extra key from a > > reference table, it inserts a row into a detail table. If you sum up all > > the child rows, that is the numbers of rows inserted in the detail table > > (about 3.4 million). > > > > I thought I'd written it as efficiently as possible with prepares and > such, > > but I'm sure it could be speeded up. > > > > The target table has no indexes and all other tables have indexes for the > > necessary joins. > > > > Excuse me for not posting my email address, it's an anti-spam measure. > > > > Thanks! > > >

A few things that might help: I) Make sure you're using page level locking during this batch process. 2) Make sure you've been updating DB statistics at a reasonable level (Medium) 3) Double check you IDXs. They should match your table join criteria. ( Run some of the SQL w/ expain on. You may see something that points to the cause) 4) If all else fails, it's may be time to ask the boss for a hardware upgrade. < :*( Good luck! Respectful wrote in message <7p2glb$9dt@newsstand.cit.cornell.edu>... >Cheers Steven > >Steven Wilcoxon <swilcoxon@iqmktg.com> wrote in message >news:204285895F88D1118FAC00A0C933CDDF31593F@mail.silverstream.com... >> You do have a lot of OPEN/FETCH calls within WHILE loops that are >> expected to return either 0 or 1 rows. I'd move fetching that data to an >> OUTER join that is part of the main FETCH for the loop. That will >> reduce the OPEN/FETCH overhead and allow the DB engine to run >> more efficiently. >> >> This also assume's that your DBA has done a good job. >> >> S.W. >> >> Respectful <please_reply@newsgroup.com> wrote in message >> news:7oufiu$6di@newsstand.cit.cornell.edu... >> > Informix 4gl 7.20.UE1, Informix Dynamic Server Version 7.30.UC5 , AIX >> > >> > I attach a 4gl program which is running slower than I want it to. It >> > inserts about 200 rows per second and takes 6 hours. >> > >> > For every row in a master table it selects rows in corresponding child >> > tables and for every child row, having retrieved an extra key from a >> > reference table, it inserts a row into a detail table. If you sum up >all >> > the child rows, that is the numbers of rows inserted in the detail table >> > (about 3.4 million). >> > >> > I thought I'd written it as efficiently as possible with prepares and >> such, >> > but I'm sure it could be speeded up. >> > >> > The target table has no indexes and all other tables have indexes for >the >> > necessary joins. >> > >> > Excuse me for not posting my email address, it's an anti-spam measure. >> > >> > Thanks! >> >> >> > >