Re: 2 Informix SE problems

In article <32ACAC1D.3A99@netins.net>, Randy Klindt <kpc@netins.net> writes >I have a few Informix SE 5.05 problems.: > >1. I have a customer with a database that has around 6 million records >with about a 300 byte record size. SELECT count(*) FROM <tablename> >with no where clause takes 5-10 minutes. Same with a dbschema on the >same table. It seems that the engine is processing every row in the >table. Shouldn't it get the number out of systables? It will always count the rows by accessing them all physically. > >2. In that same table above, if I have an index on a field that has a >large number of nulls (anywhere from 20% - 50% null values in that >field), updating that field or deleting the record takes upwards of 5-10 >minutes. > >This is a problem because in the next few months we are going to be >loading about 14 million more records (will be decreasing the record >size to about 240) into this database and I can't even imagine what kind >of time it will take then. You need to append an additional column to make the contents unique but still allow the index to be used for it's intended purpose - which is presumably finding quickly the small number of rows with a specific value. This doesn't happen because the values are NULL but because you have too many rows (for the index structure) with the same key value. -- Sally Woolrich