Close Menu
    Check BMI
    Technology

    Many Small Queries Are Efficient in SQLite

    TechAiVerseBy No Comments14 Mins Read0 Views

    Many Small Queries Are Efficient in SQLite

    Many Small Queries Are Efficient In SQLite

    1. Executive Summary

    • 200 SQL statements per webpage is excessive for client/server database
      engines like MySQL, PostgreSQL, or SQL Server.

    • But with SQLite, 200 or more SQL statement per webpage is not a problem.

    • SQLite can also do large and complex queries efficiently, just like
      client/server databases. But SQLite can do many smaller queries
      efficiently too. Application developers can use whichever technique
      works best for the task at hand.

    2. The Perceived Problem

    The Appropriate Uses For SQLite page says that
    dynamic pages on the SQLite website typically do about 200 SQL
    statements each.
    This has provoked criticism from readers. Examples:

    • “200 SQL statements is a ridiculously high number for a single page”

    • “For most sites, 200 queries is way, way, way too much.”

    • “[This is] bad design”

    Such criticism would be well-founded for a traditional client/server
    database engine, such as MySQL, PostgreSQL, or SQL Server. In
    a client/server database, each SQL statement requires a message
    round-trip from the application to the database server and back to
    the application. Doing over 200 round-trip messages, sequentially,
    can be a serious performance drag. This is sometimes called the
    “N+1 Query Problem” or the “N+1 Select Problem” and it is an anti-pattern.

    3. N+1 Queries Are Not A Problem With SQLite

    SQLite is not client/server, however. The SQLite database runs
    in the same process address space as the application. Queries do not
    involve message round-trips, only a function call. The latency
    of a single SQL query is far less in SQLite. Hence, using a large number
    of queries with SQLite is not the problem.

    4. The Need For Over 200 SQL Statements Per Webpage

    The dynamic webpages on the SQLite website are mostly generated by
    the Fossil version control system.
    A typical dynamic
    page would be a timeline such as https://sqlite.org/src/timeline.
    A log of all SQL used by the timeline is shown below.

    The first group of queries in the log are extracting display options
    from the “config” and “global_config” tables of the Fossil database.
    Then there is a single complex query that extracts a list of all elements
    to be displayed on the timeline.
    This “timeline” query demonstrates that SQLite can easily process complex
    relational database queries involving multiple tables, subqueries, and
    complex WHERE clause constraints, and it can make effective use of indexes
    to solve the queries with minimal disk I/O.

    Following the single big “timeline” query,
    there are additional queries for each timeline element.
    Fossil is using the “N+1 Query” pattern rather than trying
    to grab all the information in as few queries as possible.
    But that is ok because there is no unnecessary IPC overhead.
    At the bottom of
    each timeline page, Fossil shows approximately how long it took to generate
    the page. For a 50-entry timeline, the latency is usually less than
    25 milliseconds. Profiling shows that few of those milliseconds
    were spent inside the database engine.

    Using the N+1 Query pattern in Fossil does not harm the application.
    But the N+1 Query pattern does have benefits. For one, the
    section of the code that creates the timeline query can be
    completely separate from the section that prepares each timeline
    entry for display.
    This provides a separation of responsibility that helps keep the code
    simple and easy to maintain. Secondly, the information
    needed for display, and the queries needed to extract that information,
    vary according to what type of objects are to be shown. Check-ins need one
    set of queries. Tickets need another set of queries. Wiki pages need a
    different query. And so forth. By implementing these queries on-demand
    and in the part of the code dealing with the various entities, there is
    further separation of responsibility and simplification of the overall
    code base.

    So, SQLite is able to do one or two large and complex queries, or it can
    do many smaller and simpler queries. Both are efficient. An application
    can use either or both techniques, depending on what works best for the
    situation at hand.

    The following is a log of all SQL used to generate one particular
    timeline (captured on 2016-09-16):

    -- sqlite3_open: /home/drh/sqlite/sqlite/.fslckout
