Re: Insert cursors?
A 4GL developer inserting 7.5 million rows with prepared singleton INSERTs asked whether insert (buffered) cursors would be faster. The consensus: yes, buffered insert cursors are much faster if the data is clean, but error handling is painful — a failure is only detected when the buffer is flushed (by FLUSH or a buffer-filling PUT), so you must check sqlca (sqlerrd[2]) for how many rows actually went in and cache/re-PUT the remaining rows yourself. Art Kagel pointed to his dbcopy.ec in utils2_ak as ESQL/C sample code; violations/validation tables with filtered triggers were also suggested as a way to catch bad rows afterwards.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
From: strangiato@my-deja.com > >Online 7.x Informix 4gl > >I have a 4gl app which inserts 7.5 million rows into a table as a result >of the main foreach looping that many times. My inserts are PREPAREed >but singleton. Could the insert be slowing me down? I've never used >insert cursors are they quicker? Buffered cursors can be very fast indeed, but you have a problem if a row fails for some reason. You then lose the contents of the buffer, and you can't really tell what you've lost. But if your data is clean, it'll fly. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Obnoxio The Clown wrote: > From: strangiato@my-deja.com > > > >Online 7.x Informix 4gl > > > >I have a 4gl app which inserts 7.5 million rows into a table as a result > >of the main foreach looping that many times. My inserts are PREPAREed > >but singleton. Could the insert be slowing me down? I've never used > >insert cursors are they quicker? > > Buffered cursors can be very fast indeed, but you have a problem if a row > fails for some reason. You then lose the contents of the buffer, and you > can't really tell what you've lost. But if your data is clean, it'll fly. > I am pretty ignorant about 4gl, but isn't there something equivalent to sqlca.sqlerrd[2] in ESQL/C to allow you to determine for each PUT how many rows have been successfully shipped and inserted? Heiko
Heiko Giesselmann wrote: > Obnoxio The Clown wrote: > > From: strangiato@my-deja.com > > >Online 7.x Informix 4gl > > > > > >I have a 4gl app which inserts 7.5 million rows into a table as a result > > >of the main foreach looping that many times. My inserts are PREPAREed > > >but singleton. Could the insert be slowing me down? I've never used > > >insert cursors are they quicker? > > > > Buffered cursors can be very fast indeed, but you have a problem if a row > > fails for some reason. You then lose the contents of the buffer, and you > > can't really tell what you've lost. But if your data is clean, it'll fly. > > I am pretty ignorant about 4gl, but isn't there something equivalent to > sqlca.sqlerrd[2] in ESQL/C to allow you to determine for each PUT how many rows > have been successfully shipped and inserted? Yes, but you have to be able to recreate the row that failed, and fix the problem, and then recreate all the rows after the one that failed and resubmit the whole lot before continuing. This tends to be rather tricky to do! -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Heiko Giesselmann wrote: > Obnoxio The Clown wrote: > > From: strangiato@my-deja.com > > >Online 7.x Informix 4gl > > > > > >I have a 4gl app which inserts 7.5 million rows into a table as a result > > >of the main foreach looping that many times. My inserts are PREPAREed > > >but singleton. Could the insert be slowing me down? I've never used > > >insert cursors are they quicker? > > > > Buffered cursors can be very fast indeed, but you have a problem if a row > > fails for some reason. You then lose the contents of the buffer, and you > > can't really tell what you've lost. But if your data is clean, it'll fly. > > I am pretty ignorant about 4gl, but isn't there something equivalent to > sqlca.sqlerrd[2] in ESQL/C to allow you to determine for each PUT how many rows > have been successfully shipped and inserted? Yes, but you have to be able to recreate the row that failed, and fix the problem, and then recreate all the rows after the one that failed and resubmit the whole lot before continuing. This tends to be rather tricky to do! -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>
> have been successfully shipped and inserted? > > Yes, but you have to be able to recreate the row that failed, and > fix the problem, and then recreate all the rows after the one that > failed and resubmit the whole lot before continuing. > > This tends to be rather tricky to do! > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN > #include <disclaimer.h> What about using validation tables and filtered triggers in this case ? You could check for errors AFTER loading all the data without having many problems WHILE the main action. But... when I see which famous guys answered this posting already I think that's maybe too easy ? Greetings, Chris
Obnoxio The Clown wrote: > > From: strangiato@my-deja.com > > > >Online 7.x Informix 4gl > > > >I have a 4gl app which inserts 7.5 million rows into a table as a result > >of the main foreach looping that many times. My inserts are PREPAREed > >but singleton. Could the insert be slowing me down? I've never used > >insert cursors are they quicker? > > Buffered cursors can be very fast indeed, but you have a problem if a row > fails for some reason. You then lose the contents of the buffer, and you > can't really tell what you've lost. But if your data is clean, it'll fly. Using insert cursors you will not know if a row that was inserted into the buffer contained an error until the buffer is flushed either by a FLUSH <cursor> statement or by the PUT the fills the buffer. Then you have to handle the error, determine from sqlca how many rows were properly inserted, and reinsert any rows after that which can be inserted. As Jonathan states it can be tricky and involves caching the inserted data locally yourself in a data structure you can rescan to find the data in error and either log it or correct the problem and the data for the rows that were PUT after that row and re-PUT them from your cache. This process is made easier if you determine how many rows will fit in the buffer and manually flush so you do not have to check after every PUT statement. There is ESQL/C code that does this whole operation in the source for my dbcopy.ec utility in the package utils2_ak, which one could peruse and convert to 4GL-isms. Art S. Kagel
Chris Brauer wrote: > > > have been successfully shipped and inserted? > > > > Yes, but you have to be able to recreate the row that failed, and > > fix the problem, and then recreate all the rows after the one that > > failed and resubmit the whole lot before continuing. > > > > This tends to be rather tricky to do! > > What about using validation tables and filtered triggers in this case ? > You could check for errors AFTER loading all the data without having many > problems WHILE the main action. > > But... when I see which famous guys answered this posting already > I think that's maybe too easy ? Too modern -- we learned our trade in the days before there were cop-outs like violations tables. At least, I did. I am not wholly convinced by the theory of violations tables, and I haven't ever used them, so I tend to forget them. I really don't like the idea that some random corruption of my load file could mean that only 6 of the 10 records associated with some item are actually available in the database. It seems to me rather like an integrity problem. But it may be easier to have the database stash stuff away for you. I don't know. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"