Merge these two queries into a single query

Question

I have the following queries:

SELECT Sites.EDISID, Sites.[Name], (SUM(DLData.Quantity) / 8) AS TiedDispense
FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 JOIN DLData
  ON DLData.DownloadID = MasterDates.[ID]
 JOIN Products 
  ON Products.[ID] = DLData.Product
 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
 GROUP BY Sites.EDISID, Sites.[Name]

SELECT Sites.EDISID, Sites.[Name], SUM(Delivery.Quantity) AS TiedDelivered
FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 JOIN Delivery
  ON Delivery.DeliveryID = MasterDates.[ID]
 JOIN Products 
  ON Products.[ID] = Delivery.Product
 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
 GROUP BY Sites.EDISID, Sites.[Name]

As you can see they are very similar - only the lines regarding whether the query is for DLData or Delivery are different. One returns the total delivered the other returns the total dispensed.

Currently I am using them as two separate sub-queries in a third query. Singly they take approximately 1-2 seconds each. As two subqueries they are taking between 6 and 10 seconds (depending on load) and both return just 47 rows (though they are touching thousands of rows total).

I was thinking that combining them will give me a decent speed up - especially as this query will be called a lot.

However my attempts have failed as the number of rows change when I try to combine the two. I have tried various JOIN combinations but nothing returns the correct results.

Do the SO'ers have any suggestions?

Answer 1

Without wanting to sound overly stupid, but I guess a union is not going to help (would require a small change to a returned column name...)?

Answer 2

you could try:

SELECT Sites.EDISID, 
 Sites.[Name], 
 (SUM(DLData.Quantity) / 8) AS TiedDispense, 
 SUM(Delivery.Quantity) AS TiedDelivered
FROM Sites  
JOIN UserSites     
 ON UserSites.EDISID = Sites.EDISID  
JOIN Users    
 ON Users.[ID] = UserSites.UserID 
JOIN MasterDates   
 ON MasterDates.EDISID = UserSites.EDISID 
JOIN DLData  
 ON DLData.DownloadID = MasterDates.[ID] 
JOIN Products   
 ON Products.[ID] = DLData.Product
LEFT JOIN Delivery  
 ON Delivery.DeliveryID = MasterDates.[ID] 
LEFT JOIN SiteProductTies   
 ON SiteProductTies.EDISID = UserSites.EDISID  AND SiteProductTies.ProductID = Products.[ID] 
LEFT JOIN SiteProductCategoryTies   
 ON SiteProductCategoryTies.EDISID = UserSites.EDISID  
 AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID 
WHERE Users.[ID] = @UserID  
 AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied 
  OR @Tied IS NULL)  
 AND MasterDates.[Date] BETWEEN @From AND @To  
 AND MasterDates.[Date] >= Sites.SiteOnline 
GROUP BY Sites.EDISID, Sites.[Name]

I did a left join but an Inner join might work depending on your data. I'd also check to make sure that all of those foreign key fields are indexed.

Answer 3

After a quick look at your query, I can't be sure if this is correct without understanding the business rules behind the data. However, you can give this a shot if you'd like:

SELECT 

Sites.EDISID, Sites.[Name], 
CASE WHEN Delivery.DeliveryID IS NULL THEN 0 ELSE SUM(Delivery.Quantity) END TiedDelivered, 
CASE WHEN DLData.[ID] IS NULL THEN 0 ELSE (SUM(DLData.Quantity) / 8) END TiedDispense 

FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 LEFT JOIN Products 
  ON Products.[ID] = DLData.Product

 LEFT JOIN DLData
  ON DLData.DownloadID = MasterDates.[ID]
 LEFT JOIN Delivery
  ON Delivery.DeliveryID = MasterDates.[ID]

 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
  AND (DLData.[DownloadID] IS NOT NULL OR DELIVERY.DeliveryID IS NOT NULL)
 GROUP BY Sites.EDISID, Sites.[Name]

one key to it is this part:

  AND (DLData.[DownloadID] IS NOT NULL OR DELIVERY.DeliveryID IS NOT NULL)

Which is heavily based on assumptions of your business rules but might make up for extra rows returned by the two left joins. You can also play with something like this if you'd like:

AND ( TiedDelivered != 0 AND TiedDispense != 0)

hope this helps.

-steve

Answer 4

I wrote a dumb answer to this and it bugged me so I started looking into it a bit more - basically you want to put the group bys inside the joins. I haven't got time to edit your code but I think this example should get you there:

create table #prod(
prodid int,
prodamount int)

create table #del(
delid int,
delamount int)

create table #main(
id int,
name varchar(50))

insert into #main(id,name)
select 1, 'test 1'
union select 2, 'test 2'
union select 3, 'test 3'
union select 4, 'test 4'

insert into #prod(prodid,prodamount)
select 1, 10
union select 1, 20
union select 1, 30
union select 2, 5

insert into #del(delid,delamount)
select 1, 9
union select 1, 8
union select 3, 7

/** wrong **/

select m.id, m.name, isnull(sum(p.prodamount),0), isnull(sum(d.delamount),0)
from #main m
left join #prod p on p.prodid = m.id
left join #del d on d.delid = m.id
group by m.id, m.name

/** right! **/
select id, name, isnull(myprod.prodtot,0) as prodtot, isnull(mydel.deltot,0) as deltot
from #main
left join 
    (SELECT prodid, SUM(prodamount) AS prodtot
    FROM #prod
    GROUP BY prodid) myprod on #main.id = myprod.prodid
left join 
    (SELECT delid, SUM(delamount) AS deltot
    FROM #del
    GROUP BY delid) mydel on #main.id = mydel.delid



drop table #prod
drop table #del
drop table #main

Answer 5

Here's my rewrite of you queries into a single query:

   SELECT t.edisid,
          t.name, 
          SUM(dd.quantity) / 8 AS TiedDispense,
          SUM(d.quantity) AS TiedDelivered
     FROM SITES t
     JOIN USERSITES us ON us.edisid = t.esisid
     JOIN USERS u ON u.id = us.userid
     JOIN MASTERDATES md ON md.edisid = us.edisid
                        AND md.date >= t.siteonline
LEFT JOIN DLDATA dd ON dd.downloadid = md.id
LEFT JOIN DELIVERY d ON d.deliveryid = md.id
     JOIN PRODUCTS p ON p.id IN (dd.product, d.product)
LEFT JOIN SITEPRODUCTTIES spt ON spt.edisid = us.edisid
                             AND spt.productid = p.id
LEFT JOIN SITEPRODUCTCATEGORYTIES spct ON spct.edisid = us.edisid
                                      AND spct.productcategoryid = p.categoryid
    WHERE u.id = @UserID
      AND (@Tied IS NULL OR COALESCE(spt.tied, spct.tied, p.tied) = @Tied)
      AND md.date BETWEEN @From AND @To      
 GROUP BY t.edisid, t.name

Depending on your data, the JOINs to DLDATA and DELIVERY could be inner joins.

It'd be good to get in the habit of using table aliases.