Re: Received wisdom
On 12/18/06, Clive Eisen <clive@serendipita.com> wrote: > Does anyone have benchmarks/wisdom relating to the following? > (Informix Dynamic Server Version 10.00.UC3R1TL on Linux) > > I have to insert 40M numbers into a table in a logged database > > The target table has a unique index on this column > > The source data has duplication. > > Is it faster to > do an insert and ignore -268 duplicate key errors Ignoring the possibility of using insert cursors, this takes one round trip message per row to insert. > or > select and if no record found do an insert This takes two round trips - one for the select, one for the insert. > or > use a violations table This takes one round trip, and always does inserts (sometimes into the violations tables). In terms of work done, therefore, option 1 is most effective. The question is - what do you need to do about the errors? Note that handling errors with an insert cursor is complex at best. What percentage of your data do you think will fail the duplicate test? It actually doesn't matter in this scenario - but it might if you have to choose between insert and update. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Jonathan Leffler wrote: > > Ignoring the possibility of using insert cursors, this takes one round > trip message per row to insert. > >> or >> select and if no record found do an insert > > This takes two round trips - one for the select, one for the insert. > >> or >> use a violations table > > This takes one round trip, and always does inserts (sometimes into the > violations tables). > > In terms of work done, therefore, option 1 is most effective. > > The question is - what do you need to do about the errors? Nothing - all I need in the target table is the unique records > > Note that handling errors with an insert cursor is complex at best. Noted :-) > > What percentage of your data do you think will fail the duplicate > test? It actually doesn't matter in this scenario - but it might if > you have to choose between insert and update. > Duplication rate is around 75% and the rate will get worse over time I suspect. Thanks for the clear insight -- Clive