Posted to sqlite by evilotto at Wed Feb 02 23:16:14 GMT 2011view raw

  1. load libtclsqlite3.so
  2.  
  3. set drop 1
  4.  
  5. sqlite3 db /tmp/tdb2.sq3
  6.  
  7. if {$drop} {
  8. catch { db eval {drop table props} }
  9. catch { db eval {drop table obj} }
  10. }
  11. catch {
  12. db eval {create table obj (id integer primary key, name text)}
  13. db eval {create table props (id integer references obj, pnam text, pval text)}
  14. db eval {create index pind on props (pnam, pval)}
  15.  
  16. db transaction {
  17. for {set i 0} {$i < 2500} {incr i} {
  18. db eval {insert into obj values ($i,'object ' || $i)}
  19. db eval {insert into props values ($i, 'd', $i/50)}
  20. db eval {insert into props values ($i, 'd5', $i/500)}
  21. db eval {insert into props values ($i, 'm', $i%50)}
  22. db eval {analyze}
  23. }
  24. }
  25. }
  26.  
  27. set s {select obj.name
  28. from obj, props p1, props p2
  29. where obj.id = p1.id and p2.id = p1.id
  30. and p1.pnam = 'd' and p1.pval = '1'
  31. and p2.pnam = 'm' and p2.pval = '1'
  32. }
  33.  
  34. set s2 {select obj.name
  35. from obj, props p1
  36. where obj.id = p1.id
  37. and p1.pnam = 'd' and p1.pval = '1'
  38. and obj.id in (select id from props where pnam = 'm' and pval = '1')
  39. }
  40.  
  41.  
  42. set sx {select obj.name
  43. from obj, props p1, props p2
  44. where p1.id = obj.id and p2.id = obj.id
  45. and p1.pnam = 'd5' and p1.pval = '0'
  46. and p2.pnam = 'm' and p2.pval = '1'
  47. order by obj.id desc
  48. limit 1
  49. }
  50.  
  51. set sx2 {select obj.name
  52. from obj, props p1
  53. where p1.id = obj.id
  54. and p1.pnam = 'd5' and p1.pval = '0'
  55. and obj.id in (select id from props where pnam = 'm' and pval = '1')
  56. order by obj.id desc
  57. limit 1
  58. }
  59.  
  60. set sx3 {select obj.name
  61. from obj, props p1
  62. where p1.id = obj.id
  63. and p1.pnam = 'm' and p1.pval = '1'
  64. and obj.id in (select id from props where pnam = 'd5' and pval = '0')
  65. order by obj.id desc
  66. limit 1
  67. }
  68.  
  69. proc timeit {s} {
  70. puts [db eval $s]
  71. puts [time {db eval $s} 100]
  72. }
  73.  
  74. timeit $s
  75. timeit $s2
  76. timeit $sx
  77. timeit $sx2
  78. timeit $sx3
  79.  
  80. package require Pgtcl
  81.  
  82. set pdb [pg_connect -conninfo {dbname = test}]
  83. if {$drop} {
  84. catch {pg_execute $pdb {drop table props}}
  85. catch {pg_execute $pdb {drop table obj}}
  86. }
  87. catch {
  88. pg_execute $pdb {create table obj (id integer primary key, name text)}
  89. pg_execute $pdb {create table props (id integer references obj, pnam text, pval text)}
  90. pg_execute $pdb {create index pind on props (pnam, pval)}
  91. pg_execute $pdb begin
  92. for {set i 0} {$i < 2500} {incr i} {
  93. pg_execute $pdb "insert into obj values ($i,'object ' || $i)"
  94. pg_execute $pdb "insert into props values ($i, 'd', $i/50)"
  95. pg_execute $pdb "insert into props values ($i, 'd5', $i/500)"
  96. pg_execute $pdb "insert into props values ($i, 'm', $i%50)"
  97. }
  98. pg_execute $pdb commit
  99. pg_execute $pdb "vacuum analyze props"
  100. pg_execute $pdb "vacuum analyze obj"
  101. }
  102.  
  103. puts "\nPostgres"
  104. proc timeit {s} {
  105. upvar pdb pdb
  106. pg_execute $pdb $s {puts $name}
  107. puts [time {pg_execute $pdb $s} 100]
  108. }
  109.  
  110. timeit $s
  111. timeit $s2
  112. timeit $sx
  113. timeit $sx2
  114. timeit $sx3
  115.