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

101–110 of 147 posts

Re: SQLite: Past, Present, and Future

#101

Post by spaniard89277 »

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 create some complex views (couldn't understand how to make materialized views in MySQL), but they were still very slow.

I decided to copy most of this forum DB to DuckDB (with Knime now, until I know better), and optimization with DuckDB seems pointless. It's very, very fast. Less energy usage for my brain, and less time waiting. That's a win for me.

My current dataset is about 40GB, so It's not HUGE, and sure people here in HN would laugh at my "complex" views, but so far I've reduced all my concerns from optimizing to how to download the data I need without causing problems to the server.

Re: SQLite: Past, Present, and Future

#102

Post by chasil »

dkjaudyeqooe wrote: post #93
AndrewDucker wrote: post #91

Earlier quoted context omitted.

Can you give an example of what you mean, and what we'd gain from it?

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.

SQLite was released in the year 2000; it is 22 years old.

Because of its age, it does carry deprecated API components that are maintained solely for backward compatibility.

Notice how many _v2 and _v3 variants are present, denoting reworked aspects of the API:

https://sqlite.org/c3ref/funclist.html

A product of this age was designed (and redesigned) for specific needs. Unfortunately, your use case is not among them.

Re: SQLite: Past, Present, and Future

#103

Post by danielheath »

AndrewDucker wrote: post #91
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?

Can you give an example of what you mean, and what we'd gain from it?

Having application build a string and pass it to a library which parses the string into an AST cannot be as efficient as just building the AST, right?

Re: SQLite: Past, Present, and Future

#104

Post by leprechaun1066 »

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?

Most queries to kdb+ do exactly what you are asking for.

Re: SQLite: Past, Present, and Future

#105

Post by rprospero »

AndrewDucker wrote: post #91
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?

Can you give an example of what you mean, and what we'd gain from it?

Not the OP, but I can give two gains. First off, by passing an AST, instead of just an SQL string, we cut out a huge number of possibly SQL injection attacks. Second, in most of the projects where I've used of SQL, there's been some kind of database object that builds the actual query, which it then converts to a string. The database then takes that string and parses it into an AST. There's some performance gains to be made by skipping the middle man and just creating the AST directly.

Many years ago, I was on a project that needed to add rows to a database with a hard 10µs limit. Each rows was just four integers, so the writing part was trivial. However, allocating the string, formatting the integers to strings, then parsing the resulting string often put us over the time limit. Every time the time limit was breached, we lost about five grand. Why we were using an SQL database for this at all is a story for a different time.

Re: SQLite: Past, Present, and Future

#106

Post by 323 »

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.

Re: SQLite: Past, Present, and Future

#107

Post by yread »

danielheath wrote: post #103
AndrewDucker wrote: post #91

Earlier quoted context omitted.

Can you give an example of what you mean, and what we'd gain from it?

Having application build a string and pass it to a library which parses the string into an AST cannot be as efficient as just building the AST, right?

It's a lot more efficient for the human who is used to reading SQL to read the SQL (or even SQL-producing code) than AST

Re: SQLite: Past, Present, and Future

#108

Post by rprospero »

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.

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.

Re: SQLite: Past, Present, and Future

#109

Post by pif »

AndrewDucker wrote: post #91
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?

Can you give an example of what you mean, and what we'd gain from it?

Type safety at compile time to begin with.
Post reply on HN