undefined | Better HN

0 pointsdkjaudyeqooe3y ago0 comments

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.

0 comments

AndrewDucker3y ago

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.

dkjaudyeqooeOP3y ago

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.

AndrewDucker3y ago

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

rprospero3y ago

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.

dkjaudyeqooeOP3y ago

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.

aaaaaaaaata3y ago

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

What did you do for a solution?

1 more reply

chasil3y ago

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.

ThemalSpan3y ago

Is that a fact or an intuition?

j / k navigate · click thread line to collapse

0 comments

AndrewDucker3y ago

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.

dkjaudyeqooeOP3y ago

AndrewDucker3y ago

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

rprospero3y ago

dkjaudyeqooeOP3y ago

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.

aaaaaaaaata3y ago

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

What did you do for a solution?

1 more reply

chasil3y ago

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.

ThemalSpan3y ago

Is that a fact or an intuition?

j / k navigate · click thread line to collapse