LOAD FROM... stmt is very slow

Help!? I try to do LOAD FROM 'yaddayadda' DELIMITER '|' INSERT INTO yaddayadda and it is operating in glacial time. It's taking about 3 seconds per *row* of the table (line of text). I tested it with 2000 lines and it took forever and I quit (It had gotten to about 1500 in a few hours). I tested it with 20 lines and it took about 1 minute, which is still insane. The table has 10 ordinary columns, nothing special. I need to populate it with a large amount of data to test a large query's performance (need over a million rows of bogus data at least.) Here's the things I've already looked at: - No other database programs are running at the same time. - The 'uptime'-reported load on the (SGI o200) is about 0.05. - The '|' delimited file is formatted correctly for the table. - The table starts out completely empty, just having been deleted, so its not an issue of a poorly indexed huge table being slow. - I made a dummy program to do several single-row inserts into the same table in ESQL-C. It runs plenty fast. There are magnitudes of difference between ESQL-C's performance and dbaccess's performance, but ESQL-C doesn't recognize the 'LOAD' command, so I can't just do it that way. Further info: I am using 'dbaccess qwerty@asdf load.sql', where 'load.sql' contains the single line command mentioned at the top of this message. The box o' manuals I got with the Informix server failed to explain what 'dbload' is, or how to use it. From other posts here I get the impression that it is supposed to be better and the Informix support for the SQL LOAD statement is not very fast, but I don't know how to find info about dbload. The version is Informix 7.23.FC1 -- [----------------------------------------------------------------------] [ Steven L. Mading at BioMagneticResonanceBank (BMRB). UW-Madison ] [ Programmer/Analyst/(acting SysAdmin) mailto:madings@bmrb.wisc.edu ] [ Room B1108C, 'Old' Biochemistry Bldg, (410 Henry Mall) ]

Hi! One guess: try dbaccess databasename script without @ - this may want to try to access via network. You should be able to find dbload info on Informix website, but 1) create file which looks like this: file "loadfile.unl" delimter "|" 99; - put the actual number of columns instead of 99 insert into tablename; Call this one something like load.cmd 2) Use dbload -d databasename -c load.cmd -l logfile It will use instructions from load.cmd and load data into databasename Any errors will be logged in file "logfile" in your current directory. BTW: Have you just created this table or it was huge and has all records deleted. Also remove all indexes and load, then re-create indexes. HTH Michael e-mail me if you still have problems. Steve Mading wrote: > Help!? > > I try to do LOAD FROM 'yaddayadda' DELIMITER '|' INSERT INTO yaddayadda > and it is operating in glacial time. It's taking about 3 seconds per > *row* of the table (line of text). I tested it with 2000 lines and it > took forever and I quit (It had gotten to about 1500 in a few hours). > I tested it with 20 lines and it took about 1 minute, which is still > insane. The table has 10 ordinary columns, nothing special. I need to > populate it with a large amount of data to test a large query's > performance (need over a million rows of bogus data at least.) > > Here's the things I've already looked at: > - No other database programs are running at the same time. > - The 'uptime'-reported load on the (SGI o200) is about 0.05. > - The '|' delimited file is formatted correctly for the table. > - The table starts out completely empty, just having been deleted, so > its not an issue of a poorly indexed huge table being slow. > - I made a dummy program to do several single-row inserts into the > same table in ESQL-C. It runs plenty fast. There are magnitudes > of difference between ESQL-C's performance and dbaccess's > performance, but ESQL-C doesn't recognize the 'LOAD' command, so > I can't just do it that way. > > Further info: > I am using 'dbaccess qwerty@asdf load.sql', > where 'load.sql' contains the single line command mentioned at the top > of this message. The box o' manuals I got with the Informix server > failed to explain what 'dbload' is, or how to use it. From other posts > here I get the impression that it is supposed to be better and the > Informix support for the SQL LOAD statement is not very fast, but I > don't know how to find info about dbload. > > The version is Informix 7.23.FC1 > > -- > [----------------------------------------------------------------------] > [ Steven L. Mading at BioMagneticResonanceBank (BMRB). UW-Madison ] > [ Programmer/Analyst/(acting SysAdmin) mailto:madings@bmrb.wisc.edu ] > [ Room B1108C, 'Old' Biochemistry Bldg, (410 Henry Mall) ]

I just wanted to say thanks to everyone who helped with this problem. Just a note, the Informix manuals *do* actually describe 'dbload', but they do it in a book called 'Migration Guide', which was not where I thought to look. Looking at the title I expected it to be all about migrating from other DB engines to Informix (Oracle, Sybase, etc). Turns out it is about migrating data from one Informix database to another, which is why it describes the load/unload techniques.

Have you thought about using the high-performance loader??? It does the job quite nicely if your platform supports it. Otherwise I'd do as the few suggestions talk about turning off your logging to the data base, drop your indexes, and then load. This is doing effectively what the high-performance loader is doing, but you still must contend with the physical log and buffers, whereas the hp-loader bypasses the physical log as well as indexes, logical logs, etc. I remember hearing one of the instructors in an Informix training class talk about the size of the physical log having a bearing on data loading, but do not remember the specifics. Tim Steve Mading wrote: > > Help!? > > I try to do LOAD FROM 'yaddayadda' DELIMITER '|' INSERT INTO yaddayadda > and it is operating in glacial time. It's taking about 3 seconds per > *row* of the table (line of text). I tested it with 2000 lines and it > took forever and I quit (It had gotten to about 1500 in a few hours). > I tested it with 20 lines and it took about 1 minute, which is still > insane. The table has 10 ordinary columns, nothing special. I need to > populate it with a large amount of data to test a large query's > performance (need over a million rows of bogus data at least.) > > Here's the things I've already looked at: > - No other database programs are running at the same time. > - The 'uptime'-reported load on the (SGI o200) is about 0.05. > - The '|' delimited file is formatted correctly for the table. > - The table starts out completely empty, just having been deleted, so > its not an issue of a poorly indexed huge table being slow. > - I made a dummy program to do several single-row inserts into the > same table in ESQL-C. It runs plenty fast. There are magnitudes > of difference between ESQL-C's performance and dbaccess's > performance, but ESQL-C doesn't recognize the 'LOAD' command, so > I can't just do it that way. > > Further info: > I am using 'dbaccess qwerty@asdf load.sql', > where 'load.sql' contains the single line command mentioned at the top > of this message. The box o' manuals I got with the Informix server > failed to explain what 'dbload' is, or how to use it. From other posts > here I get the impression that it is supposed to be better and the > Informix support for the SQL LOAD statement is not very fast, but I > don't know how to find info about dbload. > > The version is Informix 7.23.FC1 > > -- > [----------------------------------------------------------------------] > [ Steven L. Mading at BioMagneticResonanceBank (BMRB). UW-Madison ] > [ Programmer/Analyst/(acting SysAdmin) mailto:madings@bmrb.wisc.edu ] > [ Room B1108C, 'Old' Biochemistry Bldg, (410 Henry Mall) ] -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- http://www.datad.com .-- .- .