SQLite: Past, Present, and Future • Hacker News Board

Re: SQLite: Past, Present, and Future

#121

Post by okennedy » Tue, Sep 20, 2022, 12:25 PM UTC

oaiey wrote: post #90

After seeing one diagram: Why the hack are we still talking to databases with SQL strings and not directly specifying the Query-AST? Admins, sure (a fancy UI could help there as well) but why in our code?

We are... Spark's DataFrame is essentially a relational algebra AST-builder. Microsoft's LINQ interprets SQL directly in at compile-time. All of these, however, run queries more or less directly in the system in which they're specified.

It helps to think of SQL strings as an untrusted wire format. Yes, parsing is a pain, but it comes with two main benefits: (i) The wire format is human writable/interpretable, with all the accompanying benefits, and (ii) The wire format is easily extensible in a predictable way.

That latter one is particularly useful in keeping SQL's ecosystem open. Take a front-end library like SQLAlchemy or ScalikeJDBC for example. It's not practical for any one such library to support every extension provided by every database engine. SQL provides a fall-back for when you need a back-end feature that hasn't been implemented in any given front-end.

Re: SQLite: Past, Present, and Future

#122

Post by spaniard89277 » Tue, Sep 20, 2022, 12:28 PM UTC

aljgz wrote: post #120

spaniard89277 wrote: post #101

I've been learning SQL recently with PostgreSQL and MySQL in an online bootcamp here in Spain. So far very comprehensive. We've touched indexing and partitioning with EXPLAIN ANALYZE for optimizing performance, and I've implemented this strategies successfully onto an active forum I own. The SQL course has almost no love by the students but so far it has been the most useful and interesting to me. I was able to creat…

> The SQL course has almost no love by the students This is a big early career mistake. I've seen experienced developers use NoSql in a project where Sql is clearly a great fit, then waste lots of manpower to emulate things you get with Sql for free. Of course one's career can fall into a success path that never depends on SQL, but not learning SQL deeply is not a safe bet.

I've read this again and again in this forum and other dev communities, so I didn't hesitate. I can't say I love SQL, but it's not that bad, Databases look interesting to me.

Re: SQLite: Past, Present, and Future

#123

Post by spaniard89277 » Tue, Sep 20, 2022, 12:30 PM UTC

wodenokoto wrote: post #118

spaniard89277 wrote: post #101

I've been learning SQL recently with PostgreSQL and MySQL in an online bootcamp here in Spain. So far very comprehensive. We've touched indexing and partitioning with EXPLAIN ANALYZE for optimizing performance, and I've implemented this strategies successfully onto an active forum I own. The SQL course has almost no love by the students but so far it has been the most useful and interesting to me. I was able to creat…

Don’t sell yourself short. I’m sure the minority here knows what a complex view is

I have no real world experience, I've seen things in Stack Overflow that I hardly manage to understand.

Re: SQLite: Past, Present, and Future

#124

Post by HelloNurse » Tue, Sep 20, 2022, 12:30 PM UTC

robertlagrant wrote: post #112

speed_spread wrote: post #99

Earlier quoted context omitted.

Because query parsing time is totally insignificant compared to query IO? I mean, I get it but the chances that it makes a noticeable difference are zero in almost every case. Also you'd have to change a lot of the existing tooling, at which point you might as well send a compiled agent or use stored procs?

> Because query parsing time is totally insignificant compared to query IO? The problem there is that the SQL query string is not parsed at compile time of the host program, so things that could be caught at compile-time are not, and things like appending strings to SQL strings in an unsafe way are much too easy to do.

That's why there are query parameters (see https://www.sqlite.org/lang_expr.html#varparam for the comprehensive SQLite implementation) and automatic escaping. Not to mention tests and code reviews.

Re: SQLite: Past, Present, and Future

#125

Post by dkjaudyeqooe » Tue, Sep 20, 2022, 12:32 PM UTC

rprospero wrote: post #108

AndrewDucker wrote: post #96

Earlier quoted context omitted.

Are you suggesting an alternate syntax for SQL-like queries, which is more compact? Or a specific one for SQLite? I'd be very surprised if generating the SQL query string and parsing it again was more than a trivial percentage of the query execution time, but happy to be proven wrong.

As I said in another part of the thread, I was in a scenario where we were performed millions of inserts into a table of four integers, one row at a time. Generating the string and parsing it again wound up being enough to blow our 10µs time budget.

