On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail <stefanossofroniou542 at yahoo.com
> wrote:
>
>
> On 08/26/2015 09:03 PM, Richard Hipp wrote:
>
> Time stands still for multiple rows, as long as they are within the
>> same sqlite3_step() call. For example, if you run:
>>
>> CREATE TABLE t1(a DATETIME);
>> WITH RECURSIVE
>> c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000000000)
>> INSERT INTO t1(a) SELECT datetime('now') FROM c;
>>
>> The entire INSERT statement will be a single sqlite3_step() call, and
>> so all billion rows of t1 will get set to the same time.
>>
> I have tested this code with version 3.8.10.2 using "DB Browser for
> SQLite" and it would crash; the same with SQLite Manager that uses the same
> version. I have decided to use the latest SQLite version available from GNU
> / Linux Debian testing 64-bit; it's 3.8.11.1 and something strange is
> happening. I hope I'm the reason behind its rather peculiar behavior.
>
> I tested the following code
>
> *sqlite> drop table if exists t1; create table t1(a datetime); begin; with
> recursive c(x) as (values(1) union all select x + 1 from c where x <
> 1000000000) insert into t1(a) select datetime('now') from c; commit;*
>
> It would take ages to finish and that is logical; it's a billion rows it
> has to insert, even though I have used begin - commit.
>
> Now the rather strange behavior would be when I use the up arrow key to
> reuse the previous code but replacing *1000000000* with *10*. It takes the
> same time to finish as before and that is not right; I'm dropping the table
> and it should take nearly 0 milliseconds for 10 rows.
>
> Can someone confirm this?
>
I have been using 3.8.11, so I just downloaded 3.8.11.1 to test your exact
query, cut & pasted from above. Here is the output from my session:
X:\>sqlite3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> drop table if exists t1; create table t1(a datetime); begin; with
recursive c(x) as (values(1) union all select x + 1 from c where x <
10000000) insert into t1(a) select datetime('now') from c; commit;
Run Time: real 16.751 user 16.520506 sys 0.062400
sqlite> drop table if exists t1; create table t1(a datetime); begin; 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; commit;
Run Time: real 0.387 user 0.374402 sys 0.000000
sqlite>
Note: I pasted the first query that begins drop table, then used up arrow
and edited the 10000000 to 10 for the second query. Further, I didn't have
time to try a full billion, so I settled for 10 million. Also used a
transient in-memory database. Finally, I am using Windows 7 Professional on
a box with 16 GiB of RAM.
Do you see the same behavior for a test of 10M rows (that the second line
takes the same amount of time as the first)?
--
Scott Robison
