D1 query taking 3-5 seconds to execute

Hi there, I’m getting very high execution times for relatively small read queries from my D1 DB, and I’m wondering if this is because of the alpha status or if there’s something I might be doing wrong.

The queries look something like this:

SELECT id, created_at, name FROM table WHERE some_param = ?1 AND is_archived = 0 ORDER BY created_at DESC LIMIT 100

For a table with about 30 rows locally (miniflare) this executes in under 2ms. For a deployed table with about 150 rows this systematically takes over 3000ms (data from the query’s meta return object). For example:

{
	"meta": {
		"duration": 5352.997754999436,
		"last_row_id": 0,
		"changes": 0,
		"served_by": "SinglePrimary-818950e0-6ef6-4340-a4b8-7b4fa0c04191.db3",
		"internal_stats": null
	},
	"results": {} // length: 37
}

The response payload is 11 KB so that should not be a problem I hope, and anyway LIMITing the returned rows further didn’t make it significantly faster.

If this is expected while D1 is in beta: good to know, I’ll wait/reconsider options if I need better perf fast (a rough roadmap would help make a decision here).

If this isn’t: I’d appreciate any pointers to understand what’s making the query take so long. Is there any way to troubleshoot execution time further?

Is this DB on D1’s new storage engine (default since July 2023) or one of the legacy DBs?

See Changelog · Cloudflare D1 docs for details on how to assess.

D1’s new storage engine is significantly faster (D1: We turned it up to 11) than the legacy engine it originally launched with.

4 Likes

Thank you, this might help, the DB is still under the legacy engine. I’ll confirm whether perf improves after I manage to migrate to the new engine (🐛 BUG: statement loo long when importing to D1 · Issue #4510 · cloudflare/workers-sdk · GitHub)

Alright, still haven’t managed to migrate, but with test data I can tell that the new backend seems to speed up the fastest of the slow queries by about 6x (from 3s to 0.5s). Planning on looking at indexing to optimize the query further. Thank you again!

Just too bad there isn’t a smoother way to migrate to the new backend that doesn’t involve manual exports and import bugs :see_no_evil:

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.