Re: 2 Informix SE problems

On 9 Dec 96 at 18:17, Randy Klindt wrote: > 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? No. If you UPDATE STATISTICS and SELECT nrows FROM systables WHERE tabname = <tablename> you will get the info out of systables... > 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. You really should never have indexes which are highly duplicate. Perhaps you might make the index on the current field and one or more other fields. > This is running on a IBM RS/6000 58H with new SSA Drives and 256MB RAM. > I have rebuilt/reloaded the table, rebuilt all the indexes, and bchecked > the file. Any ideas would be appreciated. Also anybody using this > large of tables with SE? If so I'd like to know if you have any hints > on handling large databases. I wouldn't use SE. Which is not to say you can't! Try upping the number of disk buffers in your kernel. HTH. -- Ciao, Billy I'd be in favour of apathy, but I just couldn't be bothered...