4 ms·

It’s interesting how much SQL, a declarative language, requires thinking about performance. In theory, declarative languages would allow the user to think less

It’s interesting how much SQL, a declarative language, requires thinking about performance. In theory, declarative languages would allow the user to think less about imperative concerns like the query plan. I’ve rewritten the same SQL query and got a 1000x speed up, looks like this author has too.

jusssi 5y ago
I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join: SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar' The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.
- alexisread 5y ago
  Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly. I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query. Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.
  - ako 5y ago
    How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?
    - alexisread 5y ago
      Well you switch to a different linq query as your heuristic changes. This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it. As with most coding, right tool for the job, YMMV for SQL or linq.
- BenoitP 5y ago
  RDBMS have statistics to try addressing that. IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future. These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.
  - tomrod 5y ago
    Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.
scythmic_waves 5y ago
This aspect of SQL is my favorite example of a "leaky abstraction".
- valenterry 5y ago
  It's not a leaky abstraction, because it doesn't make any promises about performance. If you still call it a "leaky abstraction" then _every_ abstraction is leaky and we can simply alias "leaky abstraction" to "abstraction" and move on.
jabart 5y ago
With any language, the more you tell the compiler information it doesn't know the better. Want to create an list of a million items but the default constructor is 50, have to tell the compiler. Same with SQL. I know X is a great start to a filter since it's unique in this one use case I'm working on, for everything else us the stats for the column. SQL has a huge number of ways to read the data, and rewritting the sql query is reprogramming how your code reads and writes data from memory/disk. Every language has the same exact problem.
mamcx 5y ago
> It’s interesting how much SQL, a declarative language, I think this is the main mistake. SQL is not declarative language, is a high-level DSL: 1 + 1 = 2 //most PLs SELECT 1 + 1 = 2 //SQL SET 1 + 1 = 2 //TCLish [1, 2] | sum = 2 //Functional 1+1 = NOT 2! //HTML, a TRUE declarative language! The main issues that causes this is that SQL is most of the time a little piece disconnected here and there. You don't see how is so imperative and functional until you write manually a big .sql file. Also, is crippled intentionally, so you don't use it for "regular" programming task (ie, not real way to do print("hello world")!. I start with FoxPro/dBASE and never develop this disconnect because for me, in Fox, SQL was just another sub-dialect of Fox, that was a "full" programming language. So every-time I do: SELECT * FROM customer WHERE code = 1 SCAN customer WHILE code = 1 //Equivalent Fox CMD ?customer ENDSCAN And similar how in Fox you know that your filters and sort depend on indexes then the same with SQL. I still look SQL and see it imperatively and have a good grasp on how everything execute (ie: at least until the query planner disagree with me!).

jusssi 5y ago

I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join: SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar' The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.

alexisread 5y ago
Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly. I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query. Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.
- ako 5y ago
  How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?
  - alexisread 5y ago
    Well you switch to a different linq query as your heuristic changes. This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it. As with most coding, right tool for the job, YMMV for SQL or linq.
BenoitP 5y ago
RDBMS have statistics to try addressing that. IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future. These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.
- tomrod 5y ago
  Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.

scythmic_waves 5y ago

This aspect of SQL is my favorite example of a "leaky abstraction".

valenterry 5y ago
It's not a leaky abstraction, because it doesn't make any promises about performance. If you still call it a "leaky abstraction" then _every_ abstraction is leaky and we can simply alias "leaky abstraction" to "abstraction" and move on.

jabart 5y ago

With any language, the more you tell the compiler information it doesn't know the better. Want to create an list of a million items but the default constructor is 50, have to tell the compiler. Same with SQL. I know X is a great start to a filter since it's unique in this one use case I'm working on, for everything else us the stats for the column. SQL has a huge number of ways to read the data, and rewritting the sql query is reprogramming how your code reads and writes data from memory/disk. Every language has the same exact problem.

mamcx 5y ago

> It’s interesting how much SQL, a declarative language, I think this is the main mistake. SQL is not declarative language, is a high-level DSL: 1 + 1 = 2 //most PLs SELECT 1 + 1 = 2 //SQL SET 1 + 1 = 2 //TCLish [1, 2] | sum = 2 //Functional 1+1 = NOT 2! //HTML, a TRUE declarative language! The main issues that causes this is that SQL is most of the time a little piece disconnected here and there. You don't see how is so imperative and functional until you write manually a big .sql file. Also, is crippled intentionally, so you don't use it for "regular" programming task (ie, not real way to do print("hello world")!. I start with FoxPro/dBASE and never develop this disconnect because for me, in Fox, SQL was just another sub-dialect of Fox, that was a "full" programming language. So every-time I do: SELECT * FROM customer WHERE code = 1 SCAN customer WHILE code = 1 //Equivalent Fox CMD ?customer ENDSCAN And similar how in Fox you know that your filters and sort depend on indexes then the same with SQL. I still look SQL and see it imperatively and have a good grasp on how everything execute (ie: at least until the query planner disagree with me!).