Ayende @ Rahien

Refunds available at head office

Working on the Uber Profiler 3.0

One of the things that tend to get lost is the fact that Hibernating Rhinos is doing more than just working on RavenDB. The tagline we like to use is Easier Data, and the main goal we have is to provide users with better ways to access, monitor and manage their data.

We have started planning the next version of the Uber Profiler, that means Entity Framework Profiler, NHibernate Profiler, etc. Obviously, we’ll offer support for EF 7.0 and NHibernate 4.0, and we had gathered quite a big dataset for common errors when using an OR/M, which we intend to convert into useful guidance whenever our users run into such an issue.

But I realized that I was so busy working on RavenDB that I forgot to even mention the work we’ve been doing elsewhere. And I also wanted to solicit feedback about the kind of features you’ll want to see in the 3.0 version of the profilers.

Special Offer: 29% discount for all our products

Well, it is nearly the 29 May, and that means that I have been married for two years.

To celebrate that, I am offering a 29% discount on all our products (RavenDB, NHibernate Profiler, Entity Framework Profiler, etc).

All you have to do is purchase any of our products using the following coupon code:

2nd anniversary

This offer is valid to the end of the month, so hurry up.

Goodbye, 2012: Our end of year discount starts now!

Well, as the year draws to a close, it is that time again, I got older, apparently. Yesterday marked my 31th trip around the sun.

To celebrate, I decided to give the first 31 people a 31% discount for all of our products.

This offer applies to:

This also applies to our support & consulting services.

All you have to do is to use the following coupon code: goodbye-2012

Enjoy the end of the year, and happy holidays.

Production Cloud Profiling With Uber Prof

With Uber Prof 2.0 (NHibernate Profiler, Entity Framework Profiler, Linq to SQL Profiler, LLBLGen Profiler) we are going to bring you a new era of goodness.

In 1.0, we gave you a LOT of goodness for the development stage of building your application, but now we are able to take it a few steps further. Uber Prof 2.0 supports production profiling, which means that you can run it in production and see what is going on in your application now!

To make things even more interesting, we have also done a lot of work to make sure that this works on the cloud as well. For example, go ahead and look at this site: http://efprof.cloudapp.net/

This is a live application, that doesn’t really do anything special, I’ll admit. But the kicker is when you go to this URL: http://efprof.cloudapp.net/profiler/profiler.html

image

This is EF Prof, running on the cloud, and giving you the results that you want, live. You can read all about this feature and how to enable it here, but I am sure that you can see the implications.

Uber Prof V2.0 is now in Public Beta

Well, we worked quite a bit on that, but the Uber Prof (NHibernate Profiler, Entity Framework Profiler, Linq to SQL Profiler, etc) version 2.0 are now out for public beta.

We made a lot of improvements. Including performance, stability and responsiveness, but probably the most important thing from the user perspective is that we now support running the profiler in production, and even on the cloud.

We will have the full listing of all the new goodies up on the company site soon, including detailed instructions on how to enable production profiling and on cloud profiling, but I just couldn’t wait to break the news to you.

In fact, along with V2.0 of the profilers, we have a brand new site for our company, which you can check here: http://hibernatingrhinos.com/.

To celebrate the fact that we are going on beta, we also offer a 20% discount for the duration of the beta.

Nitpicker corner, please remember that this is a beta, there are bound to be problems, and we will fix them as soon as we can.

Über Profiler v2.0–Private Beta is open

Well, it took a while, but the next version of the NHibernate Profiler is ready to go to a private beta.

We go to private beta before the product is done, because we want to get as much early feedback as we can.

We have 10 spots for NHibernate Profiler v2.0 and 10 spots for Entity Framework Profiler v2.0.

If you are interested, please send me an email about this.

Published at

Originally posted at

An Entity Framework Profiler user story

One of the things that I really love about build things like the profilers and RavenDB is that it gives me the chance to really spread a lot of my experience in codified form. Occasionally, we get customer feedback to that effect, and I thought that I would share this one with you.

Jeremy Holt have been using the Entity Framework Profiler and has the following to say:

The biggest issue it has highlighted for me is the question of the Context being created on different threads (the N+1 was easy to get my head around and resolve). The problem is the difficulty in finding definitive examples of how/when the Context should be created. (Also found my unbound queries – thanks for that!!)

It was working great (except for a couple of threading issues J) until I ran EFProf. I love the “page of shame” in EFProf – I think you called it “query analysis alerts” or something pompous like that – may I suggest you rename it to “errors dumb programmers make” J

I don’t think that this will be renamed, but this does point out something very important. What we are doing with the profilers is much more than simply show you what your application is doing. We are actually analyzing this data through a complex set of rules in order to find common pitfalls. In Jeremy’s case, the common ones were cross thread usage and Select N+1.

And by highlighting those issues, the profiler make it easy to do so. More so, it make you feel good about doing this, in a way that you wouldn’t get from standard analytics tools.

By having thresholds that you can visibly see and avoid, you get to create better software.

Tags:

Published at

Originally posted at

Comments (2)

Application analysis: Northwind.NET

For an article I am writing, I wanted to compare a RavenDB model to a relational model, and I stumbled upon the following Northwind.NET project.

I plugged in the Entity Framework Profiler and set out to watch what was going on. To be truthful, I expected it to be bad, but I honestly did not expect what I got. Here is a question, how many queries does it take to render the following screen?

image

The answer, believe it or no, is 17:

image

You might have noticed that most of the queries look quite similar, and indeed, they are. We are talking about 16(!) identical queries:

SELECT [Extent1].[ID]           AS [ID],
       [Extent1].[Name]         AS [Name],
       [Extent1].[Description]  AS [Description],
       [Extent1].[Picture]      AS [Picture],
       [Extent1].[RowTimeStamp] AS [RowTimeStamp]
FROM   [dbo].[Category] AS [Extent1]

Looking at the stack trace for one of those queries led me to:

image

And to this piece of code:

image

You might note that dynamic is used there, for what reason, I cannot even guess. Just to check, I added a ToArray() to the result of GetEntitySet, and the number of queries dropped from 17 to 2, which is more reasonable. The problem was that we passed an IQueryable to the data binding engine, which ended up evaluating the query multiple times.

And EF Prof actually warns about that, too:

image

At any rate, I am afraid that this project suffer from similar issues all around, it is actually too bad to serve as the bad example that I intended it to be.

Gilad Shalit is back

I don’t usually do posts about current events, but this one is huge.

To celebrate the event, you can use the following coupon code: SLT-45K2D4692G to get 19.41% discount (Gilad was captive for 1,941 days) for all our profilers:

Hell, even our commercial support for NHibernate is participating.

Please note that any political comment to this post that I don’t agree with will be deleted.

Entity Framework 4.1 Update 1, Backward Compatibility and Microsoft

One of the things that you keep hearing about Microsoft products is how much time and effort is dedicated to ensuring Backward Compatibility. I have had a lot of arguments with Microsoft people about certain design decisions that they have made, and usually the argument ended up with “We have to do it this was to ensure Backward Compatibility”.

That sucked, but at least I could sleep soundly, knowing that if I had software running on version X of Microsoft, I could at least be pretty sure that it would work in X+1.

Until Entity Framework 4.1 Update 1 shipped, that is. Frans Bouma has done a great job in describing what the problem actually is, including going all the way and actually submitting a patch with the code required to fix this issue.

But basically, starting with Entity Framework 4.1 Update 1 (the issue also appears in Entity Framework 4.2 CTP, but I don’t care about this much now), you can’t use generic providers with Entity Framework. Just to explain, generic providers is pretty much the one way that you can integrate with Entity Framework if you want to write a profiler or a cacher or a… you get the point.

Looking at the code, it is pretty obvious that this is not intentional, but just someone deciding to implement a method without considering the full ramifications. When I found out about the bug, I tried figuring out a way to resolve it, but the only work around would require me to provide a different assembly for each provider that I wanted to support (and there are dozens that we support on EF 4.0 and EF 3.5 right now).

We have currently implemented a work around for SQL Server only, but if you want to use Entity Framework Profiler with Entity Framework 4.1 Update 1 and a database other than SQL Server, we would have to create a special build for your scenario, rather than have you use the generic provider method, which have worked so far.

