sqlite的速度也很快

显示全部楼层 · 2012-5-21 10:19:41

sqlite> .timer on
sqlite> select count(*) from(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select 1 unio
n all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select
1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all sel
ect 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union
all select 3),(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3);
43046721
CPU Time: user 3.728424 sys 0.000000

千问 · 2012-5-21 10:19:41

插入100w行不到1秒
sqlite> create table t as select a.a from
...> (select 1 a union all select 2 union all select 3)a,(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
...> (select 1 union all select 2 union all select 3);
CPU Time: user 0.982806 sys 0.015600
sqlite> select count(*) from t;
1594323

千问 · 2012-5-21 10:19:41

sqlite> create table t1 as select a.a*100000+b.a*10000+c.a*1000+d.a*100+e.a*10+f.a+1 a from
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)a,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)b,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)c,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)d,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)e,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)f;
CPU Time: user 1.544410 sys 0.000000
sqlite> select count(*),sum(a) from t1;
1000000|500000500000
CPU Time: user 0.390002 sys 0.000000

千问 · 2012-5-21 10:19:41

linux下慢一些
sqlite>create table t1 as select a.a*100000+b.a*10000+c.a*1000+d.a*100+e.a*10+f.a+1 a from
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)a,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)b,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)c,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)d,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)e,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)f;
CPU Time: user 1.804726 sys 0.008999

千问 · 2012-5-21 10:19:41

〇〇发表于 2012-9-26 09:26
linux下慢一些
sqlite>create table t1 as select a.a*100000+b.a*10000+c.a*1000+d.a*100+e.a*10+f.a+1 ...
改为2表关联，就快了
sqlite> .timer on
sqlite> create table t1 as select a.a*100+b.a*10+c.a+1 a from
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)a,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)b,
...> (select 1 a union all select 2 union all select 3 union all select 4 union all select 5 union all
...> select 6 union all select 7 union all select 8 union all select 9 union all select 0)c;
CPU Time: user 0.001000 sys 0.000000
sqlite> create table t2 as select a.a*1000+b.a-1000 a from t1 a,t1 b;
CPU Time: user 0.909862 sys 0.005999
sqlite> select count(*),sum(a) from t2;
1000000|500000500000
CPU Time: user 0.288956 sys 0.000000

千问 · 2012-5-21 10:19:41

〇〇发表于 2012-9-25 20:12
插入100w行不到1秒
sqlite> create table t as select a.a from
...> (select 1 a union all select 2 ...
这个变态sql我提上去优化了一下
[执行语句1]:
create table t as select a.a from
(select 1 a union all select 2 union all select 3)a,(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3),(select 1 union all select 2 union all select 3),
(select 1 union all select 2 union all select 3);
执行成功,执行耗时4秒350毫秒. 执行号:62
影响了0条记录