6 ms·

Over high latency links this would be virtually unusable. Why not just download the entire database into memory over XHR on page load? SQLite databases of pure

Over high latency links this would be virtually unusable. Why not just download the entire database into memory over XHR on page load? SQLite databases of pure data usually aren’t over 10MB in size.

petters 5y ago
> SQLite databases of pure data usually aren’t over 10MB in size Why do you think this?
- deleted 5y ago
  [deleted]
mcculley 5y ago
That is hilariously wrong for a lot of use cases. I will find this very handy for some SQLite databases I have that are several GBs in size. I am looking right now at using this contribution.
- CyberRabbi 5y ago
  It’s not hilariously wrong or wrong at all that over high latency links this would be virtually unusable. It’s certainly possible that people are using SQLite databases with sizes on the order of gigabytes but in my experience those are the exception not the rule.
  - tehbeard 5y ago
    Over high latency links most anything interesting is virtually unusable, so stop using it as a high horse to stand upon.
simonw 5y ago
The demo here uses a 670MB database file.
formerly_proven 5y ago
This particular demo page actually makes queries against an almost 700 MB large (fills one CD!) SQLite database. Because the amount of data read is almost negligible (few hundred kB), performance is limited by latency (as you say). However, high-latency links also tend to be slower, so downloading the entire database a-priori would almost always be much slower. For example, on a 1 megabit/s link with 300 ms RTT, one example would take about 2 seconds for the data transfer itself while spending another 3 seconds or so on waiting. Downloading the entire file would take around an hour and a half. For your 10 MB database, transferring it as a whole would take 80 seconds. Assuming this solution instead needs to read e.g. 250 kB (taking 2 seconds to transfer), it could still bounce around 250 times to the database before those 10 MB are fully downloaded. (This would be a really odd query, since it would only read on average two pages per read request)
- CyberRabbi 5y ago
  Right but that is an artificially created demo by the author to justify the solution being presented (no offense). The question is how common are ~GB large SQLite databases in the real world relative to databases that are ~MB large? In my experience SQLite databases of millions of rows of raw tabular data tend to compress very well into dozens of megabytes. Indeed SQLite is often touted as a file format for applications.
  - dkarras 5y ago
    Even if you compress it, you need it in client memory which can also be a dealbreaker. Some people might need it for some projects, I know I did.
    - CyberRabbi 5y ago
      > you need it in client memory which can also be a dealbreaker Most workstations have GBs of available memory. If not you can dump it in indexeddb as a raw data store. I never disputed that it would be useful for some use cases. I only said it would be unusable with high latency links. If you have a low latency link and aren’t running very complex queries with lots of random seeks, then this should work fine for you.
detaro 5y ago
And the article starts by mentioning that you can download the entire file if it's not too large. And then goes on to present a solution for larger files. What more answer to "Why not just download the entire database" do you expect?
mythrwy 5y ago
I have been using SQLite databases for a few user application that has been running for close to a decade now. They are usually about 1.5GB. BTW, SQLite has a (theoretical?) max size of 140TB! (or so I've read)
- CyberRabbi 5y ago
  What do you think is the size of the average SQLite database?
  - mythrwy 5y ago
    Given their widespread uses on phones, the overall average is likely very small. KBs even. A more relevant question might be: what would the average size of SQLite databases for web type (or even this specific use case) applications. I don't know, but 10s or 100s of MBs might not be a bad guess.
    - _flux 5y ago
      However, would those 10s or 100s of MBs be something web sites would just like to share in their entirety to the client to query as they wish? At least many commercial services would prefer to keep most of the data secret and even gain insight from the access requests. But for a more open or a hobbyist project where minimizing amounts of data transfer is less important than minimizing the amount of doing more work (server-side code), then this seems like a decent solution. It is also worth reminding that this solution only practically works for read-only databases.

petters 5y ago

> SQLite databases of pure data usually aren’t over 10MB in size Why do you think this?

deleted 5y ago
[deleted]

mcculley 5y ago

That is hilariously wrong for a lot of use cases. I will find this very handy for some SQLite databases I have that are several GBs in size. I am looking right now at using this contribution.

CyberRabbi 5y ago
It’s not hilariously wrong or wrong at all that over high latency links this would be virtually unusable. It’s certainly possible that people are using SQLite databases with sizes on the order of gigabytes but in my experience those are the exception not the rule.
- tehbeard 5y ago
  Over high latency links most anything interesting is virtually unusable, so stop using it as a high horse to stand upon.

simonw 5y ago

The demo here uses a 670MB database file.

formerly_proven 5y ago

This particular demo page actually makes queries against an almost 700 MB large (fills one CD!) SQLite database. Because the amount of data read is almost negligible (few hundred kB), performance is limited by latency (as you say). However, high-latency links also tend to be slower, so downloading the entire database a-priori would almost always be much slower. For example, on a 1 megabit/s link with 300 ms RTT, one example would take about 2 seconds for the data transfer itself while spending another 3 seconds or so on waiting. Downloading the entire file would take around an hour and a half. For your 10 MB database, transferring it as a whole would take 80 seconds. Assuming this solution instead needs to read e.g. 250 kB (taking 2 seconds to transfer), it could still bounce around 250 times to the database before those 10 MB are fully downloaded. (This would be a really odd query, since it would only read on average two pages per read request)

CyberRabbi 5y ago
Right but that is an artificially created demo by the author to justify the solution being presented (no offense). The question is how common are ~GB large SQLite databases in the real world relative to databases that are ~MB large? In my experience SQLite databases of millions of rows of raw tabular data tend to compress very well into dozens of megabytes. Indeed SQLite is often touted as a file format for applications.
- dkarras 5y ago
  Even if you compress it, you need it in client memory which can also be a dealbreaker. Some people might need it for some projects, I know I did.
  - CyberRabbi 5y ago
    > you need it in client memory which can also be a dealbreaker Most workstations have GBs of available memory. If not you can dump it in indexeddb as a raw data store. I never disputed that it would be useful for some use cases. I only said it would be unusable with high latency links. If you have a low latency link and aren’t running very complex queries with lots of random seeks, then this should work fine for you.

detaro 5y ago

And the article starts by mentioning that you can download the entire file if it's not too large. And then goes on to present a solution for larger files. What more answer to "Why not just download the entire database" do you expect?

mythrwy 5y ago

I have been using SQLite databases for a few user application that has been running for close to a decade now. They are usually about 1.5GB. BTW, SQLite has a (theoretical?) max size of 140TB! (or so I've read)

CyberRabbi 5y ago
What do you think is the size of the average SQLite database?
- mythrwy 5y ago
  Given their widespread uses on phones, the overall average is likely very small. KBs even. A more relevant question might be: what would the average size of SQLite databases for web type (or even this specific use case) applications. I don't know, but 10s or 100s of MBs might not be a bad guess.
  - _flux 5y ago
    However, would those 10s or 100s of MBs be something web sites would just like to share in their entirety to the client to query as they wish? At least many commercial services would prefer to keep most of the data secret and even gain insight from the access requests. But for a more open or a hobbyist project where minimizing amounts of data transfer is less important than minimizing the amount of doing more work (server-side code), then this seems like a decent solution. It is also worth reminding that this solution only practically works for read-only databases.