RE: HPF is it really faster than a simple unload/load

In previous benchmarks (some time back) we rated HPL at 2GB/CPU/HR. LOAD came in at 1GB/HR - if you can handle the long transaction. DBLOAD came in at 1GB/HR - no issue with transactions. Insert cursor came in at 2GB/HR (or was it 3? - that order of magnitude). So if you have a 4 processor box, you can count on 8GB/HR with HPL. Unloads are faster - depending on your fragmentation. I have heard of load rates of 10GB/CPU/HR and have achieved 5GB/CPU/HR with HPL - it all depends on your data and where it's landing. HPL is not that hard to configure - make sure you have 'x' running and working. ipload - starts the interface and initializes the onpload database the first time. From there you can go to generate job which in 4 short mouse clicks will set up a load and unload job for any given table. Obligatory XPS aside. I gather XPS uses the same load utility under the covers, however the interface to it is much different. There is no GUI and no onpload database. Instead you have 'external tables' which are pointers to files. From there you can use most standard SQL commands (INSERT INTO foo SELECT * FROM ex). Load rates are similar to HPL. Since the table is now in the catalogue, you can do all sorts of other things to, including joins, aggregations - whatever (within reason). Try not to get too excited though, it does break more easily than a standard table - although it's fairly durable. A woman I work with has been using external tables instead of temp tables - her code runs faster than going to temp tables - imagine. cheers j. > -----Original Message----- > From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] > Sent: Friday, December 08, 2000 6:14 AM > To: dl@novertec.com; informix-list@iiug.org > Subject: Re: HPF is it really faster than a simple unload/load > > > From: "Donald Leclerc" <dl@novertech.com> > > >I have some big tables with over 1M records and some with > 15M with some > >indexes. > >I would like to know if it is really faster to use HPF instead of > >load/unload, I saw in informix guide that it's a lot of > configuration to > >use > >HPF, but if it's worth it! > > I assume you mean HPL? > > How long does a 1M row table unload/load take on your box? > > I did a simple test unload / load / reindex of 2M > unfragmented 70 byte rows > with 3 indexes on my dev box and timex reported < 10 minutes > for the whole > lot. (The unload file was 118MB and I'm on a RAID5 array > :-(((( , so OS > access would have been a non-trivial component of this.) > > >Actually, I am not the DBA, but want to know because the DBA is not > >interested in using HPF but I really need a faster way to > load the data. > >By > >the way, if you can tell me the difference in speed between > the "deluxe" > >and > >"express" mode. I am more intersted in the "deluxe" mode > because I saw in > >the manual that we must take a level 0 to reset a readonly > flag. Most of > >the time (not to say all) we need to load the data when the > system is > >online > >because 24x7. > > Well, if you've already found the manual... :-) > > >The other point of interest, what about the checkpoint time > during the load > >with HPF ??? > > It depends. :-) > > >Actually we have dynamic server 7.3 and no logging, dirty read. > > It is wickedly fast, even in deluxe mode. > > Express mode disables and re-enables (rebuilds) indexes. > > The only downside of HPL is that it can take up to 30 seconds > to fire up, > which may be expensive in the total time to do your > particular job. I can't > remember if you can run multiple jobs within the same > session, which means > it could cost up to 1 minute per table. > > Also, you might get better throughput with your current > environment by > fragmenting your tables across multiple dbspaces and using > PDQ and PSORT for > the current process. > > ______________________________________________________________ > _______________________ > Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com