5 ms·

SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? I

SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages. (It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator")

6nf 10y ago
> SQL is itself an abstraction over the "language" of query execution plans. I don't agree with this at all. SQL does not abstract a specific execution plan. The execution plan is calculated by the RDBMS to efficiently compile the results required by a given SQL statement. The plan depends on available indexes, table statistics, input parameters and so on. The execution plan might change from time to time as new indexes are built or as table statistics change. When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like.
- umanwizard 10y ago
  How does this contradict my point? C is an abstraction over machine language, in the same way; but so is Prolog.
  - 6nf 10y ago
    C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. SQL does not map any specific execution plan. The execution plan will be very different depending on external factors such as the contents of the tables and so on. Have you looked at some actual execution plans? Related rhetorical question for you: What is the difference between a 3rd generation and a 4th generation programming language? (SQL is a 4th GL and C is a 3rd GL)
    - umanwizard 10y ago
      > C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. This is totally not true unless you have optimizations turned off. Have you ever looked at what clang or gcc generates with -O2 or -O3 ? Anyway, regardless of whether you agree with me on this point, I don't see how it relates to the general point I'm making. This seems like it's just disagreement over the definition of the word "abstraction". It doesn't change (and in fact, actually supports!) my main point, which was that SQL is not properly regarded as "native" to database engines in some special way that can't be true of LINQ or any other language.
      - 6nf 10y ago
        You still seem to think that LINQ to SQL is an 'implementation detail' and it would be possible to do LINQ to DB Execution Plan directly. This is just plain wrong and you are missing important details of how LINQ and SQL and RDBMSes work in the real world.
        recursive 10y ago
        I can't see a reason why LINQ to DB Execution plan couldn't exist. If it's wrong, please tell us why.
        acscott 10y ago
        Today's db optimizer builds a set of execution plans and chooses the best one based on "cost". Number of rows, memory access vs. disk access, and other things factor into this cost. Also, the higher the number of joins, the greater the number of plans. 7 joins would be 7! possible plans +. This has to happen fast, so db optimizer cannot generate every possible plan and pick the optimal one. There's a body of research to delve more into how it does this. The point is that the optimizer does real work, as fast as possible to generate the execution plan (they called this dynamic programming in one paper I read). The LINQ interpreter on the client would have to retrieve the statistics from the server's tables and indexes of interest and perform the same operation as the optimizer (costing more network and some db cpu). Next it would have to send back the plan to the db executor in some way. This plan "marshalling" would cause more network traffic, requiring the executor to "unmarshall" the plan costing more CPU. This is less efficient than the current scheme. Alternatively the LINQ language could be implemented on the database but with its own inherent difficulties, but in reverse. Once a plan is cached and is reused, this inefficiency goes away to some degree. So there could be a possible mechanism for the server to send back to LINQ client a hash identifying the plan if it is cached and then have LINQ only send the hash with parameters to the DB on next execution. (You would have to see if this isn't covered by some patent of course!) The constraint is the network connection. If the network connection between client and server were faster and bigger than the data bus on the computers, then it would change the equation significantly. But longer distances means slower communication all things being equal (lightspeed and all). So a network connection being better than the data bus would be inefficient and quickly remedied in a competitive marketplace.
  - derphurp 10y ago
    They are all abstractions, but not at the same level of abstraction.
- deleted 10y ago
  [deleted]
- fpig 10y ago
  > When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like. Which is the exact same thing you do when you write a LINQ query against a database context... Both are just languages that describe what you want to achieve, not how to achieve it.
