Re: 2 Informix SE problems

Mark D. Stock wrote: > > 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? > Not that counting the whole table is that important, I can get that number elsewhere. I'm wondering if this isn't an indicator of another problem. If an update statistics take about 20 seconds, why does a count take 10 minutes. The table has a serial column. > > 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???? Not my idea, it was my customer's. ($$$$$) > > > 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. ;-) > I've tried to get them to do it and yes they are making me crazier than hell. Thanks for the assistance, Randy