Remember the beginning of this post? How I said that Backward Compatibility is something that Microsoft is taking so seriously?

Naturally I felt that this (a bug that impacts anyone who extends Entity Framework in such a drastic way) is an important issue to raise with Microsoft. So I contacted the team with my finding, and the response that I got was basically: Use the old version if you want this supported.

What I didn’t get was:

  • Addressing the issue of a breaking change of this magnitude that isn’t even on a major release, it is an update to a minor release.
  • Whatever they are even going to fix it, and when this is going to be out.
  • Whatever the next version (4.2 CTP also have this bug) is going to carry on this issue or not.

I find this unacceptable. The fact that there is a problem with a CTP is annoying, but not that important. The fact that a fully release package has this backward compatibility issue is horrible.

What makes it even worse is the fact that this is an update to a minor version, people excepts this to be a safe upgrade, not something that requires full testing. And anyone who is going to be running Update-Package in VS is going to hit this problem, and Update-Package is something that people do very often. And suddenly, Entity Framework Profiler can no longer work.

Considering the costs that the entire .NET community has to bear in order for Microsoft to preserve backward compatibility, I am deeply disappointed that when I actually need this backward compatibility.

This is from the same guys that refused (for five years!) to fix this bug:

new System.ComponentModel.Int32Converter().IsValid("yellow") == true

Because, and I quote:

We do not have the luxury of making that risky assumption that people will not be affected by the potential change. I know this can be frustrating for you as it is for us. Thanks for your understanding in this matter.

Let me put this to you in perspective, anyone who is using EF Prof is likely to (almost by chance) to get hit by that. When this happens, our only option is to tell them to switch back a version?

That makes us look very bad, regardless of what is the actual reason for that. That means that I am having to undermine my users' trust in my product. "He isn't supporting 4.1, and he might not support 4.2, so we probably can't buy his product, because we want to have the newest version".

This is very upsetting. Not so much about the actual bug, those happen, and I can easily imagine the guy writing the code making assumptions that turn out to be false. Heavens know that I have done the same many times before. I don’t even worry too much about this having escaped the famous Microsoft Testing Cycle.

What (to be frank) pisses me off is that the response that we got from Microsoft for this was that they aren’t going to fix this. That the only choice that I have it to tell people to downgrade if they want to use my product (with all the implications that has for my product).

Sad smile

What is next for the profilers?

We have been working on the profilers (NHibernate Profiler, Entity Framework Profiler, Linq to SQL Profiler, LLBLGen Profiler and Hibernate Profiler) for close to three years now. And we have been running always as 1.x, so we didn’t have a major release (although we have continual releases, we currently have close to 900 drops of the 1.x version).

The question now becomes, what is going to happen in the next version of the profiler?

  • Production Profiling, the ability to setup your application so that you can connect to your production application and see what is going on right now.
  • Error Analysis, the ability to provide you with additional insight and common solution to recurring problems.
  • Global Query Analysis, the ability to take all of your queries, look at their query plans and show your potential issues.

Those are the big ones, we have a few others, and a surprise in store Smile

What would you want to see in the next version of the profiler?

New Profiler Feature: Avoid Writes from Multiple Sessions In The Same Request

Because I keep getting asked, this feature is available for the following profilers:

This new feature detects a very interesting bad practice, write to the database from multiple session in the same web request.

For example, consider the following code:

public void SaveAccount(Account account)
{
    using(var session = sessionFactory.OpenSession())
    using(session.BeginTransaction())
    {
           session.SaveOrUpdate(account);
           session.Transaction.Commit();    
    }
}
public Account GetAccount(int id)
{
    using(var session = sessionFactory.OpenSession())
    {
        return session.Get<Account>(id);
    }
}

It is bad for several reasons, micro managing the session is just one of them, but the worst part is yet to come…

public void MakePayment(int fromAccount, int toAccount, decimal ammount)
{
    var from = Dao.GetAccount(fromAccount);
    var to = Dao.GetAccount(toAccount);
    from.Total -= amount;
    to.Total += amount;
    Dao.SaveAccount(from);
    Dao.SaveAccount(to);
}

