LinqToSql Query return time too performance too long

Question

I'm doing a Pretty hefty LinqToSql statement that returns a new object. Due to the amount of SQL Methods (Sum and convert mainly) the SQL is taking a significantly long time to run and therefore loading the web page takes a long time (10-15 seconds). While I could use AJAX or similar with a CSS loader. I'm wondering first if there is a simple way to achieve what i am trying to get from the SQL Database.

I am trying to:

1. Return all users where a given Field is not null
2. Get all of the current items in the opportunities table where the status is 'open' and the Foreign Key matches. (after doing a manual join)
3. Inside those opportunities, store the sum of all monetary values for several fields into my class
4. Get the count of those monetary values.

The Linq Statement itself was a pretty long write, however when turned into SQL it is full of COALESCE and other hefty SQL methods.

Here is my LINQ statement:

 decimal _default = (decimal)0.0000;
            var users = from bio in ctx.tbl_Bios.Where(bio => bio.SLXUID != null)
                      join opp in ctx.slx_Opportunities.Where(opp => opp.STATUS == "open") on bio.SLXUID equals opp.ACCOUNTMANAGERID  into opps
                      select new UserStats{
                          Name = bio.FirstName + " " + bio.SurName,
                          EnquiryMoney = opps.Where(opp => opp.SALESCYCLE == "Enquiry").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          EnquiryNum = opps.Where(opp =>  opp.SALESCYCLE == "Enquiry").Count(),
                          GoingAheadMoney = opps.Where(opp => opp.SALESCYCLE == "Going Ahead").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          GoingAheadNum = opps.Where(opp =>  opp.SALESCYCLE == "Going Ahead").Count(),
                          GoodPotentialMoney = opps.Where(opp => opp.SALESCYCLE == "Good Potential").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          GoodPotentialNum = opps.Where(opp =>  opp.SALESCYCLE == "Good Potential").Count(),
                          LeadMoney = opps.Where(opp => opp.SALESCYCLE == "Lead").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          LeadNum = opps.Where(opp =>  opp.SALESCYCLE == "Lead").Count(),
                          PriceOnlyMoney = opps.Where(opp => opp.SALESCYCLE == "Price Only").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          PriceOnlyNum = opps.Where(opp =>  opp.SALESCYCLE == "Price Only").Count(),
                          ProvisionalMoney = opps.Where(opp => opp.SALESCYCLE == "Provisional").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          ProvisionalNum = opps.Where(opp =>  opp.SALESCYCLE == "Provisional").Count()
                      };

Answer 1

There are multiple things you could do:

1. Filtered Indexes: Depending on the breakdown of records in the Opportunities table around the value 'open', you could create a Filtered Index on 'open'. If you have roughly equal amounts of 'open' and 'closed' (or whatever other values are), then a filtered index would let your TSQL only look at records that have 'open'. A Filtered Index stores only the data that meets the predicate; in this case, anything you'd join on that has a value of 'open'. That way it doesn't have to scan other indexes for records that may have 'open' in them.

2. Summary/Rollup table: Create a Rollup table that has the values you're looking for; in this case you're looking for Sums and counts -- why not create a table that simply has one row that has those counts? You could use a Stored Procedure/Agent job to keep it up to date. If your query permits it, you could also try to create an Indexed View; I'll go into that below. For the summary table; you'd essentially run a Stored Procedure that calculates those fields and updates them periodically (say once every few minutes or once a minute, depending on the load) and writes those results to a new table; this would be your Rollup table. Then your results are as easy as a select statement. This would be very fast, at the cost of the load for calculating those sums every few minutes. Depending on the number of records, that could be problematic.

3. Indexed View: Probably the 'right' way to solve a problem like this, depending on your constraints, and how many rows we're talking about (in my case; I pursued it for a case where there were hundreds of thousands of rows).

Filtered Indexes

