Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Release 3.25.0 adds support for window functions

sqlite.org

Post reply on HN
Print view View original

101–110 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#101

Post by yread »

bobwaycott wrote: post #70
electroly wrote: post #68

Earlier quoted context omitted.

Having written a fairly significant amount of SQLite virtual table module code, I think it isn't quite sophisticated enough to be useful. I regret building my application atop that virtual table functionality. Many parts of queries that are vital for efficient execution are not pushed down to the virtual table provider; for instance there is no way for "SELECT COUNT(*) FROM table" to do anything but retrieve every fi…

Out of curiosity, why would you ever run a SELECT COUNT(*)? What use case demands that versus, say, SELECT COUNT(col) where col is the name of a column in the table?

eh? if you want to know the number of rows in a table select count(*) is the idiomatic way

Re: SQLite Release 3.25.0 adds support for window functions

#102

Post by ddebernardy »

peatmoss wrote: post #50
ddebernardy wrote: post #18

Earlier quoted context omitted.

SQLite competes with fopen; not SQL. [1] It's great for embedded systems and small single-user apps. It's not when your data doesn't fit in memory. [1]: https://www.sqlite.org/whentouse.html

Many analytics use cases are single user. I’ve often thought you could do worse than SQLite as a first pass at a dataframe implementation. And SQLite is a great fit for a range of analyses on a single-user computer where you’re looking to sample from or calculate aggregations from data that fits on harddisk but not in RAM. Now, where SQLite starts to fall down in analytics workloads is that it’s row-oriented rather t…

I dunno... In my neck of the woods, datasets larger than anything a hard drive will store are commonplace, making SQLite a non-starter. I can imagine SQLite being useful for prototyping or for toy apps or for one-offs with small datasets, but not much else...

