3
\$\begingroup\$

I'm wondering what can be done to optimize the following. I've tried to note the points at which most of the overhead is occurring. The main two points I see are

  1. The call to DiffDays from within the LINQ query.
  2. The ordering by DateTime LastOnline before putting results in memory.

Right now, with only 2000 rows, I'm looking at ~1-2 seconds for the results to return, average. Removing DiffDays and Ordering by DateTime cuts this down to about 600ms, but that's still a far cry away from my desired 50ms turnaround.

What are some things I can do to significantly improve the performance of the below code?

My dbContext:

public class DbContext : DbContext {
        public DbContext() : base("name=DefaultConnection") { }
        public DbSet<User> Users { get; set; }
        public DbSet<Request> Requests { get; set; }
        public DbSet<Review> Reviews { get; set; }

        public DbSet<PromoCode> PromoCodes { get; set; }
        public DbSet<Photo> Photos { get; set; }

        public DbSet<Notification> Notifications { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
            modelBuilder.Configurations.Add(new PhotoConfiguration());
            modelBuilder.Configurations.Add(new UserConfiguration());

            base.OnModelCreating(modelBuilder);
        }

    }

    public class PhotoConfiguration : EntityTypeConfiguration<Photo> {
        public PhotoConfiguration() {
            // One-to-Many
            HasRequired(s => s.User).WithMany(s => s.Photos).HasForeignKey(s => s.UserId);
        }
    }

    public class NotificationConfiguration : EntityTypeConfiguration<Notification> {
        public NotificationConfiguration() {
            // One-to-Many
            HasRequired(s => s.User).WithMany(s => s.Notifications).HasForeignKey(s => s.UserId);
        }
    }

    public class UserConfiguration : EntityTypeConfiguration<User> {
        public UserConfiguration() {
            // One-to-Many
            HasMany(s => s.ReceivedRequests).WithRequired(s => s.ToUser).HasForeignKey(s => s.ToUserId);
            HasMany(s => s.SentRequests).WithRequired(s => s.FromUser).HasForeignKey(s => s.FromUserId);
            HasMany(s => s.ReceivedReviews).WithRequired(s => s.ToUser).HasForeignKey(s => s.ToUserId);
            HasMany(s => s.SentReviews).WithRequired(s => s.FromUser).HasForeignKey(s => s.FromUserId);
        }
    }

Slow Code

public ApiResponseDto GetProfiles(string username, ProfileSearchDto query) {
        var count = 10;
        var response = new ApiResponseDto();
        var trackNow = DateTime.UtcNow;
        for (var i = 0; i < count; i++) {

            var profiles = db.Users.Where(s => !s.AccountDisabled);
            var user = profiles.First(s => s.Username.ToLower() == username.ToLower());
            user.LastOnline = DateTime.UtcNow;
            user.SawHello = true;

            // filter by sex
            if (user.SearchSex == SearchSex.Male)
                profiles = profiles.Where(s => s.Sex == Sex.Male);
            else if (user.SearchSex == SearchSex.Female)
                profiles = profiles.Where(s => s.Sex == Sex.Female);
            else if (user.SearchSex == SearchSex.Other)
                profiles = profiles.Where(s => s.Sex == Sex.Other);

            // filter by proximity
            if (user.SearchRadius < 1) {
                user.SearchRadius = 4000;
            }

            var coord = new GeoCoordinate(user.LocationLat, user.LocationLong);
            var distance = user.SearchRadius*1609.34;
            DbGeography referencepoint = DbGeography.PointFromText(string.Format("POINT({0} {1})", coord.Longitude, coord.Latitude), DbGeography.DefaultCoordinateSystemId);
            var closeProfiles = (from r in profiles
                let rLoc =
                    DbGeography.PointFromText("POINT(" + r.LocationLong + " " + r.LocationLat + ")",
                        DbGeography.DefaultCoordinateSystemId)
                where rLoc.Distance(referencepoint) <= distance
                select r);

            var now = DateTime.UtcNow;

            if (user.SearchLowerAge < 1)
                user.SearchLowerAge = 18;

            if (user.SearchUpperAge < 1)
                user.SearchUpperAge = 60;

            // this is adding a ton of overhead - the DiffDays function
            if (user.SearchLowerAge >= 0)
                closeProfiles =
                    closeProfiles.Where(s => DbFunctions.DiffDays(s.Birthdate, now) / 365 > user.SearchLowerAge);
            if (user.SearchLowerAge <= 120 && user.SearchLowerAge > 0)
                closeProfiles =
                    closeProfiles.Where(s => DbFunctions.DiffDays(s.Birthdate, now) / 365 < user.SearchUpperAge);

            // Ordering by LastOnline (Indexed) is almost doubling the time it takes compared to Ordering by UserId (also Indexed)
            var results = closeProfiles.Include(s => s.Photos).OrderByDescending(s => s.LastOnline).Skip(query.Index*40).Take(40).ToList();

            var browsePage = new ProfileSearchDto();

            var dtos = new List<ProfilePreviewDto>();
            DateTime today = DateTime.Today;

            foreach (var userProfile in results) {
                int age = today.Year - userProfile.Birthdate.Year;
                if (userProfile.Birthdate > today.AddYears(-age)) age--;
                var photo = "https://i.imgur.com/XASFOvc.png";
                if (userProfile.Photos.Any()) {
                    var primary = userProfile.Photos.FirstOrDefault(s => s.IsProfilePhoto);
                    photo = primary != null ? primary.Url : userProfile.Photos.First().Url;
                }
                photo = photo.ReplaceLastOccurrence(".", "m.");

                dtos.Add(new ProfilePreviewDto() {
                    Age = age,
                    LastOnline = userProfile.LastOnline,
                    LastOnlineText = userProfile.LastOnline.ToRelativeTime(),
                    Location = userProfile.LocationName,
                    PhotoUrl = photo,
                    Username = userProfile.Username,
                    OnlineNow = DateTime.UtcNow.Subtract(userProfile.LastOnline).TotalMinutes < 30
                });
            }

            response.Data = browsePage;

            db.SaveChanges();
        }
        var msElapsed = DateTime.UtcNow.Subtract(trackNow).TotalMilliseconds / count;
        var a = msElapsed;
        return response;
    }

