Can take a LONG TIME (with a large table)… if the statistics are not up to date.
And that even trying to update the stats before trying to get the count can take a long time
From the sqlite page Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.
So analyze etc isn’t going to be fast not automagically fast
[quote=330814:@Norman Palardy]From the sqlite page Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.
So analyze etc isn’t going to be fast not automagically fast
However I’d try count(1) instead of count(*)[/quote]
Norman… I know the COUNT(*) vs COUNT(1) “trick” as I have advocated it here many times myself…
However it seems to make no difference… a table with 1 million records takes almost 30 seconds just to get the count,
And I realize that stats get “stale”… my point here was that
ANALYZE;
SELECT COUNT
was no faster than just
SELECT COUNT
UNLESS the stats had already been collected previously
that has been added as an option… but it is not fast…
and due to the nature of the app it is not possible to track what if any tables where added, dropped, or modified
SELECT COUNT(*) FROM million; // takes 3 minutes 21 seconds with no stats
SELECT COUNT8) FROM million; // takes 2 minutes 11 seconds with no stats, so it was "faster"
ANALYZE MILLION; // takes 2 minutes 4 seconds to collect stats, but doesn't seem to store them??? :(
the only way it seems to store the stats if is I do just “ANALYZE;” which then does ALL the tables in the database
and then for some reason it doesn’t seem to use them… because I know before IT DID
Option of changing engines not available…
This is an SQLite Database Manager … so the database(s)/table(s) do not belong to me, therefore my app can only change them based on the users input… And because it is specifically an SQLite manager… another database engine isn’t a solution
can’t make any such “assumptions”… I
but as an educational exercise I will try it … because if in fact it is faster… I can analyze the table and choose which method to use
Yes it was a test SQLite database with only one table 5 columns wide and 8 million records. It had a Integer auto increment field as well as 4 text fields.
I just created a new test table with 4 text fields and no integer id field. It took 14.403 seconds to insert 8 million rows from another table in the same database. Running a SELECT count(*) FROM Test2 took only 350 ms.
I’m trying to figure out what could possible be taking over 2 minute for a select count. What is the total size of the database file?
[quote=330841:@Neil Burkholder]I just created a new test table with 4 text fields and no integer id field. It took 14.403 seconds to insert 8 million rows from another table in the same database. Running a SELECT count(*) FROM Test2 took only 350 ms.
I’m trying to figure out what could possible be taking over 2 minute for a select count. What is the total size of the database file?[/quote]
18.3 gig basically I took “Eddies Electronics” and added 2 tables with a million rows each
I have worked mostly with Oracle in the past, and it kept a record count indicator… making it very fast…
FYI… I did the same thing directly from the TERMINAL and it took about as long it seemed
Create Table million ( id int,
firstname text,
lastname text,
address text,
city text,
state text,
zip text,
phone text,
email text,
photo,
taxable int)
Ok… my app using Neils table returns Count in 0.07 seconds
my table which is similar in structure takes over 2 minutes
and this only would solve PART of the problem
The problem ultimately is how to page thru that 1M+ record table?
I THOUGHT I could use OFFSET and LIMIT… but that won’t work, because SQLite reads ALL the records up to the OFFSET before emitting them to the recordset… So the farther down you page, the slower it gets… which saves nothing in the long run…or makes the lag way worse…
Any ideas?
Would be great if I could “pause” the recordset from being completely created…