The ever elusive UpSert . . .

Well, this is probably trivial in comparison to some systems out there but, we have a 9.4ud6 engine running on HPUX 11i and need a nice way to either load or update 30,000 records in one table. Our app is a small perl script running on the same machine as the engine. Short of 30,000 insert statements with a little logic to decide if an update might be needed instead I can't come up with a much better way to load the data. Considering that we will probably have a higher percentage of new data in the table than old, any thoughts? ( maybe a load statement??) As a side note, there is also a trigger on the table we are wanting to load data into to keep in mind. -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University

Chris Salch said: > Well, this is probably trivial in comparison to some systems out there > but, we have a 9.4ud6 engine running on HPUX 11i and need a nice way to > either load or update 30,000 records in one table. Our app is a small > perl script running on the same machine as the engine. Short of 30,000 > insert statements with a little logic to decide if an update might be > needed instead I can't come up with a much better way to load the data. > Considering that we will probably have a higher percentage of new data > in the table than old, any thoughts? ( maybe a load statement??) I would insert and if the unique constraint fails, I would then update. -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

On 5/11/07, Chris Salch <ChrisSalch@letu.edu> wrote: > Well, this is probably trivial in comparison to some systems out there > but, we have a 9.4ud6 engine running on HPUX 11i and need a nice way to > either load or update 30,000 records in one table. Our app is a small > perl script running on the same machine as the engine. Short of 30,000 > insert statements with a little logic to decide if an update might be > needed instead I can't come up with a much better way to load the data. > Considering that we will probably have a higher percentage of new data > in the table than old, any thoughts? ( maybe a load statement??) > > As a side note, there is also a trigger on the table we are wanting to > load data into to keep in mind. Depending on your level of bravery, you could consider the SQLUPLOAD program that is a part of the SQLCMD package of programs available from the IIUG Software Archive (http://www.iiug.org/software). It is designed to deal with that scenario - it has multiple modes, but if you're confident the vast majority are inserts, you can specify 'try insert, fall back on update'. Or you could let the heuristic mode do it - it keeps track of the last N operations, and it tries an insert if there have been more inserts than updates recently, or it tries an update if there have been more updates recently However - due warning - it is ALPHA-quality software (and has been for a number of years). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.

The rule of thumb that I use is that if 70% of the operations are INSERT then try INSERT first and if it fails with a UNIQUE index/constraint violation error then update the existing row. If more than 30% of the final operations are likely to be UPDATEs then it is MUCH cheaper to try the UPDATE and if zero rows have been updated then perform the INSERT. Failed INSERTS are very expensive, especially if there are more than a few indexes on the table. Art S. Kagel ----- Original Message ----- From: Chris Salch <ids@iiug.org> At: 5/11 11:35:49 Well, this is probably trivial in comparison to some systems out there but, we have a 9.4ud6 engine running on HPUX 11i and need a nice way to either load or update 30,000 records in one table. Our app is a small perl script running on the same machine as the engine. Short of 30,000 insert statements with a little logic to decide if an update might be needed instead I can't come up with a much better way to load the data. Considering that we will probably have a higher percentage of new data in the table than old, any thoughts? ( maybe a load statement??) As a side note, there is also a trigger on the table we are wanting to load data into to keep in mind. -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.