Re: <No Subject Supplied>
naomi@qip.anasazi.com writes:
|> > Alternatively, if you don't actually need Committed Read isolation, just
|> > SET ISOLATION TO DIRTY READ, and it will work fine.
|>
|> The problem I have with setting ISOLATION is that it affects the entire
|> database, and sometimes I only want a particular table.
You could, of course, only execute the isolation change immediately before
querying that table, then set it back as soon as you are done.
|> 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.
Dave
Disclaimer: These opinions are not those of Informix Software, Inc.
**************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney