4 ms·

> 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. T

by 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.
  - xxs 10y ago
    >>you can look at a C statement and be pretty sure what the ASM will end up looking like. Perhaps 20 years ago, perhaps more. Optimizing compilers have gone long way. Compiling an execution plan is a significantly easier task than actually guessing how exactly an optimizing compiler would schedule the code, down to assembly. (and cheating w/ hints in SQL- [looking at Oracle]just reinforces that.)
    - 6nf 10y ago
      Have you ever written or edited an execution plan directly?
- 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.

by 6nf 10y ago

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.
- xxs 10y ago
  >>you can look at a C statement and be pretty sure what the ASM will end up looking like. Perhaps 20 years ago, perhaps more. Optimizing compilers have gone long way. Compiling an execution plan is a significantly easier task than actually guessing how exactly an optimizing compiler would schedule the code, down to assembly. (and cheating w/ hints in SQL- [looking at Oracle]just reinforces that.)
  - 6nf 10y ago
    Have you ever written or edited an execution plan directly?
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.