Received wisdom
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 or select and if no record found do an insert or use a violations table TIA -- Clive
Clive Eisen 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 > or > select and if no record found do an insert > or > use a violations table I haven't tested this but here are my hunches. Options 1 and 3 are the same if there are no duplicates and the difference between them increases as the number of duplicates increases. I suspect option 1 will be faster since it throws away duplicates and option 3 will be a little slower since violations are recorded in another table and there will be an overhead in populating this. Option 2 is almost certainly the slowest since your doing a manual check but the database engine will automatically check every row you insert again regardless of whether you have already checked it. There are a lot of variables that may affect the speed. Here is just a few of them: - Proportion of flushes to disc using LRU writes or writes during checkpoints. - The tool used to do the insert. - No. of rows you commit after. - Whether the source data file is in a file residing on the database server, reducing TCP/IP communication. - No. of indices on the table - it may be quicker to drop some indices before the insert and and rebuild them afterwards if you can. Ben
On 18/12/06, Ben Thompson <ben@nomonitorsoftspam.com> wrote: > Clive Eisen 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 > > or > > select and if no record found do an insert > > or > > use a violations table > > I haven't tested this but here are my hunches. > > Options 1 and 3 are the same if there are no duplicates and the > difference between them increases as the number of duplicates increases. > I suspect option 1 will be faster since it throws away duplicates and > option 3 will be a little slower since violations are recorded in > another table and there will be an overhead in populating this. > > Option 2 is almost certainly the slowest since your doing a manual check > but the database engine will automatically check every row you insert > again regardless of whether you have already checked it. > > There are a lot of variables that may affect the speed. Here is just a > few of them: > > - Proportion of flushes to disc using LRU writes or writes during > checkpoints. > - The tool used to do the insert. > - No. of rows you commit after. > - Whether the source data file is in a file residing on the database > server, reducing TCP/IP communication. > - No. of indices on the table - it may be quicker to drop some indices > before the insert and and rebuild them afterwards if you can. > > Ben > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > When you say 'insert 40 M numbers' do you mean inserting integers into a single column table (or into one column of a multi column table). If so can you not eliminate the duplicates before insertion (sort -u or sort |uniq) ? Alternatively create an intermediate RAW table and insert all the data (avoids the overhead of logging), alter the table to STANDARD and create a non-unique index over the data. Finally select unique values from here to insert into the final table (if this can be built RAW to start with so much the better). Keith