agentgt 10y ago
> SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? > I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages. Thats the thing. There is nothing theoretical about this. It is about what is actually closer to the real thing which in this case is the data. I agree LINQ is far easier to understand and is more productive but those benefit just don't outweigh my performance, safety, and concurrency concerns. Understanding the database along with its native SQL gets you that. For others the productivity is worth it. > (It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator") Doesn't GHC backend compile directly to assembly?
- umanwizard 10y ago
  > It is about what is actually closer to the real thing which in this case is the data. Why is SQL closer to the data? > Understanding the database along with its native SQL gets you that. My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed. > Doesn't GHC backend compile directly to assembly? Maybe now but at some point it generated C.
  - agentgt 10y ago
    > My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed. Yes please point a reference to me to a database that speaks LINQ or how LINQ directly generates to an execution plan (I admit I haven't touched MS SQL Server in some time). In theory you are right but not in reality. To give another analog you still have to know Javascript to know TypeScript or Coffeescript (or whatever else in vogue) because WASM isn't a reality yet.
derphurp 10y ago
"SQL is itself an abstraction over the "language" of query execution plans." This is completely wrong. SQL is a declarative language, it tells database what to do, not how to do it (aka query plan).
- umanwizard 10y ago
  Yes, hence it's an abstraction. I didn't say SQL is a way of writing query execution plans but it seems some people are interpreting my post that way.
- hackinthebochs 10y ago
  I'm not sure what you guys think an abstraction is, but a declarative language on top is just another kind of abstraction.

6nf 10y ago

> SQL is itself an abstraction over the "language" of query execution plans. I don't agree with this at all. SQL does not abstract a specific execution plan. The execution plan is calculated by the RDBMS to efficiently compile the results required by a given SQL statement. The plan depends on available indexes, table statistics, input parameters and so on. The execution plan might change from time to time as new indexes are built or as table statistics change. When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like.

umanwizard 10y ago
How does this contradict my point? C is an abstraction over machine language, in the same way; but so is Prolog.
- 6nf 10y ago
  C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. SQL does not map any specific execution plan. The execution plan will be very different depending on external factors such as the contents of the tables and so on. Have you looked at some actual execution plans? Related rhetorical question for you: What is the difference between a 3rd generation and a 4th generation programming language? (SQL is a 4th GL and C is a 3rd GL)
  - umanwizard 10y ago
    > C statements map to very specific ASM, you can look at a C statement and be pretty sure what the ASM will end up looking like. This is totally not true unless you have optimizations turned off. Have you ever looked at what clang or gcc generates with -O2 or -O3 ? Anyway, regardless of whether you agree with me on this point, I don't see how it relates to the general point I'm making. This seems like it's just disagreement over the definition of the word "abstraction". It doesn't change (and in fact, actually supports!) my main point, which was that SQL is not properly regarded as "native" to database engines in some special way that can't be true of LINQ or any other language.
    - 6nf 10y ago
      You still seem to think that LINQ to SQL is an 'implementation detail' and it would be possible to do LINQ to DB Execution Plan directly. This is just plain wrong and you are missing important details of how LINQ and SQL and RDBMSes work in the real world.
      - recursive 10y ago
        I can't see a reason why LINQ to DB Execution plan couldn't exist. If it's wrong, please tell us why.
        acscott 10y ago
        Today's db optimizer builds a set of execution plans and chooses the best one based on "cost". Number of rows, memory access vs. disk access, and other things factor into this cost. Also, the higher the number of joins, the greater the number of plans. 7 joins would be 7! possible plans +. This has to happen fast, so db optimizer cannot generate every possible plan and pick the optimal one. There's a body of research to delve more into how it does this. The point is that the optimizer does real work, as fast as possible to generate the execution plan (they called this dynamic programming in one paper I read). The LINQ interpreter on the client would have to retrieve the statistics from the server's tables and indexes of interest and perform the same operation as the optimizer (costing more network and some db cpu). Next it would have to send back the plan to the db executor in some way. This plan "marshalling" would cause more network traffic, requiring the executor to "unmarshall" the plan costing more CPU. This is less efficient than the current scheme. Alternatively the LINQ language could be implemented on the database but with its own inherent difficulties, but in reverse. Once a plan is cached and is reused, this inefficiency goes away to some degree. So there could be a possible mechanism for the server to send back to LINQ client a hash identifying the plan if it is cached and then have LINQ only send the hash with parameters to the DB on next execution. (You would have to see if this isn't covered by some patent of course!) The constraint is the network connection. If the network connection between client and server were faster and bigger than the data bus on the computers, then it would change the equation significantly. But longer distances means slower communication all things being equal (lightspeed and all). So a network connection being better than the data bus would be inefficient and quickly remedied in a competitive marketplace.
- derphurp 10y ago
  They are all abstractions, but not at the same level of abstraction.
deleted 10y ago
[deleted]
fpig 10y ago
> When you write an SQL statement, you are defining what you want and how the tables are related in your context. You are NOT telling the RDBMS what the actual execution plan should look like. Which is the exact same thing you do when you write a LINQ query against a database context... Both are just languages that describe what you want to achieve, not how to achieve it.

agentgt 10y ago

> SQL is itself an abstraction over the "language" of query execution plans. Why do you regard SQL as particularly more fundamental than any other abstraction? > I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages. Thats the thing. There is nothing theoretical about this. It is about what is actually closer to the real thing which in this case is the data. I agree LINQ is far easier to understand and is more productive but those benefit just don't outweigh my performance, safety, and concurrency concerns. Understanding the database along with its native SQL gets you that. For others the productivity is worth it. > (It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator") Doesn't GHC backend compile directly to assembly?

umanwizard 10y ago
> It is about what is actually closer to the real thing which in this case is the data. Why is SQL closer to the data? > Understanding the database along with its native SQL gets you that. My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed. > Doesn't GHC backend compile directly to assembly? Maybe now but at some point it generated C.
- agentgt 10y ago
  > My whole point is that SQL is not any more "native" than LINQ. The database takes SQL and computes an execution plan in its own internal representation. This can look totally different from the SQL as the other commentator pointed out. SQL does not in any way correspond to the "native" way the data is stored or how query results are computed. Yes please point a reference to me to a database that speaks LINQ or how LINQ directly generates to an execution plan (I admit I haven't touched MS SQL Server in some time). In theory you are right but not in reality. To give another analog you still have to know Javascript to know TypeScript or Coffeescript (or whatever else in vogue) because WASM isn't a reality yet.

derphurp 10y ago

"SQL is itself an abstraction over the "language" of query execution plans." This is completely wrong. SQL is a declarative language, it tells database what to do, not how to do it (aka query plan).

umanwizard 10y ago
Yes, hence it's an abstraction. I didn't say SQL is a way of writing query execution plans but it seems some people are interpreting my post that way.
hackinthebochs 10y ago
I'm not sure what you guys think an abstraction is, but a declarative language on top is just another kind of abstraction.