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
- The call to DiffDays from within the LINQ query.
- 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
