RE: Return values ???

Posted in 1999

Topics: Performance & Tuning, Error Codes & Troubleshooting

Barnes, Steve (IFMG) — Wed, 11 Aug 1999 07:44:14 -0400 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEE3EE.D66DB26C Content-Type: text/plain; charset="iso-8859-1" Jonathan, Thanks for the reply. I was also told by Informix back in England, that a FOREACH loop had an unnecessary overhead because it would check if the cursor was open, and then open it if required, in each loop. This was because the FOREACH had an implied open at the first call. The OPEN, WHILE etc, had an explicit OPEN, and the FETCH only had to fetch the data. I think that this may have been true in the purest sense, but would only make a real difference in a large foreach with many rows being retrieved. Does this sound likely? Thanks Steve -----Original Message----- From: Jonathan Leffler [mailto:jleffler@earthlink.net] Sent: Tuesday, August 10, 1999 7:26 PM To: Barnes, Steve (IFMG) Cc: informix-list@iiug.org Subject: Re: Return values ??? Steve Barnes <SBarnes@IFMG.com> wrote: > I am curious as to why a FOREACH is considered better than OPEN, > WHILE, FETCH, CLOSE. I always thought that for large numbers of reads, > the overhead of a FOREACH instead of the alternative had performance > impacts. This was the thought a few years ago, and maybe I am behind > the times! > > Can you please clarify this for me please. I'm not sure. Which overheads? There are no overheads that I know of. A FOREACH loop is a shorthand for the loop originally shown, except that FOREACH correctly avoids fetching any data if the cursor fails to open. It is for that sort of reason that the FOREACH loop is better. Jonathan Leffler <jleffler@earthlink.net> wrote: > Octav Chiriac wrote: > > [...various variations on PREPARE and DECLARE...] > > OPEN qCursorName USING var1, var2 > > > > WHILE TRUE > > FETCH qCursorName INTO result1, result2 > > IF sqlca.sqlcode == NOTFOUND THEN > > EXIT WHILE > > END IF > > > > -- DO SOMETHING WITH YOUR results > > > > END WHILE > > > > CLOSE qCursorName > > > > FREE ... stuff > > It is much better to use a FOREACH loop because it is more > reliable: > > PREPARE p_stmt FROM "EXECUTE PROCEDURE theprocedure(?,?)" > DECLARE c_stmt FROM p_stmt > > FOREACH c_stmt USING var1, var2 INTO result1, result2 > ...Do Something With Your Results... > END FOREACH > > FREE c_stmt > FREE p_stmt -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h> ------_=_NextPart_001_01BEE3EE.D66DB26C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: Return values ???</TITLE> </HEAD> <BODY> Jonathan, Thanks for the reply. I was also told by Informix back in = England, that a FOREACH loop had an unnecessary overhead because it = would check if the cursor was open, and then open it if required, in = each loop. This was because the FOREACH had an implied open at the = first call. The OPEN, WHILE etc, had an explicit OPEN, and the FETCH = only had to fetch the data. I think that this may have been true in the = purest sense, but would only make a real difference in a large foreach = with many rows being retrieved. Does this sound likely? Thanks Steve <UL><UL> <A NAME=3D"_MailData">-----Original = Message-----</A> From:   Jonathan Leffler = [<A = HREF=3D"mailto:jleffler@earthlink.net">mailto:jleffler@earthlink.net</A>= ] Sent:   Tuesday, August 10, 1999 7:26 PM To:     Barnes, Steve (IFMG) Cc:     informix-list@iiug.org Subject:       = Re: Return values ??? Steve Barnes <SBarnes@IFMG.com> = wrote: > I am curious as to why a FOREACH = is considered better than OPEN, > WHILE, FETCH, CLOSE. I always = thought that for large numbers of reads, > the overhead of a FOREACH = instead of the alternative had performance > impacts. This was the thought a = few years ago, and maybe I am behind > the times! > > Can you please clarify this for = me please. I'm not sure.  Which = overheads?  There are no overheads that I know of. A FOREACH loop is a shorthand for the = loop originally shown, except that FOREACH correctly avoids = fetching any data if the cursor fails to open.  It is for that sort of = reason that the FOREACH loop is better. Jonathan Leffler = <jleffler@earthlink.net> wrote: > Octav Chiriac wrote: > > [...various variations on = PREPARE and DECLARE...] > > OPEN qCursorName USING = var1, var2 > > > > WHILE TRUE > >  FETCH qCursorName = INTO result1, resul

↪ replying to Barnes, Steve (IFMG)

Art S. Kagel — Wed, 11 Aug 1999 10:58:00 -0400 — source: Informix-list mailing list archive (1991-1998)

