CREATE TABLE user(
pk INTEGER PRIMARY KEY,
area INTEGER,
age INTEGER,
active INTEGER
);}
for {set i 0} {$i < $loop} {incr i} {
puts "
WITH RECURSIVE tmp(pk,area,age,active) AS
(
SELECT [expr {$i * $increment}],
500001,
5,
0
UNION ALL
SELECT pk+1,
10000*(abs(random())%9+1)+abs(random())%100000,
5*(abs(random()%3)+1),
abs(random()%2)
FROM tmp
WHERE pk < [expr {($i+1)*$increment-1}]
)
INSERT INTO user
SELECT * FROM tmp;"
}
# createSQL.tcl
set increment 100000
set loop [expr {100000000 / $increment}]
puts {PRAGMA journal_mode = OFF; PRAGMA synchronous = 0; PRAGMA cache_size = 1000000; PRAGMA locking_mode = EXCLUSIVE; PRAGMA temp_store = MEMORY;
CREATE TABLE user( pk INTEGER PRIMARY KEY, area INTEGER, age INTEGER, active INTEGER );}
for {set i 0} {$i < $loop} {incr i} { puts " WITH RECURSIVE tmp(pk,area,age,active) AS ( SELECT [expr {$i * $increment}], 500001, 5, 0 UNION ALL SELECT pk+1, 10000*(abs(random())%9+1)+abs(random())%100000, 5*(abs(random()%3)+1), abs(random()%2) FROM tmp WHERE pk < [expr {($i+1)*$increment-1}] ) INSERT INTO user SELECT * FROM tmp;" }
#EOF
Core 2 Duo 2.53 GHz, 4GB RAM, SSD:
$ time -p tclsh createsql.tcl | sqlite3 test.db
off
exclusive
real 213.37
user 208.10
sys 4.58