Multiple Result Sets for SplitQuery

[verdie-g]

In Single vs. Split Queries it states

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

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.

Though I'm confused why multiple result sets can't be used to avoid the extra round trip for split queries and why MARS is mentioned when interleave reads between the result sets don't seem to be needed.

For example, I would expect this code

context.Blogs
    .Include(blog => blog.Posts)
    .AsSingleQuery()
    .Where(b => b.Id == 5)
    .ToArray();

to generate something like

SELECT *
FROM blogs b
JOIN posts p ON b.id = p.blog_id
WHERE b.id = 5;

and

context.Blogs
    .Include(blog => blog.Posts)
    .AsSplitQuery()
    .Where(b => b.Id == 5)
    .ToArray();

to generate something like

SELECT * FROM blogs WHERE id = 5;
SELECT * FROM posts WHERE blog_id = 5;

then those two result sets can be read one after the other without needing MARS:

reader.Read();
var blog = ReadBlog(reader);
reader.NextResult();
while (reader.Read())
{
    blog.Posts.Add(ReadPost(reader));
}

but I understand that currently the two select requires two round trip to the database. What am I missing?

I couldn't find a specific issue about that subject but I think it has been discussed before so it may just be a documentation issue.

[roji]

Though I'm confused why multiple result sets can't be used to avoid the extra round trip for split queries [...]

This is definitely possible, and already tracked by #10878. It just hasn't been implemented yet.

As for the rest, a clue to how/why things work as they do can be found in the SQL that's actually generated by EF. For single query:

SELECT [b].[Id], [b].[Name], [p].[Id], [p].[BlogId]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
WHERE [b].[Id] = 5
ORDER BY [b].[Id]

... and for split:

SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
ORDER BY [b].[Id]

SELECT [p].[Id], [p].[BlogId], [b].[Id]
FROM [Blogs] AS [b]
INNER JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
ORDER BY [b].[Id]

As you can see, EF adds ORDER BY [b].[Id]. This was done in order to allow results to be streamed backed from the database; in other words, the goal here was to allow the user to read the first Blog - with all its Posts populated - before having to read the entire resultset from the database (without the ordering, a Blog's Posts can be spread around the entire resultset). For full streaming of split query results, MARS is required, otherwise the principal (Blog) query results must be fully buffered in memory before the dependent (Post) query can be executed.

This approach was implemented some time ago; we are now considering allowing removing the additional ORDER BY for performance reasons, even if that causes streaming to no longer be possible - see #29171.

[verdie-g]

Thanks for that detailed answer! I hope we'll see that feature soon in EF :)