PRAGMA foreign_keys=OFF;
SELECT sql FROM localdb.sqlite_schema WHERE name=='vfile';
-- sqlite3_open: /home/drh/.fossil
PRAGMA foreign_keys=OFF;
SELECT value FROM vvar WHERE name='repository';
ATTACH DATABASE '/home/drh/www/repos/sqlite.fossil' AS 'repository' KEY '';
SELECT value FROM config WHERE name='allow-symlinks';
SELECT value FROM global_config WHERE name='allow-symlinks';
SELECT value FROM config WHERE name='aux-schema';
SELECT 1 FROM config WHERE name='baseurl:http://';
SELECT value FROM config WHERE name='ip-prefix-terms';
SELECT value FROM global_config WHERE name='ip-prefix-terms';
SELECT value FROM config WHERE name='localauth';
SELECT value FROM vvar WHERE name='default-user';
SELECT uid FROM user WHERE cap LIKE '%s%';
SELECT login FROM user WHERE uid=1;
SELECT cap FROM user WHERE login = 'nobody';
SELECT cap FROM user WHERE login = 'anonymous';
SELECT value FROM config WHERE name='public-pages';
SELECT value FROM global_config WHERE name='public-pages';
SELECT value FROM config WHERE name='header';
SELECT value FROM config WHERE name='project-name';
SELECT value FROM config WHERE name='th1-setup';
SELECT value FROM global_config WHERE name='th1-setup';
SELECT value FROM config WHERE name='redirect-to-https';
SELECT value FROM global_config WHERE name='redirect-to-https';
SELECT value FROM config WHERE name='index-page';
SELECT mtime FROM config WHERE name='css';
SELECT mtime FROM config WHERE name='logo-image';
SELECT mtime FROM config WHERE name='background-image';
CREATE TEMP TABLE IF NOT EXISTS timeline(
  rid INTEGER PRIMARY KEY,
  uuid TEXT,
  timestamp TEXT,
  comment TEXT,
  user TEXT,
  isleaf BOOLEAN,
  bgcolor TEXT,
  etype TEXT,
  taglist TEXT,
  tagid INTEGER,
  short TEXT,
  sortby REAL
)
;
INSERT OR IGNORE INTO timeline SELECT
  blob.rid AS blobRid,
  uuid AS uuid,
  datetime(event.mtime,toLocal()) AS timestamp,
  coalesce(ecomment, comment) AS comment,
  coalesce(euser, user) AS user,
  blob.rid IN leaf AS leaf,
  bgcolor AS bgColor,
  event.type AS eventType,
  (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref
    WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid
      AND tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags,
  tagid AS tagid,
  brief AS brief,
  event.mtime AS mtime
 FROM event CROSS JOIN blob
WHERE blob.rid=event.objid
 AND NOT EXISTS(SELECT 1 FROM tagxref WHERE tagid=5 AND tagtype>0 AND rid=blob.rid)
 ORDER BY event.mtime DESC LIMIT 50;
-- SELECT value FROM config WHERE name='timeline-utc';
SELECT count(*) FROM timeline WHERE etype!='div';
SELECT min(timestamp) FROM timeline;
SELECT julianday('2016-09-15 14:54:51',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime<=2457647.121412037);
SELECT max(timestamp) FROM timeline;
SELECT julianday('2016-09-24 17:42:43',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime>=2457656.238009259);
SELECT value FROM config WHERE name='search-ci';
SELECT value FROM vvar WHERE name='checkout';
SELECT value FROM config WHERE name='timeline-max-comment';
SELECT value FROM global_config WHERE name='timeline-max-comment';
SELECT value FROM config WHERE name='timeline-date-format';
SELECT value FROM config WHERE name='timeline-truncate-at-blank';
SELECT value FROM global_config WHERE name='timeline-truncate-at-blank';
SELECT * FROM timeline ORDER BY sortby DESC;
SELECT value FROM config WHERE name='hash-digits';
SELECT value FROM global_config WHERE name='hash-digits';
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68028;
SELECT pid FROM plink WHERE cid=68028 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68028 AND tagid=9 AND tagtype>0;
SELECT value FROM config WHERE name='timeline-block-markup';
SELECT value FROM config WHERE name='timeline-plaintext';
SELECT value FROM config WHERE name='wiki-use-html';
SELECT value FROM global_config WHERE name='wiki-use-html';
SELECT 1 FROM private WHERE rid=68028;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68026;
SELECT pid FROM plink WHERE cid=68026 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68026;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68024;
SELECT pid FROM plink WHERE cid=68024 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68024;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68018;
SELECT pid FROM plink WHERE cid=68018 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68018;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68012;
SELECT pid FROM plink WHERE cid=68012 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68012;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68011;
SELECT value FROM config WHERE name='details';
SELECT pid FROM plink WHERE cid=68011 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68011 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=68011;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68008;
SELECT pid FROM plink WHERE cid=68008 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68008;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68006;
SELECT pid FROM plink WHERE cid=68006 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68006;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68000;
SELECT pid FROM plink WHERE cid=68000 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68000;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67997;
SELECT pid FROM plink WHERE cid=67997 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67997;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67992;
SELECT pid FROM plink WHERE cid=67992 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67992;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67990;
SELECT pid FROM plink WHERE cid=67990 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67990;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67989;
SELECT pid FROM plink WHERE cid=67989 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67989;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67984;
SELECT pid FROM plink WHERE cid=67984 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67984;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67983;
SELECT pid FROM plink WHERE cid=67983 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67983;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67979;
SELECT pid FROM plink WHERE cid=67979 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67979;
SELECT value FROM config WHERE name='ticket-closed-expr';
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67980;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67977;
SELECT pid FROM plink WHERE cid=67977 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67977;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67974;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67971;
SELECT pid FROM plink WHERE cid=67971 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67971;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67972;
SELECT pid FROM plink WHERE cid=67972 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67972;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67969;
SELECT pid FROM plink WHERE cid=67969 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67969;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67966;
SELECT pid FROM plink WHERE cid=67966 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67966;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67962;
SELECT pid FROM plink WHERE cid=67962 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67962;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67960;
SELECT pid FROM plink WHERE cid=67960 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67960;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67957;
SELECT pid FROM plink WHERE cid=67957 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67957;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67955;
SELECT pid FROM plink WHERE cid=67955 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67955;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67953;
SELECT pid FROM plink WHERE cid=67953 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='5990a1bdb4a073' AND tkt_uuid<'5990a1bdb4a074';
SELECT 1 FROM blob WHERE uuid>='5990a1bdb4a073' AND uuid<'5990a1bdb4a074';
SELECT 1 FROM private WHERE rid=67953;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67941;
SELECT pid FROM plink WHERE cid=67941 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67941;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67940;
SELECT pid FROM plink WHERE cid=67940 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67940;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67938;
SELECT pid FROM plink WHERE cid=67938 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67938;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67935;
SELECT pid FROM plink WHERE cid=67935 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67935;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67934;
SELECT pid FROM plink WHERE cid=67934 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67934;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67932;
SELECT pid FROM plink WHERE cid=67932 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67932;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67930;
SELECT pid FROM plink WHERE cid=67930 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67930;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67928;
SELECT pid FROM plink WHERE cid=67928 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=67928 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=67928;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67919;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='01874d252ac44861' AND tkt_uuid<'01874d252ac44862';
SELECT 1 FROM blob WHERE uuid>='01874d252ac44861' AND uuid<'01874d252ac44862';
SELECT 1 FROM private WHERE rid=67918;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67916;
SELECT pid FROM plink WHERE cid=67916 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac759' AND tkt_uuid<'0eab1ac75:';
SELECT 1 FROM private WHERE rid=67916;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='a49bc0a8244feb08' AND tkt_uuid<'a49bc0a8244feb09';
SELECT 1 FROM blob WHERE uuid>='a49bc0a8244feb08' AND uuid<'a49bc0a8244feb09';
SELECT 1 FROM private WHERE rid=67914;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67913;
SELECT pid FROM plink WHERE cid=67913 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f' AND tkt_uuid<'0eab1ac7591g';
SELECT 1 FROM private WHERE rid=67913;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67911;
SELECT pid FROM plink WHERE cid=67911 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67911;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67909;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67907;
SELECT pid FROM plink WHERE cid=67907 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67907;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67899;
SELECT pid FROM plink WHERE cid=67899 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67899;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67897;
SELECT pid FROM plink WHERE cid=67897 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67897;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67895;
SELECT pid FROM plink WHERE cid=67895 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67895;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67893;
SELECT pid FROM plink WHERE cid=67893 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67893;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67891;
SELECT pid FROM plink WHERE cid=67891 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67891;
SELECT count(*) FROM plink
 WHERE pid=67928 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68011 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68028 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT value FROM config WHERE name='show-version-diffs';
SELECT value FROM config WHERE name='adunit-omit-if-admin';
SELECT value FROM global_config WHERE name='adunit-omit-if-admin';
SELECT value FROM config WHERE name='adunit-omit-if-user';
SELECT value FROM global_config WHERE name='adunit-omit-if-user';
SELECT value FROM config WHERE name='adunit';
SELECT value FROM global_config WHERE name='adunit';
SELECT value FROM config WHERE name='auto-hyperlink-delay';
SELECT value FROM global_config WHERE name='auto-hyperlink-delay';
SELECT value FROM config WHERE name='footer';
PRAGMA database_list;
PRAGMA database_list;
PRAGMA localdb.freelist_count;
PRAGMA localdb.page_count;

    This page was last updated on 2025-05-31 13:08:22Z

    Share.

    Jonathan is a tech enthusiast and the mind behind Tech AI Verse. With a passion for artificial intelligence, consumer tech, and emerging innovations, he deliver clear, insightful content to keep readers informed. From cutting-edge gadgets to AI advancements and cryptocurrency trends, Jonathan breaks down complex topics to make technology accessible to all.

    Related Posts

    Leave A Reply