http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Other than the needless sort (not sure why that's there) and the compute scalars (which are negligible in the query plan) the EF and NH query plans are equivalent. Someone's already mentioned that a true head to head match would be NQL vs ESQL (or Linq to NHibernate vs Linq to Entities) right now you're comparing apples to oranges. Also, without a large dataset and database optimization (indices where they need to be). The query plan tells you nothing. That sort accounts for 47% of the operation, but if the total execution time is under 5 milliseconds for 10k rows who cares? The database isn't your bottleneck in that case, the network will be. Everything needs to be considered in context. The benefit of EF and NH is that you don't have to worry about these things until they become a problem. If you're noticing response issues, that's when you pop open EFProf/NHProf (or the built in SQL Profiler for SQL Server) and look what's going on. Heck SQL Server even comes with an analysis tool that will recommend ways to improve the performance of the database from your usage. Make your load tests, run it against your database with the analysis tool on and look at the results. Finally, after looking at all of these, if I still see a problem, I have the option of wrapping that call in a sproc with EF or NH. It's a good thing that you're providing these tools for devs to understand what's going on behind the scenes. But without a skilled DBA who can provide true guidance on whether that should give pause, or if it's a code or Database concern it's like having an MRI and no Doctor to read it. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment28http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment28Tue, 30 Mar 2010 23:55:34 GMTI agree with Alex for the most part. My initial reaction was to gawk at the number of clustered index scans (e.g. table scans) in each of the query plans. Perhaps this is somewhat of a misnomer in this case if there isn't much data in the tables or a lack of indexes, though. Tossing that aside for the moment, it does seem LINQ to SQL is the worst case of the three and I don't understand why it needs COUNT(*) from Comments. I may be mistaken (I haven't peeked at the internal LINQ to SQL code), but this could probably be removed and handled more efficiently on the client side. As far as the amount of data passed across the network, LINQ to SQL is probably somewhat more efficient. NHibernate and Entity Frameworks isn't exactly returning a Cartesian product, but it's close enough I think I understand why you say it is. Just curious here, but have hierarchical resultsets fallen out of favor? I haven't dealt with them in years and have one VB6 project using them that I need to rewrite in C# at some point. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment27http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment27Fri, 05 Feb 2010 20:55:03 GMTSorry, "One more evidence: IF estimated amounts"... http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment26http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment26Fri, 05 Feb 2010 18:29:12 GMT> Based on the costs in the query plan I'm guessing there is virtually no data in the test database Most likely. One more evidence: estimated amounts of data are large, there must be hash or merge join instead of nested loops. > What happens when there is significantly more data loaded? +1. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment25http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment25Fri, 05 Feb 2010 18:28:05 GMTJohn Chapman is right but in case of EF you can see clearly that it is inferior to the other two. The SQL is ridiculous. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment24http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment24Fri, 05 Feb 2010 16:05:57 GMTBased on the costs in the query plan I'm guessing there is virtually no data in the test database? How else would a clustered index seek and a clustered index scan (on what should be a single item in the nested loop) have the same cost? What happens when there is significantly more data loaded? My initial thought was the question the database design, but now I'm more concerned about the sample data size and discussing optimization on such a small data set that it would really never matter. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment23http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment23Fri, 05 Feb 2010 14:10:24 GMTUntil there are 2 index scans and 2 nested loop joins, I''m not sure if there are any reasons for optimizing SQL here. I.e. indexes and properly defined foreign key constraints must be established first. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment22http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment22Fri, 05 Feb 2010 08:42:55 GMTYou could do a transformation (in the query convert to a DTO containing all the information you want). That way, you don't have to think about eager and lazy loading, for display purposes. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment21http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment21Fri, 05 Feb 2010 07:29:03 GMTOne thing that I find problematic in NHibernate is doing server-side paging while eager-loading many-to-one associations. DistinctEntityRoot transformer operates in memory so you will end with less items per page than expected. It looks like the options are using batch loading instead of eager loading or having a subquery that returns distinct ids. Is there a more elegant way of doing this? http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment20http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment20Fri, 05 Feb 2010 05:39:54 GMTWhat would the execution plan be if you were to do say (with LINQ to SQL) var result = from p in ctx.Posts join c in ctx.Comments on p.PostID equals c.PostID where p.PostID = 1 select new Post() { PostID = p.PostID, Comments = c.PostComments.ToList(), ..... }; Because you would be doing a join, and not two seperate queries. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment19http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment19Thu, 04 Feb 2010 23:05:56 GMT> Btw, I'd like to see the same, but this appropriate indexes. Or more data, if they are there. SQL Server might really prefer scan, if there are just few hundreds of records in Posts and Comments tables (i.e. very few pages). http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment18http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment18Thu, 04 Feb 2010 19:41:52 GMTToo many mistypings again ;) > intrinsically dependent on NH on EF " NH or EF" > but this appropriate indexes "with appropriate ..." http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment17http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment17Thu, 04 Feb 2010 19:39:28 GMTMay be I'm wrong, bot all the cases seems nearly equally bad for me: 1. The only index seek operation there happens for PK_Blogs. Other indexes, that are normally quite large, are scanned. May be this is acceptable in exactly your case (SQL Server makes such decision based on statistics), but in general, this is quite bad. Are there any indexes allowing to do this better at all? E.g. I'd create indexes for Post.Blog and Comment.Post properties. 2. L2S plan seems the strangest one. It scans 3 indexes. Why? Because it computes count(*) aggregate, that actually isn't really necessary in your case (count of items in the collection can be computed completely on the client). 3. EF and NH plans are very similar, the only difference is in sequence of nested loop joins - in NH case it is much better (potential number of rows @ left & right side in both nested loops must be much lower). But this isn't something that is intrinsically dependent on NH on EF - it's just the result of SQL Server query optimizer work. I.e. in your particular case (data, I mean) the second plan might really look more attractive for it. Btw, I'd like to see the same, but this appropriate indexes. There is nothing to compare without them - i.e. SQL Server already did the best while decided to make a single index seek at least ;) http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment16http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment16Thu, 04 Feb 2010 19:36:39 GMTDamien, I just tested that with EF 4.0 The queries are basically the same, the only difference in the aliases used. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment15http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment15Thu, 04 Feb 2010 17:17:44 GMTDamien, It is 3.5, I'll post about the difference between 3.5 & 4.0 shortly http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment14http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment14Thu, 04 Feb 2010 16:59:09 GMTI *think* this is EFv1. Assuming I have that correct, I would be interested to see the same SQL and query plan with EFv4 when it is finally released. As much as I want EF to have the favourable SQL and query plan my gut reaction is to shy away from anything that is more complicated than it needs to be. There is an argument to say "it's a tool, don't worry about it" but sooner or later you will need to and having something nicely formatted and concise to wade through will be easier! http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment13http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment13Thu, 04 Feb 2010 16:49:28 GMTWhat version of Entity Framework is being used here - 3.5SP1 or 4.0? There was a lot of query optimization work done in 4.0. [blogs.msdn.com/.../...ed-sql-in-net-4-0-beta1.aspx](http://blogs.msdn.com/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx) [)amien http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment12http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment12Thu, 04 Feb 2010 16:49:05 GMTSo Ayende, which is better? http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment11http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment11Thu, 04 Feb 2010 16:45:25 GMTLINQ to SQL is not very good at eager loading one-to-many associations. It often generates multiple queries or even select n+1 scenarios. The output of the NHibernate sample is exactly what I would expect. Why does it need to be any more complicated? http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment10http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment10Thu, 04 Feb 2010 16:38:23 GMTI'm really looking forward to some examples of there the new EF4 query output. Just curious if it will be a bit nicer to look at. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment9http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment9Thu, 04 Feb 2010 16:38:04 GMTIt would be interesting to see what the equivalent ESQL comes up with, because I don't really trust the LINQ to produce a good equivalent given the history of the late addition of LINQ to the entity framework by the ADO.NET team. SELECT VALUE b FROM BlogEntities.Blogs AS b OUTER APPLY b.Posts AS p OUTER APPLY p.Comments AS c WHERE b.Id = @Id I usually like to try the similar in ESQL, because of the rushed nature of LINQ in EF 1.0. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment8http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment8Thu, 04 Feb 2010 16:36:47 GMTCurious what the logic i/o count is for the three? http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment7http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment7Thu, 04 Feb 2010 16:35:22 GMTIn a previous post of Ayende I have seen Entity Framework use a UNION ALL which is superior to any of the above approaches. I wonder a) if NHibernate will support it in the future b) why EF sometimes chooses to do such a bad job as it did in the example above http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment6http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment6Thu, 04 Feb 2010 15:18:25 GMTWas this SQL generated from EF1 or EF4? http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment5http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment5Thu, 04 Feb 2010 15:16:38 GMT....which is why i just write the SQL I want in the first place... http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment4http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment4Thu, 04 Feb 2010 14:37:11 GMTI second Henning - Cartesian product is tableA x tableB without restricting join or where clauses. In all of the 3 cases you need 1 blog and its associated posts and their associated comments. For me the difference is that in the NHibernate case you get your Blog-entity redundantly back for every row returned. In the Linq-to-SQL you only get it once. So its more a matter of getting superfluous data from the database to the application (which can also important be important based on amount of rows and data in Blog ofcourse). Both solutions are still getting superfluous data for the Posts (when more than one Comment per Post exists). On the downside for the Linq-to-sql SQL there is the Count aggregation which takes around 31% in total (Rightmost scan of Comments in executionplan). This aggregation is completely missing (not necessary) in NHibernate. Concerning EF, the query is pretty much identical to the one from NHibernate, apart from the expensive sorting (which is also the reason for the case logic - that prevents sorting on null's). Why they find it necessary to do the "outer" select I don't know - but could imagine is has to do with the way the SQL is constructed from the expression tree. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment3http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment3Thu, 04 Feb 2010 12:07:01 GMTThe execution plans are very similar in each case, so the query performance should be roughly the same. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment2http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment2Thu, 04 Feb 2010 11:38:21 GMThmm - maybe I'm missing out on something, but in which case does the NH approache create a cartesian product? As far as I know a cartesian product means to take all blogs, posts and comments and create a "matrix" of all possible combinations of blogs, posts and comments. http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment1http://ayende.com/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis#comment1Thu, 04 Feb 2010 11:25:02 GMT