Hacker News Reader

Comment by dkjaudyeqooe | Hacker News Reader

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.

View on HN

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.

What was the time constraint due to, out of curiosity?

What did you do for a solution?

It was a neutron detector that stored the the position, energy, and time stamp into the database. The design spec was 10,000 neutrons per second, so that was the origin of the time limit.

Of course, half those neutrons arrived within a 20ms window, so we had a buffer to handle the load. However, however, if the average remained above the limit, the buffer would fill up. There was a brief discussion of just ignoring events when the buffer was full, but that could introduce systematic errors in the data that would be impossible to detect, so it was better for the database to just crash.

The solution was to tighten the slits on the neutron beam to lower the count rate to the point that we never filled the buffer. Granted, we were testing a high flux technique that, so that was a bit of a disappointment. Everything else in the instrument could handle an order of magnitude more neutrons, except this database.

By the way, to be fair to the database designers, they were working with Clinton era tech and were probably told that the flux rate would never be a SUSTAINED 10,000 events per second.

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.

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.

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

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.

Is that a fact or an intuition?

Reply on news.ycombinator.com