coleifer/sqlite-web

Large results are slow

Closed this issue · 2 comments

shamrin commented

If the query result is large, I have to wait for the whole query to finish before I see anything.

In contrast, sqlite CLI streams such results to stdout. It is helpful because:

  • I can get a feeling how long did it take before the first result.
  • I don't have to wait for the whole query. With sqlite-web it could take minutes and feels wasteful, because the UI does not show results after a 1000 (see #84).

I would expect sqlite-web to do one of:

  1. stream results like CLI does
  2. output first 1000 without waiting for the rest and provide indication that it's not all
  3. output first 1000 without waiting for the rest and allow dynamic pagination on rows after 1000
coleifer commented

Yeah this would be a nice enhancement. We'd need to wrap the user query to add a LIMIT / OFFSET to support pagination, not sure if there are any ramifications there. Performance will largely depend on what indexes are available.

coleifer commented

Pagination and stuff is a little tricky since we have to use a POST (because some janky ass home automation software doesn't understand url-encoded newlines), but I think both the a) performance issue, and b) pagination should be resolved now. Please give it a try when you're able.