CODEWITHSUNDEEP
sqlsql-serveraggregate
chobo
chobo

Reputation: 32291

How to speed up query with aggregates?

I have the following query that seems to run pretty slow if more than one aggregate is used in the select part. Is there some way to optimize this?

The query returns a 168 rows and takes 1 second to complete, but this bogs down when a couple of users load the page at once and the original query had more aggregates which also add seconds to the query.

***** Update here's a simplier query** 

Select 
    gocm.CustomerID,
    sum(DISTINCT o.OrderTotal) as TotalOfOrders
from GroupOrder_Customer_Mapping gocm
    Left Join [Order] o on o.CreatedForCustomerID = gocm.customerid and o.grouporderid = 8254
where gocm.grouporderid = 8254
    group by gocm.CustomerID, invitePath
    order by invitepath

Execution Plan

enter image description here

returns following data (sample results) enter image description here

Upvotes: 1

Views: 2474

Answers (2)

michal.jakubeczy
michal.jakubeczy

Reputation: 9469

If data is not updated frequently you might consider an indexed view.

Upvotes: 0

Devart
Devart

Reputation: 121952

Possible this be helpful for you -

SELECT 
      gocm.CustomerID
    , o.TotalOfOrders 
FROM (
    SELECT DISTINCT gocm.CustomerID, invitePath
    FROM dbo.GroupOrder_Customer_Mapping gocm
    WHERE gocm.grouporderid = 8254
) gocm 
LEFT JOIN (
    SELECT 
          o.CreatedForCustomerID
        , TotalOfOrders = SUM(DISTINCT o.OrderTotal) 
    FROM dbo.[Order] o
    WHERE o.grouporderid = 8254
    GROUP BY o.CreatedForCustomerID
) o ON o.CreatedForCustomerID = gocm.customerid 
ORDER BY invitepath

Upvotes: 1

Related Questions