But with parameters you don't need to do that, or in any other scenario where only (literal) values change.

On the other hand, if you were importing data and each line specified which table and column it had to go into you'd probably have to write a new SQL statement each time.

Re: SQLite: Past, Present, and Future

#126

Post by dkjaudyeqooe » Tue, Sep 20, 2022, 12:40 PM UTC

AndrewDucker wrote: post #96

dkjaudyeqooe wrote: post #93

Earlier quoted context omitted.

When you're accessing a SQLite database in code you have to generate a query string. Parameters ameliorate that somewhat, but in many cases you still have to regenerate a new string for each new query. It's inefficient to translate your query into a string only to have it parsed back into something structured by SQLite.

Are you suggesting an alternate syntax for SQL-like queries, which is more compact? Or a specific one for SQLite? I'd be very surprised if generating the SQL query string and parsing it again was more than a trivial percentage of the query execution time, but happy to be proven wrong.

Probably, but it's still inefficient in other ways. There's memory issues and the code overhead of dealing with strings. It'd be much nicer to have a binary interface, but admittedly it would be much more complex.

The real solution is to include an alternative to SQL that looks and works like Datalog including things like variables. That would make SQLite 100x more productive for programmers. But that will never happen.

Re: SQLite: Past, Present, and Future

#127

Post by manigandham » Tue, Sep 20, 2022, 12:44 PM UTC

js8 wrote: post #117

Why cannot SQLite have two different table storage engines for different tables, one row and the other column oriented?

The same reasoning in the article applies: it's a lot of added complexity that isn't related to its core use as a general purpose in-process SQL database.

Usually OLAP at these scales is fast enough with SQLite or you can use DuckDB if you need a portable format before graduating to a full on distributed OLAP system.

Re: SQLite: Past, Present, and Future

#128

Post by dkjaudyeqooe » Tue, Sep 20, 2022, 12:47 PM UTC

spaniard89277 wrote: post #101

I've been learning SQL recently with PostgreSQL and MySQL in an online bootcamp here in Spain. So far very comprehensive. We've touched indexing and partitioning with EXPLAIN ANALYZE for optimizing performance, and I've implemented this strategies successfully onto an active forum I own. The SQL course has almost no love by the students but so far it has been the most useful and interesting to me. I was able to creat…

In the real world a relational database is the single most useful tool short of a compiler/interpreter. SQL is anachronistic but still works well even if its a pain.

My advice: avoid MySQL like the plague. PgSQL and SQLite is all you ever need and all you ever want.

Re: SQLite: Past, Present, and Future

#129

Post by Timpy » Tue, Sep 20, 2022, 12:52 PM UTC

oaiey wrote: post #90

After seeing one diagram: Why the hack are we still talking to databases with SQL strings and not directly specifying the Query-AST? Admins, sure (a fancy UI could help there as well) but why in our code?

Isn't that what a parameterized query does? It separates the sql logic from the inputs so it can cache the query and then it accepts inputs separately. Safer and more optimal at the same time, the engine doesn't have to re-optimize the same query again for the life of the connection. If my understanding is wrong somebody please correct me, it's kind of hard to get good information on what's going on under the hood with these things.

Re: SQLite: Past, Present, and Future

#130

Post by dkjaudyeqooe » Tue, Sep 20, 2022, 12:55 PM UTC

aljgz wrote: post #120

spaniard89277 wrote: post #101

I've been learning SQL recently with PostgreSQL and MySQL in an online bootcamp here in Spain. So far very comprehensive. We've touched indexing and partitioning with EXPLAIN ANALYZE for optimizing performance, and I've implemented this strategies successfully onto an active forum I own. The SQL course has almost no love by the students but so far it has been the most useful and interesting to me. I was able to creat…

> The SQL course has almost no love by the students This is a big early career mistake. I've seen experienced developers use NoSql in a project where Sql is clearly a great fit, then waste lots of manpower to emulate things you get with Sql for free. Of course one's career can fall into a success path that never depends on SQL, but not learning SQL deeply is not a safe bet.

Most "programmers" just can't understand relational databases and SQL. It's too hard.

I've seen things you wouldn't believe. Random deadlocks in multi-billion transaction reporting systems. Atomic transactions split into multiple commits in banking applications. Copying rows between tables instead of setting a flag on a row. All because highly paid programmers are scared of RDBs.