Re: Performance problem with big table!

1. Have you run update statistics? 2. Have you run the query specifying the column names instead of "*"? (Actually, I don't know if that affects performance, but it's good form). 3. What kind of fragmentation are you using? 4. Are you using PDQ? 5. How many physical disks is your table spread across? (Relates to #3) Beyond that, you may want to post the exact query you're running, the schema of your table (including your create index statement), the update statistics statement you're using and a copy of your onconfig file. If you really want to get good help, describe your hardware setup in a little more detail as well (plus EXACT version of IDS and Sun OS you're running). --Chuck > We run an IDS 7.31 on a SUN Enterprise 450. > The database contains some tables with no more than 1000000 rows. > > Today we added a new table with 10.000.000 rows and 3 indexes. (the table has > less than 10 fields) > > A simple query like "select * from table where label = x" take more than 10"!!! > (and label is a unique index)

Hi Chuck, Indexes are detached and located in other dbspaces! And we have exactly 1 extend per fragment. The query is: select field1, field2, field3 from table where field1 = x (field1 is integer and indexed/unique) And it worked! We run the debugger and seen that the index is NOT used!!! Why? It worked during one week. X -- Xavier Mertens, . . EuroNet Internet Network Operation Center . * a subsidiary of France Telecom XM3-RIPE XM1-6BONE . On Fri, 10 Dec 1999, Chuck Renaud wrote: > > 1. Have you run update statistics? > > 2. Have you run the query specifying the column names instead of > "*"? (Actually, I don't know if that affects performance, but it's good > form). > > 3. What kind of fragmentation are you using? > > 4. Are you using PDQ? > > 5. How many physical disks is your table spread across? (Relates > to #3) > > Beyond that, you may want to post the exact query you're running, > the schema of your table (including your create index statement), > the update statistics statement you're using and a copy of your > onconfig file. If you really want to get good help, describe your > hardware setup in a little more detail as well (plus EXACT version of > IDS and Sun OS you're running). > > --Chuck > > > We run an IDS 7.31 on a SUN Enterprise 450. > > The database contains some tables with no more than 1000000 rows. > > > > Today we added a new table with 10.000.000 rows and 3 indexes. (the table has > > less than 10 fields) > > > > A simple query like "select * from table where label = x" take more than 10"!!! > > (and label is a unique index) > >

Hi Xavier, we have had also the same problem. It is probably one of the many bugs in IDS 7.30-7.31 regarding the false query plans of the optimizer. In our SAP R/3 system it affected the most frequently used transaction. On Friday it worked correctly, the next Monday it did not want to use the index and therefore we got hundreds of dumps and time-out errors. You have to trick it out. Is it the primary key which the optimizer does not like any more? Then you should add a secondary key with the same fields as the primary, plus one dummy field. Or if the optimizer takes another key but it reads with that the whole table sequentially, you should temporarily drop this "unuseful" index to test if your query works correctly without that. Good luck Peter -- Peter Dzvonyar SAP-Consultant R/3 BC _______________________________________________________________