On 2014/12/11 17:19, Simon Slavin wrote:
In my table which had about 300 million (sic.) rows I did this
SELECT count(*) FROM myTable;
to count the number of rows. After half an hour it was still processing and I
had to kill it.
I know that the internal structure of a table means that this number isn't
simple to produce. But is there really no faster way ? This table is going to
have about six times that amount soon. I really can't count the rows in less
than a few hours ?
Not so strange I think...
The highest number of rows I have tried to do maintenance of that sort on was only about 50 million though DB size was around 150GB,
and the row-count on that took some time to establish, but in the order of minutes, not hours and certainly not days. I have here
and now only a 10-million row DB to run some quick tests on a machine without SSD or anything good - seems to take around 1 min 20s
on the first attempt to do a count() and around 33s on the next attempts (I'm assuming caching doing its bit here).
Scaling that up - it becomes apparent that a ~300-mil row DB with 30x the rows than what I am testing should take around 30x the
time, which is 1m20 x 30 which is around 40 minutes assuming similar hardware.
You probably stopped it just shy of the goal. Either way, 30 minutes and 45 minutes are /exactly/ the same amounts of time when
measured in impatience base units.
I have no idea how to make it faster or in any way how to improve the speed on
a query that simple.
Knowing you (a bit) I already know you have thought about all of this a lot and you won't be asking if solutions were easy to come
by, so my usual advice probably won't help much, other than to ask - do you really need to know the row-count? Is knowing it is
around 300mil not enough? Any other query you might envision on this data-set will probably take in the order of hours for simple
ones and days for anything joined. Best is to have a lone-standing machine churn through it over time and getting some results from
time to time as a long-term project. (I know you are already well-aware of this).
Cheers and best of luck!
Ryan
(PS: I know the above isn't really helpful or qualified as an "answer", sorry
about that - you are dealing with a special beast indeed).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