Do you see the error here? There are actually several, let me count them:

  • We are using 4 different connections to the database in a single method.
  • We don’t have transactional safety!!!!

Think about it, if the server crashed between the fifth and sixth lines of this method, where would we be?

We would be in that wonderful land where money disappear into thin air and we stare at that lovely lawsuit folder and then jump from a high window to a stormy sea.

Or, of course, you could use the profiler, which will tell you that you are doing something which should be avoided:

image

Isn’t that better than swimming with the sharks?

New Uber Prof Feature: Too Many Database Calls In The Same Request

Recently, we added a way to track alerts across all the sessions the request. This alert will detect whenever you are making too many database calls in the same request.

But wait, don’t we already have that?

Yes, we do, but that was limited to the scope of one session. there is a very large set of codebases where the usage of OR/Ms is… suboptimal (in other words, they could take the most advantage of the profiler abilities to detect issues and suggest solutions to them), but because of the way they are structured, they weren’t previously detected.

What is the difference between a session and a request?

Note: I am using NHibernate terms here, but naturally this feature is shared among all profiler:

A session is the NHibernate session (or the data/object context in linq to sql / entity framework), and the request is the HTTP request or the WCF operation. If you had code such as the following:

public T GetEntity<T>(int id)
{
    using (var session = sessionFactory.OpenSession())
    {
         return session.Get<T>(id);
    }
}

This code is bad, it micro manages the session, it uses too many connections to the database, it … well, you get the point. The problem is that code that uses this code:

public IEnumerable<Friends> GetFriends(int[] friends)
{
   var results = new List<Friends>();
   foreach(var id in friends)
       results.Add(GetEnttiy<Friend>(id));

   return results;
}

The code above would look like the following in the profiler:

Image1

As you can see, each call is in a separate session, and previously, we wouldn’t have been able to detect that you have too many calls (because each call is a separate session).

Now, however, we will alert the user with a too many database calls in the same request alerts.

Image2

New Uber Prof Concept: Cross Session Alerts

We have recently been doing some work on Uber Prof, mostly in the sense of a code review, and I wanted to demonstrate how easy it was to add a new feature. The problem is that we couldn’t really think of a nice feature to add that we didn’t already have.

Then we started thinking about features that aren’t there and that there wasn’t anything in Uber Prof to enable, and we reached the conclusion that one limitation we have right now is the inability to analyze your application’s behavior beyond the session’s level. But there is actually a whole set of bad practices that are there when you are using multiple sessions.

That led to the creation of a new concept the Cross Session Alert, unlike the alerts we had so far, those alerts looks at the data stream with a much broader scope, and they can analyze and detect issues that we previously couldn’t detect.

I am going to be posting extensively on some of the new features in just a bit, but in the meantime, why don’t you tell me what sort of features do you think this new concept is enabling.

And just a reminder, my architecture is based around Concepts & Features.

Uber Prof New Features: A better query plan

Originally posted at 1/7/2011

Because I keep getting asked, this feature is available for the following profilers:

This feature is actually two separate ones. The first is the profiler detecting what is the most expensive part of the query plan and making it instantly visible. As you can see, in this fairly complex query, it is this select statement that is the hot spot.

image

Another interesting feature that only crops up whenever we are dealing with complex query plans is that the query plan can get big. And by that I mean really big. Too big for a single screen.

Therefore, we added zooming capabilities as well as the mini map that you see in the top right corner.

Uber Prof New Features: Go To Session from alert

Originally posted at 1/7/2011

This is another oft requested feature that we just implemented. The new feature is available for the full suite of Uber Profilers:

You can see the new feature below:

image

I think it is cute, and was surprisingly easy to do.

Uber Prof have recently passed the stage where it is mostly implemented using itself, so I just had to wire a few things together, and then I spent most of the time just making sure that things aligned correctly on the UI.

What is Uber Prof’s competitive advantage?

Originally posted at 11/25/2010

In a recent post, I discussed the notion of competitive advantage and how you should play around them. In this post, I am going to focus on Uber Prof. Just to clarify, when I am talking about Uber Prof, I am talking about NHibernate Profiler, Entity Framework Profiler, Linq to SQL Profiler, Hibernate Profiler and LLBLGen Profiler. Uber Prof is just a handle for me to use to talk about each of those.

