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

SQLite: Past, Present, and Future

vldb.org

Post reply on HN
Print view View original

131–140 of 147 posts

Re: SQLite: Past, Present, and Future

#131

Post by ryanworl »

js8 wrote: post #117

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

Storage layout is not the primary issue here because IO throughput on commodity hardware has increased significantly in the last 10 years.

DuckDB is significantly faster than SQLite because it has a vectorized execution engine which is more CPU efficient and uses algorithms which are better suited for analytical queries. If you implemented a scan operator for DuckDB to read SQLite files, it would still have better performance.

Re: SQLite: Past, Present, and Future

#132

Post by manigandham »

323 wrote: post #106
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?

Didn't Microsoft LINQ try something like that? Was not particularly successful.

LINQ stands for Language-Integrated Query and is incredibly successful at its purpose of providing powerful querying functionality and extensions baked into the C#/.NET language space itself.

This querying framework is what powers translations and compilation into SQL and several other languages (depending on the datastore provider used).

EntityFramework is one of the most advanced ORMs out there and is supremely productive because of LINQ.

Re: SQLite: Past, Present, and Future

#133

Post by manigandham »

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?

A SQL query is an AST, but represented in a compact portable form. It also supports functions, procedures, and parameterization for flexible and safe query construction.

Your code would get incredibly large and complicated if you had to specify any serious SQL query as a raw AST.

Re: SQLite: Past, Present, and Future

#134

Post by AndrewDucker »

dkjaudyeqooe wrote: post #126
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.

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 happ…

Wouldn't that then just be a prepared statement with variables?

Re: SQLite: Past, Present, and Future

#135

Post by kwhitefoot »

dkjaudyeqooe wrote: post #130
aljgz wrote: post #120

Earlier quoted context omitted.

> 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.

> All because highly paid programmers are scared of RDBs.

Really? That's not my experience over the last thirty years of programming. As soon as relational databases became something that everyone could use we all jumped in And not just programmers either, a lot of people who never wrote a line of code in their lives became adept at writing SQL in order to get around limitations in ERP systems for instance.

Is this problem something that afflicts only younger programmers?

Re: SQLite: Past, Present, and Future

#136

Post by robertlagrant »

HelloNurse wrote: post #124
robertlagrant wrote: post #112

Earlier quoted context omitted.

> 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.

Yes, those are other ways to try and solve this problem.

Re: SQLite: Past, Present, and Future

#137

Post by sn0wtrooper »

dkjaudyeqooe wrote: post #130
aljgz wrote: post #120

Earlier quoted context omitted.

> 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.

All those strong relations will be lost, like tears in the rain.

Re: SQLite: Past, Present, and Future

#138

Post by euroderf »

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?

Check out prql[1], it might be a conceptual model you'd like.

[1] https://prql-lang.org/

Re: SQLite: Past, Present, and Future

#139

Post by oaiey »

euroderf wrote: post #138
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?

Check out prql[1], it might be a conceptual model you'd like. [1] https://prql-lang.org/

That is just modern SQL with horrible LINQ memories ;)

Re: SQLite: Past, Present, and Future

#140

Post by oaiey »

okennedy wrote: post #121
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 al…

C# LINQ does pass an expression tree into the abstraction before it will then serialize it SQL and then the database deserialize it into an AST. LINQ-to-Objects is in memory and works on the AST directly.

Also both LINQ language syntax and library methods are a builder paradigm for the expression tree. Valid, but still far from ideal representation of an AST.

Post reply on HN