Optimize all those calls to index_list and foreign_key_list

[simonw]

On the first hit to a restarted index I'm seeing this in the SQL traces: https://latest-with-plugins.datasette.io/github/commits?_trace=1

I imagine this could be sped up a lot using tricks like this one from the SQLite documentation: https://sqlite.org/pragma.html#pragfunc

SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
  FROM sqlite_schema AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table'
 ORDER BY 1;

https://latest-with-plugins.datasette.io/fixtures?sql=SELECT+DISTINCT+m.name+%7C%7C+%27.%27+%7C%7C+ii.name+AS+%27indexed-columns%27%0D%0A++FROM+sqlite_schema+AS+m%2C%0D%0A+++++++pragma_index_list%28m.name%29+AS+il%2C%0D%0A+++++++pragma_index_info%28il.name%29+AS+ii%0D%0A+WHERE+m.type%3D%27table%27%0D%0A+ORDER+BY+1%3B

[simonw]

I think all of these queries happen in one place - in the populate_schema_tables() function - so optimizing them might be localized to just that area of the code, which would be nice:

datasette/datasette/utils/internal_db.py

Lines 97 to 183 in c00f29a

	async def populate_schema_tables(internal_db, db):
	database_name = db.name
	await internal_db.execute_write(
	"DELETE FROM tables WHERE database_name = ?", [database_name], block=True
	)
	tables = (await db.execute("select * from sqlite_master WHERE type = 'table'")).rows
	for table in tables:
	table_name = table["name"]
	await internal_db.execute_write(
	"""
	INSERT INTO tables (database_name, table_name, rootpage, sql)
	values (?, ?, ?, ?)
	""",
	[database_name, table_name, table["rootpage"], table["sql"]],
	block=True,
	)
	# And the columns
	await internal_db.execute_write(
	"DELETE FROM columns WHERE database_name = ? and table_name = ?",
	[database_name, table_name],
	block=True,
	)
	columns = await db.table_column_details(table_name)
	for column in columns:
	params = {
	**{"database_name": database_name, "table_name": table_name},
	**column._asdict(),
	}
	await internal_db.execute_write(
	"""
	INSERT INTO columns (
	database_name, table_name, cid, name, type, "notnull", default_value, is_pk, hidden
	) VALUES (
	:database_name, :table_name, :cid, :name, :type, :notnull, :default_value, :is_pk, :hidden
	)
	""",
	params,
	block=True,
	)
	# And the foreign_keys
	await internal_db.execute_write(
	"DELETE FROM foreign_keys WHERE database_name = ? and table_name = ?",
	[database_name, table_name],
	block=True,
	)
	foreign_keys = (
	await db.execute(f"PRAGMA foreign_key_list([{table_name}])")
	).rows
	for foreign_key in foreign_keys:
	params = {
	**{"database_name": database_name, "table_name": table_name},
	**dict(foreign_key),
	}
	await internal_db.execute_write(
	"""
	INSERT INTO foreign_keys (
	database_name, table_name, "id", seq, "table", "from", "to", on_update, on_delete, match
	) VALUES (
	:database_name, :table_name, :id, :seq, :table, :from, :to, :on_update, :on_delete, :match
	)
	""",
	params,
	block=True,
	)
	# And the indexes
	await internal_db.execute_write(
	"DELETE FROM indexes WHERE database_name = ? and table_name = ?",
	[database_name, table_name],
	block=True,
	)
	indexes = (await db.execute(f"PRAGMA index_list([{table_name}])")).rows
	for index in indexes:
	params = {
	**{"database_name": database_name, "table_name": table_name},
	**dict(index),
	}
	await internal_db.execute_write(
	"""
	INSERT INTO indexes (
	database_name, table_name, seq, name, "unique", origin, partial
	) VALUES (
	:database_name, :table_name, :seq, :name, :unique, :origin, :partial
	)
	""",
	params,
	block=True,
	)

[simonw]

I was thinking it might even be possible to convert this into a insert into tables select from ... query:

datasette/datasette/utils/internal_db.py

Lines 102 to 112 in c00f29a

	tables = (await db.execute("select * from sqlite_master WHERE type = 'table'")).rows
	for table in tables:
	table_name = table["name"]
	await internal_db.execute_write(
	"""
	INSERT INTO tables (database_name, table_name, rootpage, sql)
	values (?, ?, ?, ?)
	""",
	[database_name, table_name, table["rootpage"], table["sql"]],
	block=True,
	)

But the SELECT runs against a separate database from the INSERT INTO, so I would have to setup a cross-database connection for this which feels a little too complicated.

[simonw]

I wonder why the INSERT INTO queries don't show up in that ?trace=1 view?

[simonw]

Here's why - trace only applies to read, not write SQL operations:

datasette/datasette/database.py

Lines 209 to 211 in 7c8f8aa

	with trace("sql", database=self.name, sql=sql.strip(), params=params):
	results = await self.execute_fn(sql_operation_in_thread)
	return results

[simonw]

Maybe it would be worth exploring attaching each DB in turn to the _internal connection in order to perform these queries faster.

I'm a bit worried about leaks though: the internal database isn't meant to be visible, even temporarily attaching another DB to it could cause SQL queries against that DB to be able to access the internal data.

So maybe instead the _internal connection gets to connect to the other DBs? There's a maximum of ten there I think, which is good for most but not all cases. But the cases with the most connected databases will see the worst performance!

[simonw]

Now that trace sees write queries (#1568) it's clear that there is a whole lot more DB activity then I had realized:

[simonw]

A simpler optimization would be just to turn all of those column and index reads into a single efficient UNION query against each database, then figure out the most efficient pattern to send them all as writes in one go as opposed to calling .execute_write() in a loop.

[simonw]

Also: running all of those CREATE TABLE IF NOT EXISTS in a single call to conn.executescript() rather than as separate queries may speed things up too.

[simonw]

One downside of conn.executescript() is that it won't be picked up by the tracing mechanism - in fact nothing that uses await db.execute_write_fn(fn, block=True) or await db.execute_fn(fn, block=True) gets picked up by tracing.

[simonw]

Idea: teach execute_write to accept an optional executescript=True parameter, like this:

diff --git a/datasette/database.py b/datasette/database.py
index 468e936..1a424f5 100644
--- a/datasette/database.py
+++ b/datasette/database.py
@@ -94,10 +94,14 @@ class Database:
             f"file:{self.path}{qs}", uri=True, check_same_thread=False
         )
 
-    async def execute_write(self, sql, params=None, block=False):
+    async def execute_write(self, sql, params=None, executescript=False, block=False):
+        assert not executescript and params, "Cannot use params with executescript=True"
         def _inner(conn):
             with conn:
-                return conn.execute(sql, params or [])
+                if executescript:
+                    return conn.executescript(sql)
+                else:
+                    return conn.execute(sql, params or [])
 
         with trace("sql", database=self.name, sql=sql.strip(), params=params):
             results = await self.execute_write_fn(_inner, block=block)

[simonw]

Using executescript=True that call now takes 1.89ms to create all of those tables.