Is there a more efficient way I should be writing a query with multiple joins?-CodePudding

I have a query I wrote like the below one however multiple of the Views I'm using in the query have > 50 million rows. The query is super slow (2.5 minutes) even despite only selecting top 50 rows.

Is there a different way I should be writing a query like this to speed up the execution time other than using indexes?

SELECT TOP 50
       ECO.ITEMNUMBER,
       ECO.PRODUCTNAME,
       SUM(POH.SCHEDULEDQUANTITY) AS WIPQTY,
       SUM(ITV.QTY) AS SOLDQTY,
       SUM(IOH.QTY) AS ONHANDQTY,
       SUM(SOC.ORIGINALORDERQTY) AS ONORDERQTY
FROM ECO
     LEFT JOIN ITV ON ECO.ITEMNUMBER = ITV.ITEMID
     LEFT JOIN IOH ON ECO.ITEMNUMBER = IOH.ITEMID
     LEFT JOIN SOC ON ECO.ITEMNUMBER = SOC.PRODUCTNUMBER
     LEFT JOIN POH ON ECO.ITEMNUMBER = POH.ITEMNUMBER
WHERE ECO.PRODUCTGROUPID = '1'
  AND ITV.REFERENCECATEGORY = 'REF0'
  AND ITV.INVENTLOCATIONID = 'MAIN'
  AND ITV.DATECLOSED > GETDATE() - 365
GROUP BY ECO.ITEMNUMBER,
         ECO.PRODUCTNAME;

CodePudding user response：

You said "multiple of the Views I'm using in the query have > 50 million rows". So you are querying Views and not tables? If so, you might want to look at the performance of those views first.

Also, maybe there is a normalization issue if you are trying to join one 50 million row view to another. There might be a better strategy.

Even good indexing and good query writing will struggle when joining 50 million rows against 50 million other rows. Maybe you can summarize the data before this query. Perhaps in a temporary table.

CodePudding user response：

Make sure you have indexes on the columns you are filtering and/or joining on.

If performance is still slow after adding indexes, try creating a lookup table and update the lookup table every time you do an CRUD action on one of those tables. (Or sync them every hour or so).