Re: Insert cursors?

Heiko Giesselmann wrote: > Jonathan Leffler wrote: > > 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! > > If there is some sort of memory management for host variables in place I4GL doesn't have user-accessible memory management functions. > it's about 100 lines of C code to implement the actual function > (to execute PUTs and buffer rows until actually inserted), including > all sorts of error handling. Probably true for the simpler cases (eg no massive blobs). I guess it depends on the buffer size and the row size. If the rows are so big that each PUT is also flushed to the database (or if blobs are involved), then there's limited benefit from an insert cursor (though I'd guess there is still some benefit). If the rows are small enough to group several PUT operations before the data is flushed to the database, then the storage management overhead (in terms of space) isn't big enough to worry about. There is some code that would have to be written, and you'd have to know when the data is flushed (which is tricky - it could happen on any PUT), and so on. Of course, this is the trouble with many optimizations; they only work under limited circumstances and understanding when the optimization applies (and determining whether it currently applies) is typically harder than understanding how it works. > But then again, I > don't know if there is any straightforward way to do that 4GL. IMHO, there isn't. -- 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!"