Episode 5 : Single vs Split Query in Entity Framework Core
Read time : 5 mins
This week I have completed 10,000 followers on LinkedIn , I would like to say Thank you to everyone who joined me in this journey. Some exciting news coming SOON!
Single Queries and their Problems
By default while working with LINQ queries in EF queries are treated as an single queries unless we define. Let’s try to understand it with an example.
You can notice that from generated SQL that this query was treated as a single query. This approach can create few issues for us.
Cartesian Explosion
Data Duplication
Cartesian Explosion : In this example, since both Posts and Contributors are collection navigations of Blog they're at the same level - relational databases return a cross product: each row from Posts is joined with each row from Contributors. This means that if a given blog has 10 posts and 10 contributors, the database returns 100 rows for that single blog. This phenomenon - sometimes called cartesian explosion.
Data Duplication : If we don’t specify SELECT then it will bring all columns from all tables if we are joining 3 tables it will bring all columns of 3 tables. For few numbers of columns it will not create issue but for large number of columns it is going to give us a performance heart attack.
Split Queries and its Benefits
To solve these issues EF has introduced a feature of Split Query , it can solve both issues of Single Queries. Let’s see through example.
We can globally enable split query behavior and if not needed we can override it as well.
Although Split Query has some advantages as described above but it has some draw backs as well.
While most databases guarantee data consistency for single queries, no such guarantees exist for multiple queries if data updated concurrently.
Each query currently implies an additional network roundtrip to your database which can cause degrade in performance.
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.
Thank you for reading my Newsletter , see you in next week :)