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://www.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 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 last modified on 2020-06-18 21:18:56 UTC

+ -- cgit v1.2.3