So, what is the major competitive advantage that I see in the Uber Prof line of products?

Put very simply, they focus very heavily on the developer’s point of view.

Other profilers will give you the SQL that is being executed, but Uber Prof will show you the SQL and:

  • Format that SQL in a way that make it easy to read.
  • Group the SQL statements into sessions. Which let the developer look at what is going on in the natural boundary.
  • Associate each query with the exact line of code that executed it.
  • Provide the developer with guidance about improving their code.

There are other stuff, of course, but those are the core features that make Uber Prof into what it is.

Profiler new features: Data binding alerts

The following features apply to NHProf, EFProf, L2SProf.

In general, it is strong discouraged to data bind directly to an IQueryable. Mostly, that is because data binding may actually iterate over the IQueryable several times, resulting in multiple queries being generated from something that can be done purely in memory. Worse, it is actually pretty common for data binding to result in lazy loading, and lazy loading from data binding almost always result in SELECT N+1. The profiler can now detect and warn you about such mistakes preemptively. More than that, the profiler can also now detect queries that are being generated from the views in an ASP.Net MVC application, another bad practice that I don’t like.

You can find more information about each warnings here:

WPF detection:

image

 

image

WinForms detections:

image

image

Web applications:

image

image

Entity Framework: If you pass a connection to the context, you are responsible for disposing it

I recently got a bug report about an issue with EF Prof, apparently the application statistics would record the context being opened, but it wouldn’t show them being closed. The customer was rightfully worried about that, and wanted to know if this is a bug in his code or in EF Prof.

Not closing connections is a pretty bad idea, obviously, because you are going to hold a lot more server resources than you need.

But we couldn’t figure out what the problem was. On my end, I could see that EF Prof was recording the context close properly, but nearly the same code on the customer showed the problem. Note the word nearly. I asked for a repro of the issue, and once I had it, it took mere minutes to confirm that the problem exists.

Now was the time to find out why. The customer code was:

