BIND 10 #2610: Query for large zone in SQLite datasrc takes a very long time

[BIND 10 Development]

#2610: Query for large zone in SQLite datasrc takes a very long time
-------------------------------------+-------------------------------------
            Reporter:  vorner        |                        Owner:
                Type:  defect        |                       Status:  new
            Priority:  medium        |                    Milestone:  Next-
           Component:  data source   |  Sprint-Proposed
            Keywords:                |                   Resolution:
           Sensitive:  0             |                 CVSS Scoring:
         Sub-Project:  DNS           |              Defect Severity:
Estimated Difficulty:  0             |  Medium
         Total Hours:  0             |  Feature Depending on Ticket:
                                     |          Add Hours to Ticket:  0
                                     |                    Internal?:  0
-------------------------------------+-------------------------------------

Comment (by jinmei):

 Replying to [comment:10 vorner]:

 > > I can't find these in the source code.
 >
 > They are not in the code. I just temporary added them locally, into the
 > findNoNameResult method (one at the very beginning, the other before the
 > findWildcardMatch call in there).

 Okay, then I suspect the most likely bottleneck is "hasSubdomains()",
 which requires this SQL query:

 {{{
     // ANY_SUB:
     // This query returns records in the specified zone for the domain
     // matching the passed name, and its sub-domains.
     "SELECT rdtype, ttl, sigtype, rdata "
         "FROM records WHERE zone_id=?1 AND rname LIKE ?2",
 }}}

 So I'd try this manually:
 {{{
 sqlite> SELECT rdtype, ttl, sigtype, rdata FROM records WHERE zone_id=xx
 AND rname LIKE 'cz.nic.ns.c.%'
 sqlite> explain query plan SELECT rdtype, ttl, sigtype, rdata FROM records
 WHERE zone_id=XX AND rname LIKE 'cz.nic.ns.c.%'
 }}}
 (replace 'XX' with the actual zone ID in the table)

 and, if this works fast, add logs before and after sqlite3_step here:
 {{{#!cpp
     bool getNext(std::string (&data)[COLUMN_COUNT]) {
         // If there's another row, get it
         // If finalize has been called (e.g. when previous getNext() got
         // SQLITE_DONE), directly return false
         while (statement_ != NULL) {
             rc_ = sqlite3_step(statement_);
 }}}
 to see how much it takes in the C++ code.

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