Where did you see the vacuum happening inside the transaction? It's just
right before begin...anyway. It seems I'm unable to make clear the
actual "issue" of mine, but anyhow it's not a bit deal. I just wanted to
report what I have noticed, that's all.
On 08/28/2015 03:43 PM, R.Smith wrote:
>
> 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
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
