comparison insert to insert cursor
Georg Walk asked how much faster insert cursors are than plain INSERT. Art S. Kagel said the biggest boost comes from a larger FET_BUF_SIZE (32767) with flushing only when the buffer is full, plus ESQL/C fetch arrays. Thomas Ronayne suggested cursors for larger loads with periodic commits (about every 5,000 rows). Jonathan Leffler saw 10:1 over a slow network, but roughly no gain for big rows or blobs.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, can anybody say how much is the typical (or approximately) performance advantage using "insert cursor" in comparison to using "insert". In wich situation we should not use "insert cursor" ? Georg
On Tue, 27 Jul 2004 08:01:23 -0400, Georg Walk wrote: You get the biggest boost from combining insert cursors with increased comm buffer size (FET_BUF_SIZE=32767) and flushing the insert cursor only when the buffer is full. The throughput improvement can be dramatic. If you are acquiring the data to insert from another table/database, you can further improve performance by making the fetches using the Fetch Array feature of ESQL/C (see the manual). Art S. Kagel > Hi, > > can anybody say how much is the typical (or approximately) performance > advantage using "insert cursor" in comparison to using "insert". In wich > situation we should not use "insert cursor" ? > > Georg
Georg Walk wrote: >Hi, > >can anybody say how much is the typical (or approximately) performance >advantage using "insert cursor" in comparison to using "insert". In wich >situation we should not use "insert cursor" ? > >Georg > > > > > Well, that kind of depends -- how many rows are you inserting, is it a logged data base? One or two (or a few) rows, not too often, just insert (and begin work, commit work before and after if it's a logged data base). Lots of rows, often, use a cursor and be sure to begin work and commit work every so often (if it's logged) so you don't blow the logs and locks. I typically judge on the basis of less than a thousand rows, just insert; more than a thousand, use a cursor (and, in either case, begin work and commit work if the data base is logged). That's in tables that contain more than a million rows. Actually I've never found any real performance difference to speak of (on big, honking Sun servers so judge from there), but, if you really want to "do it the right way," you spend a couple of minutes writing the code for cursors (for insert, update, or delete) and you don't have to worry. I also arbitrarily commit every 5,000 rows no matter what and I don't blow the locks. The real advantage of a cursor, I think, is that you don't have to wait forever for rollback (in a logged data base) if something goes wrong and you don't have "long transaction" errors if you run out of locks (again, in a logged data base). What the heck, can't hurt, probably will help.
Georg Walk wrote: > can anybody say how much is the typical (or approximately) performance > advantage using "insert cursor" in comparison to using "insert". In wich > situation we should not use "insert cursor" ? As others noted, the mileage you get will vary. When I added insert cursors to DBD::Informix, the test environment (which was unusual) showed me a performance gain of 10:1 using insert cursors. What was unusual was that I was sitting in Menlo Park, about 50 miles (80 km) from the development machine in Oakland, yet the database server was back in Menlo Park. So, when connected over a long-haul network (MAN, if not WAN -- and I don't know how direct the Menlo-Oakland connection was), I got a huge performance benefit out of the reduced number of round-trips. More usually, the gain is more modest. It is approximately nil if you have big rows (say 4K or more), or if you are inserting blobs. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/