4 ms·

How is implementing LINQ on the database any different implementing SQL on the database?

6nf 10y ago
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.
- eeperson 10y ago
  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.

6nf 10y ago

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.

eeperson 10y ago
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.