> 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.
> 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.
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)
> 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.
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.
Linq is compiled once. The database will continually monitor performance of the query and recompile the execution plan as needed. Linq runs on the client machine. The query planner runs on the database machine. Many clients will connect to the same database server, and they may not even know beforehand what exactly the RDBMS is capable of, what indexes are available, what hardware the database server is running on, etc. Therefore LINQ can't compile an optimal execution plan before sending the request off to the database server.
In short - LINQ runs on the client machine, the execution plan happens on the database server.
Think of it this way: Lets say you have a database of clients and contacts. Lots of systems in your company connects to this database to access this data. Each of those systems will submit SQL in the form of 'select clientname from clients where id = 123' or whatever. Now lets say the client list grows and the old execution plan is not optimal any more. Our smart RDBMS can just dynamically fix the execution plan and performance goes up for every system accessing the database. If the RDBMS instead received a rigid execution plan, EVERY client system will need to recalculate the execution plan.
Also: lets say your client app connects to several different database servers. What's a good exection plan on one server it not going to be a good execution plan on another server, so LINQ would need to keep a list of database servers with table statistics, indexes, etc etc and continually monitor all of those for changes. It's massive duplication of work. It's much more efficient to have each database server look after its own execution plans.
Sorry,I should have been clearer. The GP post mentioned 2 ways to implement a LINQ to execution plan transform. The first way would be to do it on the client. This is problematic because the client doesn't have enough information to generate an efficient execution plan. That makes sense to me. The second way would be to do it on the server. This is the part I don't understand. Why can't we do it that way? This is how it is done for SQL.
Interesting discussion as it considers how SQL is implemented, what is an abstraction, etc.
On the topic of concept-rightness you quickly veer into a language game. gcc transforms c code into machine code (and may also output error messages, warnings and such). For some, this is not important, and may not be conceptually right. But if you are interested in say the ABI for instance, you do care.
On the point of "fundamentalness of abstraction", I interpret it as a measure of how many layers of abstraction (Ab) can be decomposed into the concrete thing of interest. LINQ generates another layer, that of SQL. If it generated an execution plan and sent that to the database optimizer, then I would say it does not generate another layer and would be equal in level of Ab to SQL.
But if LINQ can generate SQL that runs on the database, then it is also possible for the database to support LINQ natively. And maybe that was the original intent.
However, as it stands today LINQ to SQL costs CPU resources. So in adopting LINQ one of the tradeoffs you are adopting is CPU resources for developer use/benefit of LINQ.
>>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.)
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.