Re: 2 Informix SE problems

In <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? No, this isn't so. The systables number is only updated when you phsically run UPDATE STATISTICS on the database. It's not maintained on the fly. It's used by the optimiser at runtime to decide on the best path to do the query. After that, if you have a nice primary index on the table (a serial or some such), your count(*) should not take anything like as long as 10 minutes depending on hardware, fragmentation, load etc etc etc etc. 10 seconds would seem more like it. >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. Others here could best answer this for you, but to me it seems a bit scary to have nulls (especially 50%!) in an index field. Take a look at the DB design and see if this can be overcome. Even if they weren't nulls, having an index that is 50% one value is going to slow done updates and deletes on a large table by a huge amount. You may be inclined to get rid of it completely if possible. >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. Take a good lok at dropping the indexes prior to the load, and recreating them afterwards. I've found this to speed large loads by an order of magnitude on occasions. Bryan Tonnet batonnet@zeta.org.au