Re: Tetra and Update Statistics

Skip wrote: > Hi Paul, > > I ran into the same problem at two clients using the same combination > of IDS 7.31 and Tetra CS/3 3.1. After running update statistics high > on columns heading an index etc. Tetra became extremely slow > especially posting batches and the like. My conclusion was that the > query that Tetra does on the database in some or other way "bypasses" > the IDS optimizer :-( (I realise it sounds funny). I tried the same > query through isql with sqexplain on and it was just as slow. It doesn't sound funny for applications written for flat file access. Baan has the same problem, although I am surprised you see a performance problem in a single query run in dbaccess. The query optimiser is very good at optimising multiple table joins. When an application simply asks for a single record or a few records from a single table, the optimiser can do very little except go get that record. With the application doing the join, it is unlikely to be as efficient as the database server's own optimiser. > I solved the immediate problem by dropping the distribution on the > Tetra tables and running a standard update statistics on a regular > basis on those tables. The rest of my database works fine with the > "tuned" update statistics script. As Andrew said, running UPDATE STATISTICS LOW will update things like record counts, so shouldn't deteriorate any query, but may not help either. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list

Mark D. Stock wrote: > > As Andrew said, running UPDATE STATISTICS LOW will update things > like record counts, so shouldn't deteriorate any query, but may not > help either. I would expect that if they *have* defined indexes, then the engine must know that there are some rows, or it will ignore the indexes even when the erstwhile PK index is available and being targeted. I mean, even a flat-file style access will surely say "gimme the record with serial number 207683" so an index on the serial number should be known to contain rows or the engine will think it's quicker to scan serially. As for distribution stats, I'm still boggled that entire applications like BAAN can be written so that it uniformly dies in the ar5e when the engine is given some clue about the data stored in all the tables :- can anyone explain this in words of less than 5 syllables?