EF7 Slow Performance on Include()

Question

I'm investigating slow performance of the app (the page loads 8+ seconds on Azure), and found out interesting thing.

The following code takes about 0.318 seconds to execute

        var reservations = db.Reservations.Include(r => r.PickUpLocation);
        var reservationsList = reservations.ToList();

But if I load the navigation property manually, it will complete 2.5 times faster (0.128 seconds).

        var reservations = db.Reservations.ToList();
        var locations = db.Locations.ToList();

        foreach (var r in reservations)
        {
            r.PickUpLocation = locations.FirstOrDefault(l => l.ID == r.PickUpLocationID);
        }

For more complicated queries the difference is even higher - up to 20 times (such as 8 seconds vs. 0.8 seconds).

I tried both QueryTrackingBehavior.NoTracking and QueryTrackingBehavior.TrackAll, with no significant effect on performance.

Also I noticed that live Azure is 10x slower then a local machine, and shows higher difference in performance between EF/manual methods.

Is there any way to make EF work faster?

UPDATE: Locations table has 121 rows (6 string columns, 1 boolean and 2 int) Reservations table has 1268 rows

The important part of Reservation model class:

public class Reservation
{
    public int ID { get; set; }

    ...

    [ForeignKey("PickUpLocation")]
    public int PickUpLocationID { get; set; }
    public Location PickUpLocation { get; set; }

    ...
}

Location model class:

public class Location
{
    public int ID { get; set; }

    [StringLength(100)]
    public string Name { get; set; }

    [StringLength(255)]
    public string Address { get; set; }

    [StringLength(25)]
    public string Barangay { get; set; }

    [StringLength(25)]
    public string City { get; set; }

    [StringLength(25)]
    public string Province { get; set; }

    [StringLength(20)]
    public string Country { get; set; }

    [Display(Name = "Service Type")]
    public LocationServiceType? ServiceType { get; set; }

    [Display(Name = "Location Type")]
    [Required]
    public LocationType? LocationType { get; set; }

    public bool IsDeleted { get; set; }
}

When executing the app with verbose logging on, I can see the SQL queries actually performed. EF7 does just a single query:

  Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
  SELECT [r].[ID], [r].[AssignedUnitID], [r].[AssignedUnitThirdParty], [r].[BookingCode], [r].[Comments], [r].[DailyPriceID], [r].[DamageDescription], [r].[DocumentCountry], [r].[DocumentNumber], [r].[DocumentOwnerName], [r].[DocumentType], [r].[Email], [r].[FirstName], [r].[FlightArrival], [r].[FlightNumber], [r].[HomeAddress], [r].[LastName], [r].[Phone], [r].[PickUpDateTime], [r].[PickUpLocationID], [r].[RequestedVehicleModelID], [r].[RequestedVehicleTypeID], [r].[ReturnDateTime], [r].[ReturnLocationID], [r].[State], [r].[StayingAddress], [r].[ThirdPartyName], [r].[Type], [r].[UserId], [r].[VersionDate], [l].[ID], [l].[Address], [l].[Barangay], [l].[City], [l].[Country], [l].[IsDeleted], [l].[LocationType], [l].[Name], [l].[Province], [l].[ServiceType]
  FROM [Reservation] AS [r]
  INNER JOIN [Location] AS [l] ON [r].[PickUpLocationID] = [l].[ID]

So the problem must be in how it parses the result.

Do you verified how many additional data will be included by `.Include` in your case? I personally prefer to constrict explicit SELECT statements with inner join and filling only the field which I really need. I think that you can analyse the problem independent from Azure. The code which you posted don't contains enough details about the model. One can't define which SQL statements will be generated in both cases. Moreover EF7 could have some bugs which you can locale only by examining the generated SQL selects. — Oleg, Feb 03 '16 at 12:54
Oleg, I updated the question to include model classes the SQL query generated by EF7. — Sergey Kandaurov, Feb 03 '16 at 14:02
the query looks OK. I see no reason for 8 seconds or even for 0.8 seconds. It should be *much more* quickly. Do you verified that `ID` is really the primary key? I personally use EF without EF (see [the answer](http://stackoverflow.com/a/34764698/315935) with direct SQL statements). I don't see an error in your code. Do you have the same performance problem with local database? I would suggest you to analyse the problem in local database. You can use SQL Server 2014 Profiler to examine which part spend the 8 seconds. Probably you can use Profiler on Azure too. — Oleg, Feb 03 '16 at 14:53
8 seconds is for much more complex query. I simplified it for performance research. The simplified query (from the post) takes 0.318 seconds (which is 2.5 times slower compared to doing manually). Same problem with local DB. The SQL itself seem to preform fine (4 ms for the query), so the problem should be somewhere inside EF itself. — Sergey Kandaurov, Feb 04 '16 at 05:03
I have the same problem, the T-Sql that is sent to Sql Server in my case, If I run in Sql Server Management Studio it takes 3 seconds. If I run the query with EF7 it takes 5 minutes if I use raw sql with includes. — Álvaro García, Jun 20 '16 at 15:57

EF7 Slow Performance on Include()

0 Answers0