You could also create a filtered index (it's a bit of an abuse; but it would work) for each of those states, and then simply when it's summing/ counting, it only needs to rely on the index that matches the state it's looking for.

To create a filtered Index:

CREATE NONCLUSTERED INDEX FI_OpenStatus_Opportunities
    ON dbo.Opportunities (AccountManagerId, Status, ActualAmount)
    WHERE status = 'OPEN';
GO

Likewise for your sums and counts (one per column):

CREATE NONCLUSTERED INDEX FI_SalesCycleEnquiry_Status_Opportunities
    ON dbo.Opportunities (AccountManagerId, Status, SalesCycle, ActualAmount)
    WHERE status = 'OPEN' and SalesCycle = 'Enquiry'

(and so on for the rest).

I'm not saying this is your best idea; but it is an idea. Whether or not it's a good one depends on how it performs in your environment on your workload (something I can't answer).

Indexed View

You could also create an Indexed View that contains this rollup information; this is a little bit more advanced and depends on you.

To do that:

  CREATE VIEW [SalesCycle_Summary] WITH SCHEMABINDING AS
    SELECT AccountManagerID, Status, SUM(ActualAmount) AS MONETARY
    ,COUNT_BIG(Status) as Counts 
FROM [DBO].Opportunities
GROUP BY AccountManagerID, Status
GO


-- Create clustered index on the view; making it an indexed view
CREATE UNIQUE CLUSTERED INDEX IDX_SalesCycle_Summary ON [SalesCycle_Summary] (AccountManagerId);

And then (depending on your set up) you can either join to that Indexed View directly, or include it via a hint (try for the former).

Finally, if none of that works (there are some gotchas around Indexed Views -- I haven't used them in about 6 months, so I don't quite remember the specific issue that bit me), you can always create a CTE and ditch the Linq-To-SQL entirely.

That answer is a bit out of scope (because I've already given two approaches and they require a lot of investigation on your part).

To investigate how these do:

1. Get the Generated SQL from your Linq-To-SQL statement (here's how you do that).

2. Open up SSMS and turn on the following in a query window:

   • SET STATISTICS IO ON
   • SET STATISTICS TIME ON
   • Check the box that says "display actual query plan" and "display estimated query plan"
   • Copy the generated SQL into it; run it.

3. Fix any issues with Indexes before continuing. If you get Missing Index Warnings; investigate them and resolve those, and then re-run the benchmarks.

Those starting numbers are your benchmarks.

• Statistics IO tells you the number of logical and physical reads your query is making (lower is better -- concentrate on the areas where there are a high number of reads first)
• Statistics TIME tells you how much time the query took to run and to display its results to SSMS (make sure to turn SET NOCOUNT ON so you're not affecting the results)
• The Actual Query plan tells you exactly what it's using, what indexes SQL Server thinks you're missing, and other issues like Implicit Conversions or bad statistics that would affect your results. Brent Ozar Unlimited has a great video on the subject, so I won't reproduce the answer here.
• The estimated query plan tells you what SQL Server thinks is going to happen -- those are not always the same as the Actual Query plan -- and you want to be sure to account for the difference in your investigation.

There are no 'easy' answers here; the answer depends on your data, your data usage, as well as what changes you can make to the underlying schema. Once you run this in SSMS, you'll see how much of it is Linq-To-SQL overhead, and how much of it is the query itself.

Answer 2

I made my linq query local earlier in my query, doing a group by and then creating my objects. I was only able to do this due to the small amount of items returned so the server can easily handler them. Anyone else getting a similar problem would be better advised to user George Stocker's Answer

I updated my query to the following:

 var allOpps = ctx.slx_Opportunities.Where(opp => opp.STATUS == "open").GroupBy(opp => opp.SALESCYCLE).ToList();

        var users = ctx.tbl_Bios.Where(bio => bio.SLXUID != null).ToList().Select(bio => new UserStats
        {
            LeadNum = allOpps.Single(group => group.Key == "Lead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            LeadMoney = allOpps.Single(group => group.Key == "Lead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp =>  opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            GoingAheadNum = allOpps.Single(group => group.Key == "Going Ahead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            GoingAheadMoney = allOpps.Single(group => group.Key == "Going Ahead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            EnquiryNum = allOpps.Single(group => group.Key == "Enquiry").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            EnquiryMoney = allOpps.Single(group => group.Key == "Enquiry").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            GoodPotentialNum = allOpps.Single(group => group.Key == "Good Potential").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            GoodPotentialMoney = allOpps.Single(group => group.Key == "Good Potential").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            PriceOnlyNum = allOpps.Single(group => group.Key == "Price Only").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            PriceOnlyMoney = allOpps.Single(group => group.Key == "Price Only").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            ProvisionalNum = allOpps.Single(group => group.Key == "Provisional Booking").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(),
            ProvisionalMoney = allOpps.Single(group => group.Key == "Provisional Booking").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)),
            Name = bio.FirstName + " " + bio.SurName
        }).ToList();