Posted to sqlite by evilotto at Wed Feb 02 23:16:14 GMT 2011view raw
- load libtclsqlite3.so
- set drop 1
- sqlite3 db /tmp/tdb2.sq3
- if {$drop} {
- catch { db eval {drop table props} }
- catch { db eval {drop table obj} }
- }
- catch {
- db eval {create table obj (id integer primary key, name text)}
- db eval {create table props (id integer references obj, pnam text, pval text)}
- db eval {create index pind on props (pnam, pval)}
- db transaction {
- for {set i 0} {$i < 2500} {incr i} {
- db eval {insert into obj values ($i,'object ' || $i)}
- db eval {insert into props values ($i, 'd', $i/50)}
- db eval {insert into props values ($i, 'd5', $i/500)}
- db eval {insert into props values ($i, 'm', $i%50)}
- db eval {analyze}
- }
- }
- }
- set s {select obj.name
- from obj, props p1, props p2
- where obj.id = p1.id and p2.id = p1.id
- and p1.pnam = 'd' and p1.pval = '1'
- and p2.pnam = 'm' and p2.pval = '1'
- }
- set s2 {select obj.name
- from obj, props p1
- where obj.id = p1.id
- and p1.pnam = 'd' and p1.pval = '1'
- and obj.id in (select id from props where pnam = 'm' and pval = '1')
- }
- set sx {select obj.name
- from obj, props p1, props p2
- where p1.id = obj.id and p2.id = obj.id
- and p1.pnam = 'd5' and p1.pval = '0'
- and p2.pnam = 'm' and p2.pval = '1'
- order by obj.id desc
- limit 1
- }
- set sx2 {select obj.name
- from obj, props p1
- where p1.id = obj.id
- and p1.pnam = 'd5' and p1.pval = '0'
- and obj.id in (select id from props where pnam = 'm' and pval = '1')
- order by obj.id desc
- limit 1
- }
- set sx3 {select obj.name
- from obj, props p1
- where p1.id = obj.id
- and p1.pnam = 'm' and p1.pval = '1'
- and obj.id in (select id from props where pnam = 'd5' and pval = '0')
- order by obj.id desc
- limit 1
- }
- proc timeit {s} {
- puts [db eval $s]
- puts [time {db eval $s} 100]
- }
- timeit $s
- timeit $s2
- timeit $sx
- timeit $sx2
- timeit $sx3
- package require Pgtcl
- set pdb [pg_connect -conninfo {dbname = test}]
- if {$drop} {
- catch {pg_execute $pdb {drop table props}}
- catch {pg_execute $pdb {drop table obj}}
- }
- catch {
- pg_execute $pdb {create table obj (id integer primary key, name text)}
- pg_execute $pdb {create table props (id integer references obj, pnam text, pval text)}
- pg_execute $pdb {create index pind on props (pnam, pval)}
- pg_execute $pdb begin
- for {set i 0} {$i < 2500} {incr i} {
- pg_execute $pdb "insert into obj values ($i,'object ' || $i)"
- pg_execute $pdb "insert into props values ($i, 'd', $i/50)"
- pg_execute $pdb "insert into props values ($i, 'd5', $i/500)"
- pg_execute $pdb "insert into props values ($i, 'm', $i%50)"
- }
- pg_execute $pdb commit
- pg_execute $pdb "vacuum analyze props"
- pg_execute $pdb "vacuum analyze obj"
- }
- puts "\nPostgres"
- proc timeit {s} {
- upvar pdb pdb
- pg_execute $pdb $s {puts $name}
- puts [time {pg_execute $pdb $s} 100]
- }
- timeit $s
- timeit $s2
- timeit $sx
- timeit $sx2
- timeit $sx3