On 2015-08-28 01:17 PM, Yahoo! Mail wrote:
> Obviously you did not get my issue; something is wrong and your timer
> suggestion indicates this. During the execution of each command, I
> would monitor it with *watch "du test.db*"*. The journal size would go
> mad even surpassing the database's actual size at some moments.
>
> *sqlite> .timer on
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 100) insert into t1(a) select datetime('now') from c;
> commit;
> Run Time: real 0.207 user 0.000000 sys 0.004000
>
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 100000000) insert into t1(a) select datetime('now')
> from c; commit;
> Run Time: real 94.226 user 73.096000 sys 4.788000
>
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 100) insert into t1(a) select datetime('now') from c;
> commit;
> Run Time: real 209.612 user 4.724000 sys 21.588000*
Firstly, that's a 100-million inserts, not a billion as in the previous
post. The billion inserts should have taken around 1000s or 20-ish minutes.
It might be the vacuum that takes the time for you... the drop table is
quick.
If I run the same through SQLitespeed hooking SQLite 3.8.11.1 with full
diagnostics (and probably worse hardware than you have), I get about
120sec (2 minutes) on the insert into an empty table, and 3.6s on the
drop+insert 10 items.
I am not vacuuming inside the following script since vacuums cannot
happen inside transactions, but I vacuum directly after it and the
vacuum takes less than 2 seconds, so I doubt that is the problem either.
--
================================================================================================
drop table if exists t1;
create table t1(a datetime);
with recursive c(x) as (values(1) union all select x + 1 from c where x
< 100000000) insert into t1(a) select datetime('now') from c;
-- Script Stats: Total Script Execution Time: 0d 00h 02m and
00.082s
-- Total Script Query Time: 0d 00h 02m and
00.064s
-- Total Database Rows Changed: 100000000
-- Total Virtual-Machine Steps: -2094967211
-- Last executed Item Index: 3
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2015-08-28 14:25:16.109 | [Success] Script Success.
-- 2015-08-28 14:25:45.088 | [Success] Transaction Committed.
--
================================================================================================
drop table if exists t1;
create table t1(a datetime);
with recursive c(x) as (values(1) union all select x + 1 from c where x
< 10) insert into t1(a) select datetime('now') from c;
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
03.605s
-- Total Script Query Time: 0d 00h 00m and
03.582s
-- Total Database Rows Changed: 10
-- Total Virtual-Machine Steps: 305
-- Last executed Item Index: 3
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2015-08-28 14:26:29.095 | [Success] Script Success.
-- 2015-08-28 14:26:29.239 | [Success] Transaction Committed.
--
================================================================================================
Maybe some more information about your specific use case and environment
is needed.
Cheers,
Ryan
