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:12 vorner]:

 > It doesn't seem to use the index much (it probably does something, since
 it
 > says it'd scan 50k rows, but the table contains 1911305 rows in the
 zone).

 > Looking at the sqlite3 webpage, it should use the index. But I don't
 know why
 > it doesn't.

 In my experiment it should use an index.  I don't know why it doesn't
 happen your setup, but I'd primarily check the schema and sqlite3
 version.

 > Anyway, we don't really need to know if the result is NXRRSET or
 NXDOMAIN in
 > this case, since these are the additional records and the result would
 be the
 > same. Should we add a flag to disable that kind of check with
 subdomains? We
 > probably still need it for the general case, though, so we should make
 the
 > query faster somehow too.

 There could be some cases where we can skip some of the processing,
 but I suspect we'll still hit the unacceptable bottleneck while
 checking wildcards (which cannot be omitted for glue records too).
 So, such micro optimization wouldn't be a solution for this ticket.

 My suggestion is: first figure out why the index doesn't work for you;
 if it cannot be resolved by upgrading either/both schema/sqlite3 or
 by some operational fix, I think we have to just give up saying
 "basically sqlite3 data source isn't expected to work for a very large
 zone".

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