Re: Ref: Performance

cheryl@redriverad-emh1.army.mil (cheryl@RRAD05.ARMY.MIL) wrote: : Hurray, Jack ! : Seems like you could have answered like that the first time.... : A soft answer ....(is better) : I have a performance problem...and I hope it will not fall into : the bad category. : I added another index to a table in my database (the 6th one) : and my performance took a turn for the worse. : 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? In what way did performance take a turn to the worse? Normally, Adding indexes *may* affect insert/update/delete performance. More indexes to maintain, trees to balance, etc. Informix will handle integer indexes better that character string indexes, IMHO. Investigate building indexes when they are needed, i.e. reporting, etc. Depending on what you need to do, temp tables may be a good idea for reporting if nothing else remedies your problems. Use SET EXPLAIN ON and analyze the sqexplain.out file. This will tell you a lot. Don't pay much attention to the cost of # of rows returned. Look at how the optimizer is using indexes. If it has taken a turn to the worse in retrieval operations, it may be a problem with the optimizer. The cost-based optimizer will, sometimes, use the wrong index. Sometimes this can be fixed by updating statistics. Sometimes not, the optimizer will look at your query and it's statistics and pick the wrong index to perform your query against. This *sometimes* can be remedied by repeating the join condition in the Where clause. customer Index 1 is on customer name Index 2 is on customer # credit Index 1 is on customer # Select * from customer, credit where customer.cust_id = credit.cust_id and customer.id = 12345678 and credit.level > 4 You look at sqexplain.out and the query is doing a sequential search on customer and credit. Sometimes, the following will jog the optimizer's memory: Select * from customer, credit where customer.cust_id = credit.cust_id and customer.cust_id = credit.cust_id and customer.id = 12345678 and credit.level > 4 If this works, you will see in sqexplain.out that the query is doing an indexed search on both tables with a lower filter on credit level. It's a weird trick, but I understand the optimizer has a number of glitches and 6.0 or 7.0 will premier a new optimizer strategy. : We are doing very little 4GL, mostly .ace reports and perform screens : with ESQL behind the the perform srcreen. Is 4GL faster? No. : (I know, it depends on the data, design and hardware...), but : is it possible to get the performance using temp tables instead : of indexes? -- @ David Tomlinson @ @ dtomlnsn@netcom.com @ @ "By day fantastic birds flew through the petrified forest, and @ @ jeweled crocodiles glittered like heraldic salamanders on the @ @ banks of the crystalline river. By night the illuminated man @ @ raced among the trees, his arms like golden cartwheels, his @ @ head like a spectral crown...." @