Re: Need help loading huge table

Jay Konigsberg wrote: > > 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. If you have 7.2 you could use the P-loader - it is INCREDIBLE!!!! We have a 23 million row table (about 650 bytes per record). Here are some stats: It took 13 hours to load with dbload and had to be broken into 4 seperate files to load (they were too large for the 4 gig file system logical volume limit. It was a little less on the unload. This was when the table was at 18 million rows. When we got p-load, we were at 22 million rows. We unloaded the table in parallel to a p-loader created 'device' which consisted of 8 4 gig disks. The unload took about 65 minutes. We dropped the table and recreated it with new extent and fragmentation schemes (table maintenance) and started the reload. It took 1 hour 23 minutes. It completely blew me away. Our hardware consists of: HP9000 K400 HP-UX 10.01 40 GIG RAID 0 and RAID 5 File system 80 GIG raw database space (2 gig Jamacia Drives - 40 in all) We are at Online 7.2 something. If you are interested I will post the exact version later. We also use it to translate ebsidic (sp!!) files transferred directly from the mainframes and load directly to tables - no other process needed. We used to write all kinds of c programs for data conversion and cleaning. This tool is not quite perfect - but it is truly the best add on product I have seen in years. FAST and very flexible. WOW!!! Informix has a true winner with this product!!! James Saville BellSouth Telecommunications