Re: Need help loading huge table

Saul Rubin wrote: > > Hi all, > I need some help. A client of mine has a table which needs to be loaded > on a regular basis. The table currently has about 20 million records and > will probably grow to 50 million within a few months. The table has > 7 fields (mostly int's). I have a C program which generates a flat file > which I then load into the table. After all the data is loaded, the > indexes are created. This takes HOURS. > Any suggestions?? > Thanx in advance. > Saul When you say your creating a flat file, I'll assume the source of the data is not in a loadable format. If its in fixed length format, try dbload, it can read flat files. Somehow I suspect that won't work (its worth the try though). Another meathod is a bit more subtle, eliminate the intermediate flat file. You use piplines all the time: ps -ef | grep foo > outfile Why not apply the same thing. Assume that convert.c is your C program that creates the flat (pipe delimited?) file. Change it to output to <stdout>. Then do: $ mknod np p $ convert > np & $ echo "load from np insert into table" | dbaccess database - $ rm np Output will be blocked on the pipe untill you begin to read in the load. The load will terminate when it receives EOF. Hmm, in fack you could put it all in the C program and use popen too. Beyond this, Informix "Data Fragmentation" and multi CPU's would probable speed the rest up.