RE: INSERT performance problems
Also, try updating statistics. You may find that makes a large difference. -----Original Message----- From: BEATRIZ ROCHA [mailto:BEATRICE.ROCHA@worldnet.att.net] Sent: Thursday, April 01, 1999 8:43 PM To: informix-list@iiug.org Subject: Re: INSERT performance problems One thing you could try is to use HPL (High Performance Loader) to load the data. It disables the keys and constraints in order to load the data, then it enables the indexes again. Even better, you could try to drop the indexes and constraints, then use HPL to load the data and then re-create the indexes and constraints. The load will be very fast, the creation of the indexes and constraints will still take some time, but you will save a a lot of time by using the HPL instead of the LOAD or DBLOAD command. Good luck. -Beatrice Rocha Thomas J. Girsch <@one.net> wrote in article <370026ec.0@news.one.net>... > Hello all. > > I'm having some performance problems at my site, and I'm hoping some of you > in the newsgroup will have suggestions that can help. > > The problem is that I've got a huge accounts receivable table (1.6 million > rows), into which I need to be able to do large inserts (hundreds of > thousands of rows) for our end-of-period posting. These inserts take > several hours to complete. My window is more like two hours. > > Having done some initial analysis, I know that indexes and constraints on > the A/R table are causing a good deal of the trouble. With constraints and > indexes disabled, the inserts take one third of the time; but to re-enable > those indexes and constraints takes even longer than if I had just left them > enabled. > > Here are some key statistics: > > # columns: 50 > rowsize: 471 bytes > idxsize: ~212 bytes > special columns: 3 > primary key: (serial), referenced by two other tables > foreign keys: 17 (I know, that's a lot) > > The table is fragmented across four dbspaces (equivalent with the number of > high-performance disks we have), and the indexes are detached, on two other > disks. > > Any suggestions will be greatly appreciated. > > - Tom Girsch > tgirsch@iname.com > > > > >
Hi, Russ. Thanks for the suggestion! We do run update statistics regularly, although I would expect that it won't make that much difference on an insert. It's far more important to selects. So it's imperative that we run update statistics AFTER the load. <Russ_Evans@doh.state.fl.us> wrote in message news:7e2vn7$4uf$1@news.xmission.com... > > Also, try updating statistics. > You may find that makes a large difference. > > -----Original Message----- > From: BEATRIZ ROCHA [mailto:BEATRICE.ROCHA@worldnet.att.net] > Sent: Thursday, April 01, 1999 8:43 PM > To: informix-list@iiug.org > Subject: Re: INSERT performance problems > > > One thing you could try is to use HPL (High Performance Loader) to load the > data. It disables the keys and constraints > in order to load the data, then it enables the indexes again. > > Even better, you could try to drop the indexes and constraints, then use > HPL to load the data and then re-create the indexes > and constraints. The load will be very fast, the creation of the indexes > and constraints will still take some time, but you > will save a a lot of time by using the HPL instead of the LOAD or DBLOAD > command. > Good luck. > -Beatrice Rocha > > > Thomas J. Girsch <@one.net> wrote in article <370026ec.0@news.one.net>... > > Hello all. > > > > I'm having some performance problems at my site, and I'm hoping some of > you > > in the newsgroup will have suggestions that can help. > > > > The problem is that I've got a huge accounts receivable table (1.6 > million > > rows), into which I need to be able to do large inserts (hundreds of > > thousands of rows) for our end-of-period posting. These inserts take > > several hours to complete. My window is more like two hours. > > > > Having done some initial analysis, I know that indexes and constraints on > > the A/R table are causing a good deal of the trouble. With constraints > and > > indexes disabled, the inserts take one third of the time; but to > re-enable > > those indexes and constraints takes even longer than if I had just left > them > > enabled. > > > > Here are some key statistics: > > > > # columns: 50 > > rowsize: 471 bytes > > idxsize: ~212 bytes > > special columns: 3 > > primary key: (serial), referenced by two other tables > > foreign keys: 17 (I know, that's a lot) > > > > The table is fragmented across four dbspaces (equivalent with the number > of > > high-performance disks we have), and the indexes are detached, on two > other > > disks. > > > > Any suggestions will be greatly appreciated. > > > > - Tom Girsch > > tgirsch@iname.com > > > > > > > > > >