[sqlite] Question about temp table performance

[K. Haley]

I have two versions of the same algorithm.  The first operates directly
on the main db table.  The second operates on a temp table containing
only the working set.  The problem is that the second version is about
20x slower, 1.5 sec versus 30 sec.  If the EXISTS line in the second
version is commented out the execution time drops to 9 sec.  Any ideas?


Version 1:
  sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent=null
WHERE group_id=?;",-1,&stmt,NULL);
  sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent="
              "( SELECT article.id FROM refs ,article "
              "WHERE refs.article_id=group_article.article_id "
              "AND reference=hash "
              "AND EXISTS (SELECT id FROM group_article WHERE
group_id=?1 AND article_id=article.id) "
              "ORDER BY refs.id DESC LIMIT 1 ) "
              "WHERE group_id=?1;",-1,&stmt,NULL);

Version 2:
  sq_res=sqlite3_prepare(db,"CREATE TEMP TABLE thrd(aid UNIQUE,
parent);",-1,&stmt,NULL);
  sq_res=sqlite3_prepare(db,"INSERT INTO thrd(aid) SELECT article_id
FROM group_article "
               "WHERE group_id=?;", -1, &stmt, NULL);
  sq_res=sqlite3_prepare(db,"UPDATE thrd SET parent="
              "( SELECT article.id FROM refs ,article "
              "WHERE refs.article_id=thrd.aid "
              "AND reference=hash "
              "AND EXISTS (SELECT aid FROM thrd WHERE aid=article.id) "
               "ORDER BY refs.id DESC LIMIT 1 ) "
               ";",-1,&stmt,NULL);
  sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent="
              "( SELECT parent FROM thrd "
                  "WHERE aid=article_id ) "
              "WHERE group_id=? ;", -1, &stmt, NULL);

signature.asc
Description: OpenPGP digital signature