3

I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created.

I am trying to join the Customer_Snacks table so that I can get the Snack Quantity for each customer's order. The issue I'm having is when I do a LEFT OUTER JOIN which, as far as I can tell, should work, some of the other columns data is drastically changed.

I managed to get it to work the way I would like it to by using a subquery but it is DREADFULLY slow. The query takes over a minute to run and in return, the application that is making the call for that view times out.

Here is what I currently have that works (I know, it's messy):

SELECT
    cl.CustomerID,
    cl.FirstName + ' ' + c.LastName as CustomerDisplay,
    cl.InvoiceID,
    cl.MealPlanID,
    mp.DeliveryDate1 as DeliveryDate,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity ELSE 0 END) as RegularQuantity,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity * Amount ELSE 0 END) as RegularTotal,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity ELSE 0 END) as ProteinQuantity,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity * Amount ELSE 0 END) as ProteinTotal,
    SUM(CASE WHEN cll.Name = 'TAX' THEN Amount ELSE 0 END) as TotalTax,
    SUM(CASE WHEN cll.Name = 'SNACKS' THEN Amount ELSE 0 END) AS SnackTotal,
    -- Here is my subquery that I would like to integrate into this view with a join so that it's much faster
    (SELECT SUM(cs.Quantity) FROM Fustomer_Snacks as cs WHERE cs.CustomerID = cl.CustomerID and cs.DeliveryDate = mp.DeliveryDate1) as SnackQuantity,
    ------------------------------------------------------------------
    CAST(SUM((CASE WHEN cll.Name = 'home' or cll.Name = 'work' THEN 1 ELSE 0 END)) as bit) as IsDelivery,
    SUM(CASE WHEN (cll.Name = 'home' or cll.Name = 'work' or cll.Name = 'pickup') and cll.Quantity > 0 THEN cll.Quantity * Amount else 0 END) as DeliveryCharge,
    cl.Balance as PrePromoTotal,
    ISNULL(cl.Balance * (pc.Discount * .01), 0) * -1 as PromoDiscount,
    cl.FinalBalance - ISNULL((cl.FinalBalance * (pc.Discount * .01)),0) Total
FROM
    Customer_Ledger as cl
INNER JOIN
    Customer_Ledger_LineItems as cll
        on cl.ID = cll.LedgerID
INNER JOIN
    MealPlan as mp
        on cl.MealPlanID = mp.ID
INNER JOIN
    Customer as c
        on cl.CustomerId = c.Id
LEFT OUTER JOIN
    PromoCode as pc
        on cl.BalancePromoId = pc.Id
GROUP BY
    cl.CustomerID,
    c.FirstName,
    c.LastName,
    MealPlanID,
    InvoiceID,
    mp.DeliveryDate1,
    cl.Balance,
    cl.FinalBalance,
    pc.Discount

Like I said, the way I currently have it, it works but it's way too slow. As soon as I try a LEFT OUTER JOIN it's a lot faster but other columns like RegularQuantity become inflated with other numbers (and I'm not sure how that's even happening).

If anyone can guide me in the right direction, that would be great!

  • 3
    Which table is the Amount from? – ypercubeᵀᴹ Jun 22 '17 at 19:43
  • 4
    Can you add the execution plan? – ypercubeᵀᴹ Jun 22 '17 at 19:43
  • 4
    "As soon as I try a LEFT OUTER JOIN it's a lot faster but ..." As soon as you try a join to which table? A different one, not in the query you show? – ypercubeᵀᴹ Jun 22 '17 at 19:44
8

The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks and Customer_Ledger_LineItems.

When the DB engine performs a join, it matches however many rows in the first table with value ABC with however many rows in the second table have the same value, ABC (outer joins working a little differently, but let's ignore that for the moment).

I'll guess that for a given Customer_Ledger row, you tie to one Customer row, one MealPlan row, one (or zero) PromoCode row, and multipleCustomer_Ledger_LineItems` rows.

I'm also guessing for one Customer_Ledger row and its associated MealPlan row, you can bring back multiple Customer_Snacks rows.

So, when we put all the rows together, let's say we have:

  • 1 Customer row
  • 1 Customer_Ledger row
  • 1 MealPlan row
  • 1 PromoCode row
  • 3 Customer_Ledger_LineItems rows

Match all those together, you get 1 * 1 * 1 * 1 * 3 = 3 rows.

Now, add in 2 Customer_Snacks rows. those are matched against all the rows we already have, so we get 1 * 1 * 1 * 1 * 3 * 2 = 6 rows.

When we go to take our sums, we'll get double the amount for sums from Customer_Ledger_LineItems rows, because each row now shows up twice. And, we'll get triple the amount for the sum from Customer_Snacks, because those rows each now show up three times. Basically, you're getting a CROSS JOIN between the relevant Customer_Ledger_LineItems rows, and the Customer_Snacks rows.

There are two ways to avoid this. You already employing the first option - use a subquery to get the sums from one of the tables returning multiple rows to our grouped values.

The other option is very similar: build a temporary table (or a CTE) with all relevant values that you need, so that you'll just have one row with the sums you need when you go to join the data in. In your case, this would look like:

;WITH SnackSums AS
      (SELECT cs.CustomerID
             ,cs.DeliveryDate
             ,SUM(cs.Quantity) as SnackQuantity
         FROM Customer_Snacks cs
        GROUP BY cs.CustomerID, cs.DeliveryDate
      )

and would go right before the SELECT for your query. Then, you just put ss.SnackQuantity where the subquery was, and add:

LEFT OUTER JOIN
  SnackSums ss
    ON (cl.CustomerId = ss.CustomerId AND ss.DeliveryDate = mp.DeliveryDate1)

right before your GROUP BY. Finally, you'll also need to add ss.SnackQuantity to the GROUP BY, since in the context of your main query this is a stand-alone, non-aggregated value.

Now, I make no promises that this will be faster than the subquery. However, it should be accurate - and it doesn't matter is something is blazing fast, if it isn't accurate.

| improve this answer | |
  • You are awesome. This worked perfectly and took the execution time down from 1:30 to 0:09. Thanks a ton for the thorough explanation as well so I know exactly what's going on and what my issue most likely was. :) – Quiver Jun 22 '17 at 20:58
3

I can't comment on your question, because I have low reputation (newbie here). My guess is that with the left join you are joining rows with a default value or something like that, witch is messing with the sums.

You could, in my opinion, stick with what you just did, but create a Index on the fields you are searching to optimize your query.

| improve this answer | |

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.