For Cheryl@redriverad-emh1.army.mil

Sorry all - can't seem to go directly. } } ----- Transcript of session follows ----- } 550 cheryl@redriverad-emh1.army.mil... Host unknown } } ----- Unsent message follows ----- } Received: by hpbs3645.boi.hp.com } (1.37.109.4/15.5+IOS 3.22) id AA00728; Fri, 3 Jun 94 09:14:00 -0600 } From: Jack Parker <jparker@hpbs3645.boi.hp.com> } Return-Path: <jparker@hpbs3645.boi.hp.com> } Message-Id: <9406031514.AA00728@hpbs3645.boi.hp.com> } Subject: Try this again } To: cheryl@redriverad-emh1.army.mil } Date: Fri, 3 Jun 94 9:14:00 MDT } Mailer: Elm [revision: 70.85] } } > ----- Transcript of session follows ----- } > 550 <cheryl@RRAD05.ARMY.MIL>... Host unknown } > } > ----- Unsent message follows ----- } > Received: from hpbs3645.boi.hp.com by hp.com with SMTP } > (1.36.108.7/15.5+IOS 3.13) id AA15922; Fri, 3 Jun 1994 07:42:10 -0700 } > Return-Path: <jparker@hpbs3645.boi.hp.com> } > Received: by hpbs3645.boi.hp.com } > (1.37.109.4/15.5+IOS 3.22) id AA00552; Fri, 3 Jun 94 08:45:35 -0600 } > From: Jack Parker <jparker@hpbs3645.boi.hp.com> } > Message-Id: <9406031445.AA00552@hpbs3645.boi.hp.com> } > Subject: Re: Ref: Performance } > To: cheryl@RRAD05.ARMY.MIL ("cheryl@redriverad-emh1.army.mil") } > Date: Fri, 3 Jun 94 8:45:35 MDT } > In-Reply-To: <9406021419.AA14316@rmy.rmy.emory.edu>; from "cheryl@redriverad-emh1.army.mil" at Jun 2, 94 9:09 am } > Mailer: Elm [revision: 70.85] } > } > > } > > I added another index to a table in my database (the 6th one) } > > and my performance took a turn for the worse. } > } > Update or read performance? } > } > > } > > Where is the trade off, when are there too many indexes? How } > > do most folks handle the need for indexes? Do they create } > > temp tables for ace reports and have only 2 or 3 main indexes? } > } > I like to feel that it's four. But it really depends. I'm working on } > a utility right now to do 'stress' testing on indices. And yes - when } > an SQL statement is slow one of the first things we look at is splitting } > it in two and using a temp table. I spent three days two weeks ago trying } > out different things with a 4gl program which used 32 temp tables and took } > 75 minutes to run. I managed to get it down to 38min just by changing the } > SQL - I wound up NOT creating one of the temp tables. } > } > > } > > We are doing very little 4GL, mostly .ace reports and perform screens } > > with ESQL behind the the perform srcreen. Is 4GL faster? } > > (I know, it depends on the data, design and hardware...), but } > > is it possible to get the performance using temp tables instead } > > of indexes? } > } > 4gl isn't faster - after all it's the SQL portion that is generally the } > culprit not the code. 4GL is just a WHOLE LOT more powerful. } > } > What I would suggest is grabbing the SQL out of your ace report and running } > it through isql. The first line of your SQL code should be 'SET EXPLAIN ON;' } > This will generate 'optimizer plan' info in a file called sqexplain.out - } > generally in your home directory. Reading that file will give you an } > insight into how the engine plans to do the work. } > } > There is a chapter in the 'Guide to SQL Tutorial' which deals with } > perfomance on tables - I read it yesterday. doesn't really cover indices } > that well - but gave me some ideas. } > } > j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| Someday you'll go far, jparker@hpbs3645.boi.hp.com | if you catch the right train. (208) 396-5388 (W) (208) 384-1623 (H) | _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________