Re: 2 Informix SE problems

Randy Klindt wrote: > > 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. Mmm, have you tried a bcheck? What about: SELECT COUNT(DISTINCT serial_column) FROM table_name is that any quicker? Also try Billy's suggestion. > > > 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. ($$$$$) Oh, okay then. It's strange isn't it. They hire an expert like you, pay your bills, and STILL don't listen to a word of your advice. :-( > > > 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. I know the feeling. ;-) > Thanks for the assistance, No problem. -- 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! | +------------------------+------------------------------------------------+