Speed up query to rank team sales by aggregating data from two databases

On my machine it runs in about 60 seconds and about 2-3 minutes live.

The criteria needed are:

• Current month only (from first of month until current day)

• I need the rank of the currently logged in person based on total sales.

-
I have two tables across two databases where this data is to be gathered:

• Database 1: ProPit_User table (this houses the logged in users).

• Database 2: This houses the sales in the Orders table.

-
Both tables will be linked on the SalesRepID column.

• There are around around 352 users in the ProPit_User table.

• There are a lot of orders in the orders table.

This code does what I need it to do; it is just dreadfully slow.

public static int GetNationalRank(int teamId, int CurrentUserId)
{
    List lstProPit_User = new List();
    List lstTeamSales = new List();
    int currentUserRank = 0;

    using (ProsPitEntities ppdb = new ProsPitEntities())
    using (TraegerEntities db = new TraegerEntities())
    {
        lstProPit_User = ppdb.ProPit_User.Where(x => x.SalesRepID != null).ToList();

        foreach (var user in lstProPit_User)
        {

            var strStartDate = DateTime.Now.AddDays(-(DateTime.Now.Day - 1)).ToString("M/d/yyyy");
            var dtStartDate = Convert.ToDateTime(strStartDate);

            var strEndDate = DateTime.Now.AddDays(1).ToString("M/d/yyyy");
            var dtEndDate = Convert.ToDateTime(strEndDate);

            var orders = db.Orders.Where(o => o.DateCompleted >= dtStartDate
                                        && o.DateCompleted  x.TotalSales).FindIndex(x => x.userId == CurrentUserId);

        if (rank == -1)
        {
            currentUserRank = 0;
        }
        else
        {
            // Zero based index need to add one to rank
            currentUserRank = rank + 1;
        }

    }

    return currentUserRank;
}

I have .FindIndex added as an extension method to IEnumerable.

All I really need is to get the rank of the person logged in in compari

Try what's below on for size. The date calculation is moved out of the loop, the TeamSales list capacity is pre-allocated and finally, the two LINQ statements are combined into one and rolled into AddRange rather than the explicit loop.

public static int GetNationalRank(int teamId, int CurrentUserId)
{
    int currentUserRank;
    var startDate = Convert.ToDateTime(DateTime.Now.AddDays(-(DateTime.Now.Day - 1)).ToString("M/d/yyyy"));
    var endDate = Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("M/d/yyyy"));
    List proPitUsers;

    using (var ppdb = new ProsPitEntities())
    {
        proPitUsers = ppdb.ProPit_User.Where(x => x.SalesRepID != null).ToList();
    }

    var teamSales = new List(proPitUsers.Count());

    using (var db = new TraegerEntities())
    {
        teamSales.AddRange(proPitUsers
            .Select(user => new
            {
                user,
                total = db.Orders.Where(o => o.DateCompleted >= startDate
                    && o.DateCompleted  (decimal?)o.OrderTotal).Sum() ?? 00.0M
            })
            .Select(userTotal => new TeamSales { userId = userTotal.user.userID, TotalSales = userTotal.total }));

        var rank = teamSales
            .OrderByDescending(x => x.TotalSales)
            .FindIndex(x => x.userId == CurrentUserId);

        currentUserRank = rank == -1 ? 0 : rank + 1;
    }

    return currentUserRank;
}

StackExchange Code Review Q#51444, answer score: 4