Re: 2 Informix SE problems

"Mark D. Stock" <marks@west.co.za> wrote: :Bryan Tonnet wrote: :> :> In <58n37j$gf9@nntp.idgonline.no> Nils.Myklebust@idg.no (Nils Myklebust) writes: :> > >On a customers SE engine (5.01.UD2) it uses about 1/10 second to count : > >to 12914. This speed has allways been true for all my counts with no : > >where clause on SE and OnLine, whatever the number of rows. : > >I allways believed Informix stored this count somwhere with the table : > >(in the index file?) with SE. It shure isn't in systables as this is : > >only updated when update stistics is run. :> :> This seems very slow to me. I've just run a count on two tables on a :> customer's site (during normal workload). The results > : > table a 294444 rows 0.09 secs : > table b 372833 rows 0.10 secs : > :> and this was using CURRENT to display times, so the processing overhead :> of doing the LET/DISPLAY must be taken out. Nowhere near the original :> posters 6mil. rows I know, but could still be indicative unless the SE :> engine experiences some sort of logarithmic response curve as it gets :> up to larger table sizes. :My point exactly. You are surely not suggesting that it has a linear one? Excuse me, but I am. His numbers where about 0.1 seconds for 3 - 400.000 rows, and mine the same for 12.000. I believe Informix maintians and reads this count from some fixed place (possibly an extra space at the start of the index file?). It is not from the systables.nrows value for sure, which gets updated only when update statistics is executed. However this part of update statistics is also fast, and seems to read the row count value from the same place. The point of all this for the original poster: There must be something very wrong, either in his database or in my (our) understanding of the issue, as his counts takes so long. Of course as soon as you add any where clause (even where 1=1) the count will go through the table (or the index) and take much longer. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org