On Sat, 13 Dec 2014 14:15:15 +0200 RSmith <rsm...@rsweb.co.za> wrote:
> Most DB Admin tools out there displays the number of rows in a table > when you select it or open it, so too the one I am working on and > after testing stuff on Simon's question about the row counting, I > realised that selecting a large table always pauses somewhat (with > suitable progress bar) to simply open and report the usual > statistics. The culprit of course being row-counting. Every DB Admin tool I've ever used proved to be more hinderance than help. They seem to be written by the moderately competent to help the novice, and run out of gas or fall over when faced with anything complex. So the number of tools with feature X is no measure of the value of X. (Notable example: the tool should keep every query and result in a time-sequenced transcript log, so that prior results can be re-examined and prior queries modified. Most tools disassociate query from output and invite the user to modify the query in-place, destroying the prior.) My first question, then, is whether or not the rowcount is so interesting that it must be known before a table can be operated on. I suggest the answer is No. The relative & approximate sizes of the tables is known to the admin in most cases and, when it is not, the information is readily discovered on a case-by-case basis. Would a proxy figure do? Is it enough to know the number of pages or bytes allocated to a table? I don't know if such is available, but if it is perhaps that would serve your purpose. Otherwise your user might be satisfied by lazy evaluation: run your count(*) query on a fork and provide the answer through a pipe or similar, in such a way that the user can cancel it if it's placing too high a load on the system. That said, I'm puzzled why rowcount isn't maintained and exposed in SQLite as part of a table's metadata, particularly when indexes/keys are present. The cost of maintaining a rowcount is small, in terms of computation and complexity. ISTM it is valuable information to the system itself in evaluating query-plan costs. The "because it's lite" argument doesn't hold water insofar as applications are paying a high cost (even if somewhat optimized) for a common query. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