(Edit to clarify: I think it's awesome for embedded apps. But it never struck me as a good choice for data science.)

Re: SQLite Release 3.25.0 adds support for window functions

#103

Post by peatmoss »

ddebernardy wrote: post #102
peatmoss wrote: post #50

Earlier quoted context omitted.

Many analytics use cases are single user. I’ve often thought you could do worse than SQLite as a first pass at a dataframe implementation. And SQLite is a great fit for a range of analyses on a single-user computer where you’re looking to sample from or calculate aggregations from data that fits on harddisk but not in RAM. Now, where SQLite starts to fall down in analytics workloads is that it’s row-oriented rather t…

I dunno... In my neck of the woods, datasets larger than anything a hard drive will store are commonplace, making SQLite a non-starter. I can imagine SQLite being useful for prototyping or for toy apps or for one-offs with small datasets, but not much else... (Edit to clarify: I think it's awesome for embedded apps. But it never struck me as a good choice for data science.)

Cool, but in between data that fits in RAM and data that requires exotic storage and computing surely you can at least imagine data of this mid-sized variety.

For that, SQLite isn’t necessarily bad.

Re: SQLite Release 3.25.0 adds support for window functions

#104

Post by bobwaycott »

yread wrote: post #101
bobwaycott wrote: post #70

Earlier quoted context omitted.

Out of curiosity, why would you ever run a SELECT COUNT(*)? What use case demands that versus, say, SELECT COUNT(col) where col is the name of a column in the table?

eh? if you want to know the number of rows in a table select count(*) is the idiomatic way

No. That’s the way one may see the first time they’re learning what a SELECT statement is, but it’s most certainly not what you’d ever do in anything near production code.

The parent made this statement:

> ... for instance there is no way for `SELECT COUNT(star) FROM table` to do anything but retrieve every field of every row and return them to the SQLite engine.”

The only response here is, “Of course! That’s what you told it to do! Why would it do anything else—and why would you ever tell it to do that?!”

Edit: To be clear and not sound as though I’m just being dismissive, you can of course use `COUNT(star)`, but the parent made it sound like this was something non-trivial. There are better ways to write such a query when you care about the exact count of rows and, assuming you can’t depend on grabbing the max primary key due to deletions, you’re complaining about the database wasting cycles pulling back irrelevant info. Get to know your database, as it often has features to query such metadata in a much smarter and more performant way.

PS: I replaced the asterisk with the word “star” because it’s making the formatting of this comment atrocious. Apologies for all the edits. I give up now.

Re: SQLite Release 3.25.0 adds support for window functions

#105

Post by yread »

siscia wrote: post #72
yread wrote: post #27

A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite or something else?

Shameless plug since I am the author, but you could be interested in https://redisql.com/ Basically an Redis module that embed SQLite, I offer replication on the PRO version, if you want to try it out you can download the trial version for free. If the trial version is not enough, send me an email and we could work something out ;)

Just a heads up, you should probably have someone proof read your copy. There is a typo in every paragraph.

Re: SQLite Release 3.25.0 adds support for window functions

#106

Post by cryptonector »

SQLite wrote: post #75
cryptonector wrote: post #64

Earlier quoted context omitted.

The "lite" refers to things like: - it only has b*-tree indexes - it only has one index per-table source

> it only has one index per-table source I don't know for sure what this means, but it sounds like it is incorrect.

I could swear I've seen you say this. Maybe I'm mis-remembering? ISTR it was that for each table source in a query SQLite3 uses just one index (or the table itself) for indexing or scanning to find relevant rows in that table source.

Re: SQLite Release 3.25.0 adds support for window functions

#107

Post by SQLite »

cryptonector wrote: post #106
SQLite wrote: post #75

Earlier quoted context omitted.

> it only has one index per-table source I don't know for sure what this means, but it sounds like it is incorrect.

I could swear I've seen you say this. Maybe I'm mis-remembering? ISTR it was that for each table source in a query SQLite3 uses just one index (or the table itself) for indexing or scanning to find relevant rows in that table source.

SQLite can use multiple indexes if there are OR terms in the WHERE clause.

SQLite tries to only uses indexes in situations where they help the query run faster. SQLite is not limited in its use of indexes. It is just that the use of multiple indexes for a single FROM-clause term is rarely helpful.

Re: SQLite Release 3.25.0 adds support for window functions

#108

Post by cryptonector »

SQLite wrote: post #107
cryptonector wrote: post #106

Earlier quoted context omitted.

I could swear I've seen you say this. Maybe I'm mis-remembering? ISTR it was that for each table source in a query SQLite3 uses just one index (or the table itself) for indexing or scanning to find relevant rows in that table source.

SQLite can use multiple indexes if there are OR terms in the WHERE clause. SQLite tries to only uses indexes in situations where they help the query run faster. SQLite is not limited in its use of indexes. It is just that the use of multiple indexes for a single FROM-clause term is rarely helpful.

Ah, OK, thanks!

Re: SQLite Release 3.25.0 adds support for window functions

#109

Post by siscia »

yread wrote: post #105
siscia wrote: post #72

Earlier quoted context omitted.

Shameless plug since I am the author, but you could be interested in https://redisql.com/ Basically an Redis module that embed SQLite, I offer replication on the PRO version, if you want to try it out you can download the trial version for free. If the trial version is not enough, send me an email and we could work something out ;)

Just a heads up, you should probably have someone proof read your copy. There is a typo in every paragraph.

Thanks I will proceed soon!

Re: SQLite Release 3.25.0 adds support for window functions

#110

Post by electroly »

bobwaycott wrote: post #70
electroly wrote: post #68

Earlier quoted context omitted.

Having written a fairly significant amount of SQLite virtual table module code, I think it isn't quite sophisticated enough to be useful. I regret building my application atop that virtual table functionality. Many parts of queries that are vital for efficient execution are not pushed down to the virtual table provider; for instance there is no way for "SELECT COUNT(*) FROM table" to do anything but retrieve every fi…

Out of curiosity, why would you ever run a SELECT COUNT(*)? What use case demands that versus, say, SELECT COUNT(col) where col is the name of a column in the table?

That was just a simplified example. "SELECT COUNT(col) FROM ..." has the same problem: from the virtual table provider's point of view, the query you submitted is "SELECT col FROM ...". There's no way for it to avoid retrieving the data even if it has a faster way just to count it. This is just one small example -- the virtual table interface is just too simplistic to support a lot of real-life scenarios.

Another good example is joins -- they aren't pushed down, and SQLite can only do joins as nested loops. All you can do is stream raw data into the SQLite engine and allow it to perform the join, even if you can do it more efficiently yourself.

In my case I was passing queries to another database engine, so every single thing that could be pushed down to the virtual table provider, I would have been able to take advantage of. But not enough stuff is.

Post reply on HN