Re: 2 Informix SE problems

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? Why are you counting the whole table. Is this used for some other purpose? Have you got some example code and context? > 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 right. You shouldn't index fields with NULL values or a very low number of distinct values. > 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. Perleeeese! In SE???? > 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. Yes, see a psychiatrist, you must be mad. :-) SE is a very robust little engine, but it was never designed to handle those volumes of data. The figures you give are probably quite quick. Upgrade to OnLine. ;-) Cheers, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +------------------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work... force it! | |Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! | |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+------------------------------------------------+