Re: SQL load or DBLOAD

If you are talking about a large enough number of rows, dbload is definitely faster for the simple reason that you will run into a long transaction using load from/insert into. After you wait for the rollback to complete, you can then use dbload. dbload allows you to do a commit every x rows, which avoids the long transaction scenario altogether. Alternatively, you can break the input file up into several smaller files and use the load syntax, but this can be tedious and you now have to worry about a bunch of small(er) load running to completion instead of one dbload that will complete regardless (assuming that you either have no errors or specify a large enough value for -e if you do have them). As I understand it, and I could definitely be wrong since I have never used it, there are a number of bugs associated with the high performance loader. I do know that the dba's at my last job tried using it to load a 170,000,000 row table. They were quite happy with the performance of it until I tried to do a 'select <date-column> from table' and Informix told me it could not convert the value of the date column to a valid date. There were a couple of other problems, but the bottom line is they had to drop and recreate the table and then reload it using dbload. They weren't so happy after that. Paul Watson wrote: > Carlson@WHSmith wrote: > > > Informix: 7.23.UC4 > > OS: HPUX 10.01 > > > > I'm looking at a partial database reorg this weekend and started looking > > at my reorg shell script. I need to shave a bit of time off this > > process, as our data has grown considerably. Short of using the > > High-Performance Loader (haven't had the opportunity to play with it > > yet), which would be faster: > > > > load from "filename" > > insert into table_name> > > > OR > > > > DBLOAD -d database -c configfile . . . etc . . > > [cutting] > > Personally I'd use dbload 'cos I believe it is more robust and easier to > control. However, last time I asked ITS which was quicker I was told load > was marginally quicker but only because of control. > > Depending on your existing and proposed layout there might be some mileage > in investigating parallel loading from scripts. > > -- > Paul Watson # > WF Software Ltd # I don't suffer from stress - > Tel: +44 1436 674729 # I'm a carrier. > Fax: +44 1436 678693 # > www.wfsoftware.co.uk #