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 > > > > >

Thanks for the suggestion. Unfortunately, we're inserting 500,000 new rows into a table that already has over 2 million rows in it. So if we disable the indexes/constraints and re-enable them later, it rebuilds them for the whole table, not just for the rows we inserted. So the amount of time saved by disabling the indexes is overshadowed by the amount of time it costs us to re-enable them. Also, it doesn't really look like HPL is a viable option, because most of the INSERTs are done via INSERT INTO... SELECT FROM... statements; we'd have to externalize to use HPL. I've already run some tests, and using HPL in deluxe mode is still slower (surprisingly enough) than doing INSERT INTO SELECT FROM with constraints enabled. Using HPL in express mode isn't an option, because then you need to take a Level-0 archive after loading before the table can be used, and on our system that takes a few hours to do. BEATRIZ ROCHA <BEATRICE.ROCHA@worldnet.att.net> wrote in message news:01be7ca8$b58513e0$ceb44d0c@camila... > 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 > > > > > > > > > >

On Fri, 2 Apr 1999 13:05:31 -0500, "Thomas J. Girsch" <tgirsch@iname.com> wrote: >> > 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) That last one is your problem. Each foreign key will have a duplicate index corresponding to it, which has to be updated for each insert. You really depend on 17 other tables for this table? That's tough to fathom. Anyway, if the domain size of any of these foreign keys is relatively small, you will be dealing with VERY long lists of rowids for each key value. These are very expensive to maintain during inserts, as they are sorted by rowid, so each insert causes the list of rowids to be resorted. Also, for every insert you do, you will be doing a lookup on the primary key of the referenced table to see if the key value already exists. If any of the foreign keys are referencing small lookup tables, you are probably much better off substituting a check constraint. The more of those foreign keys you can eliminate, the better your insert performance will be. Dave