dbload vs. load
A user hit "long transaction aborted" after loading ~1.4 million rows with the LOAD statement and asked how many rows can be loaded, believing LOAD is faster than dbload because it doesn't commit periodically. Replies said the limit depends on row size and logical log number/size, and disputed the speed claim: very large transactions actually slow down (log checking traverses a linked list), so frequent commits are often faster. Suggested options: use dbload with a commit interval, make the database/table unlogged (raw), or use the High Performance Loader with express jobs. No single definitive answer was confirmed by the poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, i have a question about the load-utility. how many rows can i load without receving a "long transaction aborted"-message? I had a load-job running and after 1.4 million rows i received the mentioned message. i have read that the load-utility is faster than the dbload-utility because it doesn't commit after a certain amount of rows. my file which i wanted to load contains about 5 million rows. greetings chris
ahum it depends on the rowsize to be loaded, the number and size of
your logical logs etc.
dbload is slower but you can commit after xxx records...
Question:
my be you want to do this unlogged?? if possible change either the db
to be unlogged or dependant on what version change your table to be raw
or use the High Performance loader (a.k.a. Nuke Obstacle)
this tool can also do a commit after xx or if the rowsize is smaller
then a page
you could use express load jobs... check it out...
way fast.... in dutch: oerend hard.
Superboer.
christrier said: > > Hi, > > i have a question about the load-utility. > how many rows can i load without receving a "long transaction > aborted"-message? > I had a load-job running and after 1.4 million rows i received the > mentioned message. Well, then, I guess _you_ can load about 1.4 million rows. > i have read that the load-utility is faster than the dbload-utility > because it doesn't commit after a certain amount of rows. Bullshit. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Obnoxio The Clown wrote: > > > Bullshit. > Is bullshit 'ClownSpeak' for RTFM?
Clive Eisen said: > > Obnoxio The Clown wrote: >> >> >> Bullshit. >> > Is bullshit 'ClownSpeak' for RTFM? No, it's actually quite a subtle problem, and I hope I can remember the basics. If I don't, I'm sure Marco or Madison or JL or someone will correct me. IIRC, each operation (in this case, each INSERT) has to check or validate something in the log, and does this in a relatively inefficient manner -- traversing a linked list. So the longer the transaction gets, the slower each subsequent operation is. When the transaction is short, the cost of committing is relatively high, but when the transaction gets longer, it can often be substantially better to commit more often (if this is possible). I hope I haven't cocked up the explanation too badly. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
> Clive Eisen said: > > > > Obnoxio The Clown wrote: > >> > >> > >> Bullshit. > >> > > Is bullshit 'ClownSpeak' for RTFM? > > No, it's actually quite a subtle problem, and I hope I can > remember the > basics. If I don't, I'm sure Marco or Madison or JL or someone will > correct me. > > IIRC, each operation (in this case, each INSERT) has to check > or validate > something in the log, and does this in a relatively > inefficient manner -- > traversing a linked list. So the longer the transaction gets, > the slower > each subsequent operation is. When the transaction is short, > the cost of > committing is relatively high, but when the transaction gets > longer, it > can often be substantially better to commit more often (if this is > possible). > > I hope I haven't cocked up the explanation too badly. > > -- > Bye now, > Obnoxio This is my experience with Dbload, as the commit points get larger the runtime seems to grow 'exponentially'. So 1x 1000000 row commit point is significantly slower than 10X 100000 Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information.