BIND 10 #324: improve SQL data source performance

[BIND 10 Development]

#324: improve SQL data source performance
-------------------------------------+-------------------------------------
                   Reporter:  each   |                 Owner:  UnAssigned
                       Type:         |                Status:  assigned
  enhancement                        |             Milestone:
                   Priority:  major  |  Sprint-20120306
                  Component:  data   |            Resolution:
  source                             |             Sensitive:  0
                   Keywords:         |           Sub-Project:  DNS
  performance                        |  Estimated Difficulty:  5
            Defect Severity:  N/A    |           Total Hours:  0
Feature Depending on Ticket:         |
  sqlite schema upgrade              |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------

Comment (by jinmei):

 I've taken a closer a look at it, and if I understand it correctly
 we cannot solve this problem just by tweaking indexes.  In my
 experiment the major bottleneck is this query:

 const char* const q_count_str = "SELECT COUNT(*) FROM records "
     "WHERE zone_id=?1 AND rname LIKE (?2 || '%');";

 I suspect due to the "LIKE" sqlite3 cannot perform efficient search
 using indices.

 The new version of sqlite3 datasource also uses a "LIKE" search to see
 if a wildcard match is the best one.

 So, I suspect that if we really want to solve this problem we should
 consider more fundamental restructure of the schema, e.g., storing
 each label in a separate column.

 In the newer version of implementation, one possible compromise would
 be to allow a zone to have an option of "no wildcard match" (and say
 it's admin's responsbility to ensure the zone has no wildcard).  In
 the new version there's no other place that requires select with
 "LIKE", and thanks to the cleaner code organiation it looks quite easy
 to implement this option.  (Another reason for switching to the new
 version sooner:-)

 For the purpose of this sprint, my suggestion is to give up handling
 it.  Maybe we can close it with 'wontfix' or just leave it open.

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