To the SQLite devs:
After recent discussion about the row-count issue w.r.t. Nulls in primary keys etc. I have been somewhat wrestling with how to
improve this from a user perspective.
To explain: 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. The
problem escalates with bigger tables and most users detest sluggishness and all of us try to make things less so.
I thought of keeping the count cached, which works while the connection is open, but becomes useless if re-opened (another app may
have changed that in the meantime - actually this may even have happened while the connection is open). I've also tried cheating by
inspecting the file size and upon big enough files, defer row-counting with some form of [This is a large DB - Click here to check
the row count, this may take some time.] user message where the row-count is supposed to appear - but as you must be aware I have
run across DBs several GBs in size with only a few hundred-K rows in the large tables, and one DB I have weighs in at only 250MB but
have about 11mil rows in the main table. Not to mention the fact that one table might have all the rows and the others may all be small.
To address the table-walk for Indices containing NULLs: Most DB admins and system engineers are savvy to this problem, I am sure in
over 90% of the cases they do not keep NULLs in primary keys even if SQLite allows this. (I think the figure is over 99% but I am
weary of exaggeration) - but even if they do have NULLs, sometimes you just need to know the amount of rows, not the amount of
non-NULL value rows. I realise this cannot fit in an algebraically verifyable SQL result such as count() because of those few cases,
but a possible pragma can fix it for the rest of us.
I realise this problem is rather specific to the DB admin programs as opposed to user systems, but a Pragma "rowcount(TableName);"
would be spectacular where the count is simply a fast reference to the total rows regardless of content with documentation pointing
out the difference.
I am very willing to submit a documentation draft if this feature gets added (to save someone some work) but I am not versed well
enough in the SQLite internals to attempt a patch. Also, the solution needn't fall upon my suggestion, any other suitable means of
making row count fast-determinable would be welcome.
Thank you kindly,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
