RE: LOAD FROM... stmt is very slow

1. First, traditional questions: Exactly what version of Informix are you using? What is your platform? 2. What is the logging mode of your database? (ANSI, Unbuffered, Buffered, None) 3. Please attach the output of "dbschema -d databasename -t tablename tablename.sql" -----Original Message----- From: Steve Mading [mailto:madings@baladi.nmrfam.wisc.edu] Sent: Tuesday, November 02, 1999 2:10 AM To: informix-list@iiug.org Subject: 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) ]