Why is AsSplitQuery doing separate roundtrip to DB for each query?

Question

I have finally been able to upgrade a very old codebase from EF6 to EF Core 7 and I'm playing with some "new" features.

I have been super hyped about Split query option for eager loading but when reading the documentation I was taken back by this:

Each query currently implies an additional network roundtrip to your database. Multiple network roundtrips can degrade performance, especially where latency to the database is high (for example, cloud services).

Why? Why is it not multiple queries in a single roundtrip? SqlDataReader can process queries with multiple result sets. This stopped me from rewriting existing code using stored procedure to EF.

Unless, these split queries are executed concurrently when MARS is enabled on connection to SQL server. Are they? I know that DbContext does not support parallel processing but does it execute these queries concurrently and only process results in sequence?

Additional question - if there is this limitation for executing each query in separate roundtrip, is it possible to define SplitQuery behaviour only for certain includes in a query instead of defining it for all includes in a query?

Answer 1

Based on some discussion on the github if MARS is enabled EF Core can use it for split queries (see this and this), but in general case (since EF Core can be used with different databases) the next quote from the docs I would argue explains the general case behavior/wording:

While some databases allow consuming the results of multiple queries at the same time (SQL Server with MARS, Sqlite), most allow only a single query to be active at any given point. So all results from earlier queries must be buffered in your application's memory before executing later queries, which leads to increased memory requirements.

Also the following comment by Shay Rojansky (one of the EF team members) can be useful:

MARS support in SqlClient is known to have various performance issues (as well as other bugs), some of them severe. It's recommended to avoid it.