Yesterday we took a look at how Entity Framework 6.1 communicates with SQL Server when retrieving data from multiple related tables. It builds inline views, adapting the data from multiple, related tables, transforming them to fit into a single, de-normalized view, looking much like a jagged array when the null values are ignored.
This results in one round trip to the database. It has a flexible structure, as the query is built dynamically with each call. It does use parameters for key values. This results in a re-usable query plan, which is cached in the dynamic query cache in SQL Server. I have found that there is a performance threshold using this technique. As you add more records to the results, or, more tables to the set, the performance of this technique diminishes.
Other ORM tools rely on multiple round trips. Many have a query technique to return child records from a table by the foreign key of the parent table. As more than one parent table record is required by your app, a separate round trip is executed for each child table. While this results in a lot more round trip executions, the load on the SQL Server is often much less, and higher performing. Yes, your ORM software has to convert all of this to objects. But it is no less onerous than digging objects out of a Jagged Array.
Perhaps it’s time for a head to head comparison of some of the different ORM toolkits. If you have some experience in this matter, your comments would provide value for our readers. Leave a note here, or drop an email to btaylor@sswug.org.
Cheers,
Ben