Posted to tcl by sebres at Fri Jul 29 15:46:06 GMT 2022view raw

  1. sqlite3 db :memory:
  2. db eval "CREATE TABLE pages ( type TEXT, id TEXT, content TEXT, PRIMARY KEY (type, id) ); CREATE TABLE pages_done ( type TEXT, id TEXT, PRIMARY KEY (type, id) );"
  3. set i 0; while {$i < 10000} {incr i; set t "type$i"; set c "content $i"; db eval {insert into pages values(:t, :i, :c)}; if {$i & 1} {db eval {insert into pages_done values(:t, :i)}}}
  4. puts -nonewline "Row counts: "; db eval {select (select count(*) from pages) pages, (select count(*) from pages_done) done} values {puts "pages: $values(pages), done: $values(done)"}
  5. puts "IN: [timerate {db eval {SELECT type, id, content FROM pages WHERE (type, id) IN (SELECT type, id FROM pages_done) LIMIT 4999,1}}]"
  6. puts "NOT IN: [timerate {db eval {SELECT type, id, content FROM pages WHERE (type, id) NOT IN (SELECT type, id FROM pages_done) LIMIT 4999,1}}]"
  7. puts "EXISTS: [timerate {db eval {SELECT type, id, content FROM pages p WHERE EXISTS (SELECT 1 FROM pages_done pd WHERE (pd.type, pd.id) = (p.type, p.id)) LIMIT 4999,1}}]"
  8. puts "NOT EXISTS: [timerate {db eval {SELECT type, id, content FROM pages p WHERE NOT EXISTS (SELECT 1 FROM pages_done pd WHERE (pd.type, pd.id) = (p.type, p.id)) LIMIT 4999,1}}]"
  9.  
  10. # results:
  11.  
  12. # Row counts: pages: 10000, done: 5000
  13. # IN: 1599.02 µs/# 626 # 625.38 #/sec 1000.988 net-ms
  14. # NOT IN: 935942.5 µs/# 2 # 1.068 #/sec 1871.885 net-ms
  15. # EXISTS: 2761.25 µs/# 363 # 362.15 #/sec 1002.335 net-ms
  16. # NOT EXISTS: 2754.40 µs/# 364 # 363.06 #/sec 1002.602 net-ms
  17.