using(var context = new MyContext(new EntityConnection("name=MyConStr"))
{
   // do stuff
}

Now, what EF Prof tells you is a data context open & close are actually more accurately connection open & close. After some extensive study, I verified that it wasn’t my code to blame, there was a leaking connection here.

Checking a bit further, it became clear. We passed an existing connection to the context. When we dispose the context, the context asks: “Am I the owner of this connection?” And since the answer is no, it will not dispose it. It makes sense, you might want to use that connection for your own purposes, and it is pretty rude of the context to close a connection that it doesn’t own.

How can we resolve this? By giving the context the information to open the connection, but not the connection itself:

using(var context = new MyContext("name=MyConStr")
{
   // do stuff
}

This code will instruct the context to open its own connection, which it will know that it owns, so it can safely dispose it when it is disposed.

Look Ma, no leaks!

Tags:

Published at

Originally posted at

Comments (5)

Profiler new features, Sept Edition

The following features apply to NHProf, EFProf, HProf, L2SProf.

The first feature is something that was frequently requested, but we kept deferring. Not because it was hard, but because it was tedious and we had cooler features to implement: Sorting.

image

Yep. Plain old sorting for all the grids in the application.

Not an exciting feature, I’ll admit, but an important one.

The feature that gets me exciting is the Go To Session. Let us take the Expensive Queries report as a great example for this feature:

image

As you can see, we have a very expensive query. Let us ignore the reason it is expensive, and assume that we aren’t sure about that.

The problem with the reports feature in the profiler is that while it exposes a lot of information (expensive queries, most common queries, etc), it also lose the context of where this query is running. That is why you can, in any of the reports, right click on a statement and go directly to the session where it originated from:

image

image

We bring the context back to the intelligence that we provide.

What happen if we have a statement that appear in several sessions?

image

You can select each session that this statement appears in, getting back the context of the statement and finding out a lot more about it.

I am very happy about this feature, because I think that it closes a circle with regards to the reports. The reports allows you to pull out a lot of data across you entire application, and the Go To Session feature allows you to connect the interesting pieces of the data back to originating session, giving you where and why this statement was issued.

Estimates sucks, especially when I pay for them

I recently got an estimate for a feature that I wanted to add to NH Prof. It was for two separate features, actually, but they were closely related.

That estimate was for 32 hours.

And it caused me a great deal of indigestion. The problem was, quite simply, that even granting that there is the usual padding of estimates (which I expect), that timing estimate was off, way off. I knew what would be required for this feature, and it shouldn’t be anywhere near complicated enough to require 4 days of full time work. In fact, I estimated that it would take me a maximum of 6 hours and a probable of 3 hours to get it done.

Now, to be fair, I know the codebase (well, actually that isn’t true, a lot of the code for NH Prof was written by Rob & Christopher, and after a few days of working with them, I stopped looking at the code, there wasn’t any need to do so). And I am well aware that most people consider me to be an above average developer.

I wouldn’t have batted an eye for an estimate of 8 – 14 hours, probably. Part of the reason that I have other people working on the code base is that even though I can do certain things faster, I can only do so many things, after all.

But a time estimate that was 5 – 10 times as large as what I estimated was too annoying. I decided that this feature I am going to do on my own. And I decided that I wanted to do this on the clock.

The result is here:

image

This is actually total time over three separate sittings, but the timing is close enough to what I though it would be.

This includes everything, implementing the feature, unit testing it, wiring it up in the UI, etc.

The only thing remaining is to add the UI works for the other profilers (Entity Framework, Linq to SQL, Hibernate and the upcoming LLBLGen Profiler) . Doing this now…

image

And we are done.

I have more features that I want to implement, but in general, if I pushed those changes out, they would be a new release that customers can use immediately.

Nitpicker corner: No, I am not being ripped off. And no, the people making the estimates aren't incompetent. To be perfectly honest, looking at the work that they did do and the time marked against it, they are good, and they deliver in a reasonable time frame. What I think is going on is that their estimates are highly conservative, because they don't want to get into a bind with "oh, we run into a problem with XYZ and overrun the time for the feature by 150%".

That also lead to a different problem, when you pay by the hour, you really want to have estimates that are more or less reasonably tied to your payments. But estimating with hours has too much granularity to be really effective (a single bug can easily consume hours, totally throwing off estimation, and it doesn't even have to be a complex one.)

The Profiler New Features: Starring & Renaming

An interesting thing happened recently, when I started to build the profiler, a lot of the features were what I call Core Features. Those were the things that without which, we wouldn’t have a product. Things like detecting SQL, merging it into sessions, providing reports, etc. What I find myself doing recently with the profiler is not so much building Core Features, but building UX features. In other words, now that we have this in place, let us see how we can make better use of this.

Case in point, the new features that were just released in build 713. They aren’t big, but they are there to improve how people are commonly using the products.

Renaming a session:

image

This is primarily useful if you are in a long profiling session and you want to mark a specific session with some notation:

image

Small feature, and individually not very useful. But you might have noticed that the sessions are marked with stars around them. They weren’t there is previous builds, so what are they?

image

They are a way to tell the profiler that you really like those sessions :-)

More to the point, such sessions will not be removed when you clear the current state. That lets you keep around the previous state of the application as a base line while you work to improve it. Beside, it makes it much easier to locate them visually.

And finally, as a quicker way to do that, you can just ask the profiler to clear all but the selected features.

image

Not big features, but nice ones, I think.

Analyzing LightSwitch data access behavior

I thought it would be a good idea to see what sort of data access behavior LightSwitch applications have. So I hook it up with the EntityFramework Profiler and took it for a spin.

It is interesting to note that it seems that every operation that is running is running in the context of a distributed transaction:

image

There is a time & place to use DTC, but in general, you should avoid them until you really need them. I assume that this is something that is actually being triggered by WCF behavior, not intentional.

Now, let us look at what a simple search looks like:

image

This search results in:

image

That sound? Yes, the one that you just heard. That is the sound of a DBA somewhere expiring. The presentation about LightSwitch touted how you can search every field. And you certainly can. You can also swim across the English channel, but I found that taking the train seems to be an easier way to go about doing this.

Doing this sort of searching is going to be:

  • Very expensive once you have any reasonable amount of data.
  • Prevent usage of indexes to optimize performance.

In other words, this is an extremely brute force approach for this, and it is going to be pretty bad from performance perspective.

Interestingly, it seems that LS is using optimistic concurrency by default.

image

I wonder why they use the slowest method possible for this, instead of using version numbers.

Now, let see how it handles references. I think that I run into something which is a problem, consider:

image

Which generates:

image

This make sense only if you can think of the underlying data model. It certainly seems backward to me.

I fixed that, and created four animals, each as the parent of the other:

image

Which is nice, except that here is the SQL required to generate this screen:

-- statement #1
SELECT [GroupBy1].[A1] AS [C1]
FROM   (SELECT COUNT(1) AS [A1]
        FROM   [dbo].[AnimalsSet] AS [Extent1]) AS [GroupBy1]

-- statement #2
SELECT   TOP ( 45 ) [Extent1].[Id]              AS [Id],
                    [Extent1].[Name]            AS [Name],
                    [Extent1].[DateOfBirth]     AS [DateOfBirth],
                    [Extent1].[Species]         AS [Species],
                    [Extent1].[Color]           AS [Color],
                    [Extent1].[Pic]             AS [Pic],
                    [Extent1].[Animals_Animals] AS [Animals_Animals]
FROM     (SELECT [Extent1].[Id]                      AS [Id],
                 [Extent1].[Name]                    AS [Name],
                 [Extent1].[DateOfBirth]             AS [DateOfBirth],
                 [Extent1].[Species]                 AS [Species],
                 [Extent1].[Color]                   AS [Color],
                 [Extent1].[Pic]                     AS [Pic],
                 [Extent1].[Animals_Animals]         AS [Animals_Animals],
                 row_number()
                   OVER(ORDER BY [Extent1].[Id] ASC) AS [row_number]
          FROM   [dbo].[AnimalsSet] AS [Extent1]) AS [Extent1]
WHERE    [Extent1].[row_number] > 0
ORDER BY [Extent1].[Id] ASC

-- statement #3
SELECT [Extent1].[Id]              AS [Id],
       [Extent1].[Name]            AS [Name],
       [Extent1].[DateOfBirth]     AS [DateOfBirth],
       [Extent1].[Species]         AS [Species],
       [Extent1].[Color]           AS [Color],
       [Extent1].[Pic]             AS [Pic],
       [Extent1].[Animals_Animals] AS [Animals_Animals]
FROM   [dbo].[AnimalsSet] AS [Extent1]
WHERE  1 = [Extent1].[Id]

-- statement #4
SELECT [Extent1].[Id]              AS [Id],
       [Extent1].[Name]            AS [Name],
       [Extent1].[DateOfBirth]     AS [DateOfBirth],
       [Extent1].[Species]         AS [Species],
       [Extent1].[Color]           AS [Color],
       [Extent1].[Pic]             AS [Pic],
       [Extent1].[Animals_Animals] AS [Animals_Animals]
FROM   [dbo].[AnimalsSet] AS [Extent1]
WHERE  2 = [Extent1].[Id]

-- statement #5
SELECT [Extent1].[Id]              AS [Id],
       [Extent1].[Name]            AS [Name],
       [Extent1].[DateOfBirth]     AS [DateOfBirth],
       [Extent1].[Species]         AS [Species],
       [Extent1].[Color]           AS [Color],
       [Extent1].[Pic]             AS [Pic],
       [Extent1].[Animals_Animals] AS [Animals_Animals]
FROM   [dbo].[AnimalsSet] AS [Extent1]
WHERE  3 = [Extent1].[Id]

I told you that there is a select n+1 builtin into the product, now didn’t I?

Now, to make things just that much worse, it isn’t actually a Select N+1 that you’ll easily recognize. because this doesn’t happen on a single request. Instead, we have a multi tier Select N+1.

image

What is actually happening is that in this case, we make the first request to get the data, then we make an additional web request per returned result to get the data about the parent.

And I think that you’ll have to admit that a Parent->>Children association isn’t something that is out of the ordinary. In typical system, where you may have many associations, this “feature” alone is going to slow the system to a crawl.