sqlite timing

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