Generated SQL

Opened connection at 2/15/2015 12:42:09 AM -05:00

SELECT 
    [Project2].[UserId] AS [UserId], 
    [Project2].[Username] AS [Username], 
    [Project2].[AboutMe] AS [AboutMe], 
    [Project2].[LocationName] AS [LocationName], 
    [Project2].[LocationZip] AS [LocationZip], 
    [Project2].[LocationLat] AS [LocationLat], 
    [Project2].[LocationLong] AS [LocationLong], 
    [Project2].[CountryCode] AS [CountryCode], 
    [Project2].[PromoCode] AS [PromoCode], 
    [Project2].[Email] AS [Email], 
    [Project2].[HasConfirmedEmail] AS [HasConfirmedEmail], 
    [Project2].[EmailConfirmationCode] AS [EmailConfirmationCode], 
    [Project2].[EmailUnsubscribeCode] AS [EmailUnsubscribeCode], 
    [Project2].[Orientation] AS [Orientation], 
    [Project2].[Sex] AS [Sex], 
    [Project2].[UserType] AS [UserType], 
    [Project2].[Birthdate] AS [Birthdate], 
    [Project2].[LastOnline] AS [LastOnline], 
    [Project2].[RawNumber] AS [RawNumber], 
    [Project2].[FriendlyNumber] AS [FriendlyNumber], 
    [Project2].[HasConfirmedNumber] AS [HasConfirmedNumber], 
    [Project2].[NumberConfirmationCode] AS [NumberConfirmationCode], 
    [Project2].[Rate] AS [Rate], 
    [Project2].[SawHello] AS [SawHello], 
    [Project2].[ProvidedPhoneNumber] AS [ProvidedPhoneNumber], 
    [Project2].[ProvidedPhoto] AS [ProvidedPhoto], 
    [Project2].[ProvidedAboutMe] AS [ProvidedAboutMe], 
    [Project2].[ProvidedRate] AS [ProvidedRate], 
    [Project2].[SendTextWhenRequestReceived] AS [SendTextWhenRequestReceived], 
    [Project2].[SendEmailWhenRequestReceived] AS [SendEmailWhenRequestReceived], 
    [Project2].[SendEmailWhenReviewPosted] AS [SendEmailWhenReviewPosted], 
    [Project2].[SendTextWhenRequestApproved] AS [SendTextWhenRequestApproved], 
    [Project2].[SendEmailWhenRequestApproved] AS [SendEmailWhenRequestApproved], 
    [Project2].[SendTextOtherNotifications] AS [SendTextOtherNotifications], 
    [Project2].[SendEmailOtherNotifications] AS [SendEmailOtherNotifications], 
    [Project2].[SearchInterest] AS [SearchInterest], 
    [Project2].[SearchSex] AS [SearchSex], 
    [Project2].[SearchType] AS [SearchType], 
    [Project2].[SearchRadius] AS [SearchRadius], 
    [Project2].[SearchLowerAge] AS [SearchLowerAge], 
    [Project2].[SearchUpperAge] AS [SearchUpperAge], 
    [Project2].[SearchMaxRate] AS [SearchMaxRate], 
    [Project2].[SearchMinRating] AS [SearchMinRating], 
    [Project2].[AccountDisabled] AS [AccountDisabled], 
    [Project2].[C1] AS [C1], 
    [Project2].[PhotoId] AS [PhotoId], 
    [Project2].[Url] AS [Url], 
    [Project2].[IsProfilePhoto] AS [IsProfilePhoto], 
    [Project2].[UserId1] AS [UserId1]
    FROM ( SELECT 
        [Limit1].[UserId] AS [UserId], 
        [Limit1].[Username] AS [Username], 
        [Limit1].[AboutMe] AS [AboutMe], 
        [Limit1].[LocationName] AS [LocationName], 
        [Limit1].[LocationZip] AS [LocationZip], 
        [Limit1].[LocationLat] AS [LocationLat], 
        [Limit1].[LocationLong] AS [LocationLong], 
        [Limit1].[CountryCode] AS [CountryCode], 
        [Limit1].[PromoCode] AS [PromoCode], 
        [Limit1].[Email] AS [Email], 
        [Limit1].[HasConfirmedEmail] AS [HasConfirmedEmail], 
        [Limit1].[EmailConfirmationCode] AS [EmailConfirmationCode], 
        [Limit1].[EmailUnsubscribeCode] AS [EmailUnsubscribeCode], 
        [Limit1].[Orientation] AS [Orientation], 
        [Limit1].[Sex] AS [Sex], 
        [Limit1].[UserType] AS [UserType], 
        [Limit1].[Birthdate] AS [Birthdate], 
        [Limit1].[LastOnline] AS [LastOnline], 
        [Limit1].[RawNumber] AS [RawNumber], 
        [Limit1].[FriendlyNumber] AS [FriendlyNumber], 
        [Limit1].[HasConfirmedNumber] AS [HasConfirmedNumber], 
        [Limit1].[NumberConfirmationCode] AS [NumberConfirmationCode], 
        [Limit1].[Rate] AS [Rate], 
        [Limit1].[SawHello] AS [SawHello], 
        [Limit1].[ProvidedPhoneNumber] AS [ProvidedPhoneNumber], 
        [Limit1].[ProvidedPhoto] AS [ProvidedPhoto], 
        [Limit1].[ProvidedAboutMe] AS [ProvidedAboutMe], 
        [Limit1].[ProvidedRate] AS [ProvidedRate], 
        [Limit1].[SendTextWhenRequestReceived] AS [SendTextWhenRequestReceived], 
        [Limit1].[SendEmailWhenRequestReceived] AS [SendEmailWhenRequestReceived], 
        [Limit1].[SendEmailWhenReviewPosted] AS [SendEmailWhenReviewPosted], 
        [Limit1].[SendTextWhenRequestApproved] AS [SendTextWhenRequestApproved], 
        [Limit1].[SendEmailWhenRequestApproved] AS [SendEmailWhenRequestApproved], 
        [Limit1].[SendTextOtherNotifications] AS [SendTextOtherNotifications], 
        [Limit1].[SendEmailOtherNotifications] AS [SendEmailOtherNotifications], 
        [Limit1].[SearchInterest] AS [SearchInterest], 
        [Limit1].[SearchSex] AS [SearchSex], 
        [Limit1].[SearchType] AS [SearchType], 
        [Limit1].[SearchRadius] AS [SearchRadius], 
        [Limit1].[SearchLowerAge] AS [SearchLowerAge], 
        [Limit1].[SearchUpperAge] AS [SearchUpperAge], 
        [Limit1].[SearchMaxRate] AS [SearchMaxRate], 
        [Limit1].[SearchMinRating] AS [SearchMinRating], 
        [Limit1].[AccountDisabled] AS [AccountDisabled], 
        [Extent2].[PhotoId] AS [PhotoId], 
        [Extent2].[Url] AS [Url], 
        [Extent2].[IsProfilePhoto] AS [IsProfilePhoto], 
        [Extent2].[UserId] AS [UserId1], 
        CASE WHEN ([Extent2].[PhotoId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT [Project1].[UserId] AS [UserId], [Project1].[Username] AS [Username], [Project1].[AboutMe] AS [AboutMe], [Project1].[LocationName] AS [LocationName], [Project1].[LocationZip] AS [LocationZip], [Project1].[LocationLat] AS [LocationLat], [Project1].[LocationLong] AS [LocationLong], [Project1].[CountryCode] AS [CountryCode], [Project1].[PromoCode] AS [PromoCode], [Project1].[Email] AS [Email], [Project1].[HasConfirmedEmail] AS [HasConfirmedEmail], [Project1].[EmailConfirmationCode] AS [EmailConfirmationCode], [Project1].[EmailUnsubscribeCode] AS [EmailUnsubscribeCode], [Project1].[Orientation] AS [Orientation], [Project1].[Sex] AS [Sex], [Project1].[UserType] AS [UserType], [Project1].[Birthdate] AS [Birthdate], [Project1].[LastOnline] AS [LastOnline], [Project1].[RawNumber] AS [RawNumber], [Project1].[FriendlyNumber] AS [FriendlyNumber], [Project1].[HasConfirmedNumber] AS [HasConfirmedNumber], [Project1].[NumberConfirmationCode] AS [NumberConfirmationCode], [Project1].[Rate] AS [Rate], [Project1].[SawHello] AS [SawHello], [Project1].[ProvidedPhoneNumber] AS [ProvidedPhoneNumber], [Project1].[ProvidedPhoto] AS [ProvidedPhoto], [Project1].[ProvidedAboutMe] AS [ProvidedAboutMe], [Project1].[ProvidedRate] AS [ProvidedRate], [Project1].[SendTextWhenRequestReceived] AS [SendTextWhenRequestReceived], [Project1].[SendEmailWhenRequestReceived] AS [SendEmailWhenRequestReceived], [Project1].[SendEmailWhenReviewPosted] AS [SendEmailWhenReviewPosted], [Project1].[SendTextWhenRequestApproved] AS [SendTextWhenRequestApproved], [Project1].[SendEmailWhenRequestApproved] AS [SendEmailWhenRequestApproved], [Project1].[SendTextOtherNotifications] AS [SendTextOtherNotifications], [Project1].[SendEmailOtherNotifications] AS [SendEmailOtherNotifications], [Project1].[SearchInterest] AS [SearchInterest], [Project1].[SearchSex] AS [SearchSex], [Project1].[SearchType] AS [SearchType], [Project1].[SearchRadius] AS [SearchRadius], [Project1].[SearchLowerAge] AS [SearchLowerAge], [Project1].[SearchUpperAge] AS [SearchUpperAge], [Project1].[SearchMaxRate] AS [SearchMaxRate], [Project1].[SearchMinRating] AS [SearchMinRating], [Project1].[AccountDisabled] AS [AccountDisabled]
            FROM ( SELECT 
                [Extent1].[UserId] AS [UserId], 
                [Extent1].[Username] AS [Username], 
                [Extent1].[AboutMe] AS [AboutMe], 
                [Extent1].[LocationName] AS [LocationName], 
                [Extent1].[LocationZip] AS [LocationZip], 
                [Extent1].[LocationLat] AS [LocationLat], 
                [Extent1].[LocationLong] AS [LocationLong], 
                [Extent1].[CountryCode] AS [CountryCode], 
                [Extent1].[PromoCode] AS [PromoCode], 
                [Extent1].[Email] AS [Email], 
                [Extent1].[HasConfirmedEmail] AS [HasConfirmedEmail], 
                [Extent1].[EmailConfirmationCode] AS [EmailConfirmationCode], 
                [Extent1].[EmailUnsubscribeCode] AS [EmailUnsubscribeCode], 
                [Extent1].[Orientation] AS [Orientation], 
                [Extent1].[Sex] AS [Sex], 
                [Extent1].[UserType] AS [UserType], 
                [Extent1].[Birthdate] AS [Birthdate], 
                [Extent1].[LastOnline] AS [LastOnline], 
                [Extent1].[RawNumber] AS [RawNumber], 
                [Extent1].[FriendlyNumber] AS [FriendlyNumber], 
                [Extent1].[HasConfirmedNumber] AS [HasConfirmedNumber], 
                [Extent1].[NumberConfirmationCode] AS [NumberConfirmationCode], 
                [Extent1].[Rate] AS [Rate], 
                [Extent1].[SawHello] AS [SawHello], 
                [Extent1].[ProvidedPhoneNumber] AS [ProvidedPhoneNumber], 
                [Extent1].[ProvidedPhoto] AS [ProvidedPhoto], 
                [Extent1].[ProvidedAboutMe] AS [ProvidedAboutMe], 
                [Extent1].[ProvidedRate] AS [ProvidedRate], 
                [Extent1].[SendTextWhenRequestReceived] AS [SendTextWhenRequestReceived], 
                [Extent1].[SendEmailWhenRequestReceived] AS [SendEmailWhenRequestReceived], 
                [Extent1].[SendEmailWhenReviewPosted] AS [SendEmailWhenReviewPosted], 
                [Extent1].[SendTextWhenRequestApproved] AS [SendTextWhenRequestApproved], 
                [Extent1].[SendEmailWhenRequestApproved] AS [SendEmailWhenRequestApproved], 
                [Extent1].[SendTextOtherNotifications] AS [SendTextOtherNotifications], 
                [Extent1].[SendEmailOtherNotifications] AS [SendEmailOtherNotifications], 
                [Extent1].[SearchInterest] AS [SearchInterest], 
                [Extent1].[SearchSex] AS [SearchSex], 
                [Extent1].[SearchType] AS [SearchType], 
                [Extent1].[SearchRadius] AS [SearchRadius], 
                [Extent1].[SearchLowerAge] AS [SearchLowerAge], 
                [Extent1].[SearchUpperAge] AS [SearchUpperAge], 
                [Extent1].[SearchMaxRate] AS [SearchMaxRate], 
                [Extent1].[SearchMinRating] AS [SearchMinRating], 
                [Extent1].[AccountDisabled] AS [AccountDisabled]
                FROM [dbo].[User] AS [Extent1]
                WHERE ([Extent1].[AccountDisabled] <> 1) AND ([Extent1].[Rate] < (@p__linq__0 + 1))
            )  AS [Project1]
            ORDER BY [Project1].[LastOnline] DESC
            OFFSET 40 ROWS FETCH NEXT 40 ROWS ONLY  ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[Photo] AS [Extent2] ON [Limit1].[UserId] = [Extent2].[UserId]
    )  AS [Project2]
    ORDER BY [Project2].[LastOnline] DESC, [Project2].[UserId] ASC, [Project2].[C1] ASC


-- p__linq__0: '500' (Type = Int32, IsNullable = false)

-- Executing at 2/15/2015 12:42:09 AM -05:00

-- Completed in 129 ms with result: SqlDataReader



Closed connection at 2/15/2015 12:42:09 AM -05:00

Opened connection at 2/15/2015 12:42:09 AM -05:00

Started transaction at 2/15/2015 12:42:09 AM -05:00

UPDATE [dbo].[User] SET [LastOnline] = @0 WHERE ([UserId] = @1)

-- @0: '2/15/2015 5:42:09 AM' (Type = DateTime2)

-- @1: '1' (Type = Int32)

-- Executing at 2/15/2015 12:42:09 AM -05:00

-- Completed in 160 ms with result: 1
\$\endgroup\$
  • \$\begingroup\$ Can you provide a sample database schema including indexes? \$\endgroup\$ – kerem Feb 19 '15 at 14:55
2
\$\begingroup\$

In a nutshell, and I could be misreading here... but you have a for loop that iterates 10 times. Each of those iterations makes a database call using the same parameters. I'm not sure what this could be accomplishing.

My recommendations would be:

  • Don't declare any variables within the for loop that you don't have to have for that specific loop. Each time you do that, you're having to allocate a piece of memory which makes it that much slower (even if it's a fraction of a millisecond).

  • Get the oldest possible value for the lower search range, and the youngest possible value for the upper search range - in date form - and then do a search on birthdate being between those two dates. You won't be causing the database to have to do business logic then.

  • Include the photos when you make the initial call, then later do something like results.Where(r => !r.Photos.Any()).Select(r => r.Photo = myDefaultPhoto).ToList();

  • Once you've got a dataset to work with, having filtered out all the entries that you KNOW aren't acceptable results, then you can call the orderby the smaller set which should be a bit more performant.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.