Igor, how about simple test? Normalized database is big and slow.
$ time ./test_norm_idx.tcl
real 32m54.978s
user 32m14.885s
sys 0m39.842s
$ time ./test_idx.tcl
real 7m19.005s
user 6m55.226s
sys 0m11.717s
$ ls -lh *db
2,1G test_idx.db
7,1G test_norm_idx.db
test_idx.tcl
=============================
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}
db eval {
create table parent(dt DATETIME, user_id INTEGER);
create index parent_dt_idx on parent(dt);
create index parent_user_id_idx on parent(user_id);
create virtual table parent_fts using fts4(childs TEXT);}
db transaction {
for {set i 1} {$i<=100000} {incr i} {
set time [clock microseconds]
set childs ""
for {set j [expr {$i*1000}]} {$j<=[expr {$i*1000+1000}]} {incr j} {
lappend childs $j
}
db eval {insert into parent(dt, user_id) values ($time, 1)}
db eval {insert into parent_fts(childs) values ($childs)}
}
}
test_norm_idx.tcl
=============================
#!/usr/bin/tclsh8.5
package require sqlite3
# test normalized
sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}
db eval {create table link(dt DATETIME, user_id INTEGER, parent_id
INTEGER, child_id INTEGER);
create index link_child_id_idx on link(child_id);
create index link_dt_idx on link(dt);
create index link_user_id_idx on link(user_id);}
db transaction {
for {set i 1} {$i<=100000000} {incr i} {
set time [clock microseconds]
db eval {insert into link(dt, user_id, parent_id, child_id)
values ($time, 1, $i%1000, $i)}
}
}
P.S. With versioning of all records we need some additional fields and
normalized database
is very big and very slow.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
