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?
[deleted]
SQLite: Past, Present, and Future
111–120 of 147 posts
Re: SQLite: Past, Present, and Future
#112After 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?
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.
Re: SQLite: Past, Present, and Future
#113After 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?
> Why the hack are we still talking to databases with SQL strings and not directly specifying the Query-AST? The same reason language servers took off. Instead of one to one mapping, SQL enables one to many mapping with minor tweaks, allowing everyone to do whatever they want over a well known, well defined, mature abstraction. In the same spirit, I may ask why we're not writing assembly or even machine code, and we…
You’re missing the point if you think that that is “in the same spirit”.
SQL-as-strings and SQL-as-AST are still the same thing. What is being proposed it not to write procedural code for record retrieval instead of declarative SQL.
Re: SQLite: Past, Present, and Future
#114After 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.
No, it never tried that.
Re: SQLite: Past, Present, and Future
#115After 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 is stunningly successful, LINQ Query syntax (what you probably are referring to) with LINQ to SQL less so.
And yes, as usual, we have the amazing confusion of Microsoft Naming.
But query syntax is essentially just a way to use an ORM that looks closer to SQL but is strongly typed.
Re: SQLite: Past, Present, and Future
#116Earlier 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.
What was the time constraint due to, out of curiosity?
What did you do for a solution?
Re: SQLite: Past, Present, and Future
#117Why cannot SQLite have two different table storage engines for different tables, one row and the other column oriented?
Re: SQLite: Past, Present, and Future
#118I'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
Re: SQLite: Past, Present, and Future
#119Earlier quoted context omitted.
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…
You should be using prepared statements and variable bindings. You should never have to parse any statement more than once (many dynamic language interfaces even cache statements for you, and many SQLite libraries also offer an interface for this out of the box).
You should also never be formatting integers into strings, but simply binding them to the prepared statement. I really hope you weren't interpolating them and formatting each statement with the literal values in the string. That would be horridly inefficient, and a bad misuse of any modern SQL database. I also hope you were properly using transactions to speed up your batches.
Re: SQLite: Past, Present, and Future
#120I'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.