Re: <No Subject Supplied>

In <1993Jul8.143903.18109@informix.com> davek@newjersey.informix.com (Dave Kosenko) writes: >|> A silly thought >|> though. To get the number of rows in a table, sometimes I : >|> >|> SELECT nrows >|> FROM systables >|> WHERE tabname = "table_name"; >|> >|> Its much faster than count(*). >It is also *not* guaranteed to return a "correct" (in the sense that it may not >be up to date) result, unless immediately preceded by an UPDATE STATISTICS. >This is because nrows is only updated when an UPDATE STATISTICS is done. >Of course, it can only reflect the value at the time of that update; any >subsequent changes to the table will not be reflected in the nrows value >(until another UPDATE STATISTICS is done). >A SELECT COUNT(*) with no where clause will get the row count from either the >tblspace tblspace page (for OnLine) or from the .idx file (for SE) both of >which are updated whenever a row is inserted or deleted. Of course, once you >get the count, it, too, is no longer guaranteed to be correct, since the row >count may change immediately after the query completes. >Given that running an UPDATE STATISTICS is going to take longer than the >SELECT COUNT(*) (since it must do a lot more work than just checking the >row count as mentioned above), I'd be inclined to believe that the COUNT(*)>would be the quicker way to go. Of course, if you are not relying on up >to the second row counts, the UPDATE STATISTICS may not be mandatory for >your particular case. I think this has been made evident, but the original problem was that I *couldn't* select count(*) using ISQL (which is strange in itself, because the 4GL program didn't have an exclusive lock on the table...). As painful as it may be, the best solution presented was to include a where clause. -- Clay Irving New York, NY "Everything should be made as simple as possible, but not simpler." Einstein