BIND 10 #1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)

[BIND 10 Development]

#1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)
-------------------------------------+-------------------------------------
                   Reporter:         |                 Owner:
  jinmei                             |                Status:  new
                       Type:         |             Milestone:
  defect                             |  Sprint-20120925
                   Priority:         |            Resolution:
  medium                             |             Sensitive:  0
                  Component:  data   |           Sub-Project:  DNS
  source                             |  Estimated Difficulty:  6
                   Keywords:         |           Total Hours:  0
            Defect Severity:         |
  Medium                             |
Feature Depending on Ticket:         |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------

Comment (by muks):

 With 300000 records each in `records` and `nsec3` tables:

 {{{

 [muks at jurassic jreed]$ time (echo "SELECT rdtype, ttl, sigtype, rdata,
 name, rname FROM records WHERE zone_id = 5 UNION SELECT rdtype, ttl,
 'NSEC3', rdata, owner, owner FROM nsec3 WHERE zone_id = 5 ORDER by rname,
 rdtype;" | sqlite3 tmp2.sqlite3 > /dev/null)

 real    0m15.763s
 user    0m7.698s
 sys     0m7.982s
 }}}

 Add the relevant keys and change query style:

 {{{
 [muks at jurassic jreed]$ time (echo "SELECT rdtype, ttl, sigtype, rdata,
 name, rname FROM (SELECT *, '' as hash, '' as owner FROM records WHERE
 zone_id=5 UNION SELECT *, owner as name, owner as rname, '' as sigtype
 FROM nsec3 WHERE zone_id=5 ORDER by rname, rdtype);" | sqlite3
 tmp2.sqlite3 > /dev/null)

 real    0m9.565s
 user    0m6.766s
 sys     0m2.772s
 }}}

-- 
Ticket URL: <http://bind10.isc.org/ticket/1899#comment:7>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development