Sorry but I have to agree with Jonathan. FOREACH adds NO unneccessary overhead, never did as far as I know. I have just looked at some .ec code generated by 4GL 6.04, that should be old enough, and the FOREACH loop generated the following sequence: o Push statement string and pop into variable $statement o Prepare statement from $statement o check status of prepare & call fgl_fatal if error o declare cursor for statement_id o check status of declare & call fgl_fatal if error o open cursor_id o check status of open & call fgl_fatal if error o while(1) o fetch cursor_id into variable list o check status of fetch & call fgl_fatal if error o check for status == SQLNOTFOUND & break if TRUE o do some of my processing o call ixrsthwm() [This is the only thing I don't know about! Looks like a window management function, but why here?] o do some more of my processing o end while o close cursor_id o test status of close & call fgl_fatal if error Except for that one function call this is good clean code. True some of us would be too lazy to check all of those error returns, but that was one of Jonathan's point, eh? The FOREACH loop handles all of that for you without the pain. Anyone know what that function, ixrstwm() does? Jonathan? Art S. Kagel "Barnes, Steve (IFMG)" wrote: > Jonathan, > > Thanks for the reply. > > I was also told by Informix back in England, that a FOREACH loop had an > unnecessary overhead because it would check if the cursor was open, and then > open it if required, in each loop. This was because the FOREACH had an > implied open at the first call. The OPEN, WHILE etc, had an explicit OPEN, > and the FETCH only had to fetch the data. I think that this may have been > true in the purest sense, but would only make a real difference in a large > foreach with many rows being retrieved. > > Does this sound likely? > > Thanks > > Steve > -----Original Message----- > From: Jonathan Leffler [mailto:jleffler@earthlink.net] > Sent: Tuesday, August 10, 1999 7:26 PM > To: Barnes, Steve (IFMG) > Cc: informix-list@iiug.org > Subject: Re: Return values ??? > > Steve Barnes <SBarnes@IFMG.com> wrote: > > I am curious as to why a FOREACH is considered better than > OPEN, > > WHILE, FETCH, CLOSE. I always thought that for large > numbers of reads, > > the overhead of a FOREACH instead of the alternative had > performance > > impacts. This was the thought a few years ago, and maybe I > am behind > > the times! > > > > Can you please clarify this for me please. > > I'm not sure. Which overheads? There are no overheads that > I know of. > A FOREACH loop is a shorthand for the loop originally shown, > except > that FOREACH correctly avoids fetching any data if the > cursor fails to > open. It is for that sort of reason that the FOREACH loop > is better. > > Jonathan Leffler <jleffler@earthlink.net> wrote: > > Octav Chiriac wrote: > > > [...various variations on PREPARE and DECLARE...] > > > OPEN qCursorName USING var1, var2 > > > > > > WHILE TRUE > > > FETCH qCursorName INTO result1, result2 > > > IF sqlca.sqlcode == NOTFOUND THEN > > > EXIT WHILE > > > END IF > > > > > > -- DO SOMETHING WITH YOUR results > > > > > > END WHILE > > > > > > CLOSE qCursorName > > > > > > FREE ... stuff > > > > It is much better to use a FOREACH loop because it is more > > reliable: > > > > PREPARE p_stmt FROM "EXECUTE PROCEDURE theprocedure(?,?)" > > DECLARE c_stmt FROM p_stmt > > > > FOREACH c_stmt USING var1, var2 INTO result1, result2 > > ...Do Something With Your Results... > > END FOREACH > > > > FREE c_stmt > > FREE p_stmt > > -- > Jonathan Leffler (jleffler@informix.com, > jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see > http://www.perl.com/CPAN > #include <disclaimer.h> > > ------_=_NextPart_001_01BEE3EE.D66DB26C > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2448.0"> > <TITLE>RE: Return values ???</TITLE> > </HEAD> > <BODY> > > Jonathan, > > > Thanks for the reply. > > > I was also told by Informix back in = > England, that a FOREACH loop had an unnecessary overhead because it = > would check if the cursor was open, and then open it if required, in = > each loop. This was because the FOREACH had an implied open at the = > first call. The OPEN, WHILE etc, had an explicit OPEN, and the FETCH = > only had to fetch the data. I think that this may have been true in the = > purest sense, but would only make a real difference in a large foreach = > with many rows being retrieved. > > Does this sound likely? > > > Thanks > > > Steve > <UL><UL> > <A NAME=3D"_MailData">-----Original = > Message-----</A> > From:   Jonathan Leffler = > [<A = > HREF=3D"mailto:jleffler@earthlink.net">mailto:jleffler@earthlink.net</A>= > ] > Sent:   SIZE=3D2 FACE=3D"Arial">Tuesday, August 10, 1999 7:26 PM > FACE=3D"Arial">To:     FACE=3D"Arial">Barnes, Steve (IFMG) > FACE=3D"Arial">Cc:     FACE=3D"Arial">informix-list@iiug.org > FACE=3D"Arial">Subject:

↪ replying to Art S. Kagel

Jonathan Leffler — Thu, 12 Aug 1999 11:52:13 -0700 — source: Informix-list mailing list archive (1991-1998)

"Art S. Kagel" wrote: > Sorry but I have to agree with Jonathan. FOREACH adds NO unneccessary > overhead, never did as far as I know. I have just looked at some .ec code > generated by 4GL 6.04, that should be old enough Thanks! I've checked that 4.13 produces substantially the same code (3 versions prior to 4.16/6.04). Version 4.13/6.01 does not support the FOREACH USING, so that was added in 4.14/6.02. >[...] > o call ixrsthwm() [This is the only thing I don't know about! Looks > like a window management function, but why here?] > [...] > Anyone know what that function, ixrsthwm() does? Jonathan? I wrote it, so I suppose I should know... I'm working from memory so I may have the odd detail wrong but the gist of the answer below is correct. ixrsthwm() is one of a set of functions which help deal with the old, obsolete, -4518 error. The other functions are ixpshhwm() and ixpophwm() or thereabouts. The hwm bit indicates a high-water mark, the rst is for reset, psh for push and pop is self-explanatory; if you look at MAIN, it has ixrelhwm() at the end to release all the space. These functions allow the I4GL runtime system to release no longer needed memory from the TSS (temporary string stack?). The TSS is used to store strings returned from functions - it is auxilliary to the I4GL stack. The ixpshhwm() function records what was in use before the current I4GL function was called. The ixrsthwm() function is used at points where there can be no pending activity in the TSS to reclaim the space allocated on behalf of the current I4GL function. The ixpophwm() function removes the record established by the ixpshhwm() function. Life really gets complicated when you have a function called from within a list of values in a RETURN statement. -- Yours, Jonathan Leffler (jleffler@informix.com) #include Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn

RE: Return values ???

Related threads

Your privacy choices