I have noticed that SQLite Query Browser is running slower than other IDEs, including SQLitespeed, for some reason. Even when each IDE is set to using similar versions of the SQLite3.dll. We had a recursive query in SQB take 6 min, on other IDEs it would be less than 2 min.
My $0.02 -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith Sent: Friday, August 28, 2015 7:44 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] order by not working in combination with random() 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 This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
