Is there a way to improve performance of this query (aggregation)

Question

Here is the query:

SELECT  sdd.CompanyID 
            ,sdd.ClassID 
            ,sdd.PeriodID, SUM(sdd.Volume) AS VolumeTotal, SUM(sdd.Dollars) AS DollasTotal
            ,COUNT(LogID) as LogIDCount

    FROM (SELECT dp.CompanyID 
                ,ds.ClassID 
                ,fs.PeriodID, fs.LogID, sum(fs.Volume) AS Volume,sum(fs.Dollars) AS Dollars
    FROM DW.FactSupplyDataDetail fs     WITH (NOLOCK)
        JOIN DW.DimPLProvider dp    WITH (NOLOCK) 
            ON fs.PLProviderID = dp.PLProviderID
        JOIN DW.DimSupply ds    WITH (NOLOCK) 
            ON fs.SupplyID = ds.SupplyID
        WHERE fs.PeriodID between 201901 and 201907
        GROUP BY dp.CompanyID 
                ,ds.ClassID 
                ,fs.PeriodID,fs.LogID) sdd
    GROUP BY sdd.CompanyID 
            ,sdd.ClassID 
            ,sdd.PeriodID

here is the execution plan for the query:

https://www.brentozar.com/pastetheplan/?id=rkoxSEjEH

DW.FactSupplyDataDetail has 10590237 records
DW.DimPLProvider has 5071 records
DW.DimSupply has 81001 records

result of a query is 1992094

Could you use [paste the plan](https://www.brentozar.com/pastetheplan/) for us? — S3S, Aug 21 '19 at 18:19
And careful with [adding NOLOCK](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) everywhere... it has some terrible side affects — S3S, Aug 21 '19 at 18:43
https://stackoverflow.com/questions/11920317/countdistinct-in-multiple-columns-in-sql-server-2008 — Tab Alleman, Aug 21 '19 at 18:53
@TabAlleman you suggesting to use count(distinct LogID), I tried, it runs super slow... — DmitrySD, Aug 21 '19 at 19:41
```You could get rid of the useless SUM() columns.``` @TabAlleman, i need them, it's actually just a sub query, I use sum() columns in an outer query — DmitrySD, Aug 21 '19 at 19:43
By showing us only part of the query, you obfuscated the question. If you are doing more than just getting a single COUNT() then my comments don't apply. — Tab Alleman, Aug 21 '19 at 19:45
I see the link, but we need the XML...Read [this post](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question) as well as [this post](https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors) for why. Also, using paste the plan allows us to see inside the XML on attributes that you can not capture with an image. — S3S, Aug 21 '19 at 19:48
@scsimon posted real query that I'm trying to improve, and also posted a query plan. Thank you. — DmitrySD, Aug 21 '19 at 21:25
@TabAlleman posted real query that I'm trying to improve, and also posted a query plan. Thank you. — DmitrySD, Aug 21 '19 at 21:26
Looking at that index seek, I would suggest you [update your statistics](https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017) — S3S, Aug 21 '19 at 21:32
What would happen if you just pre-aggregated on the items in the Fact table, then used _that_ to join out to your other tables and get your counts and a count(distinct LogID) at that point? That would likely result in a join on a smaller set of data for the Company and Class values. — Peter Schott, Aug 23 '19 at 21:41

score 0 · Answer 1 · answered Aug 22 '19 at 23:04

0

Check that

Table FactSupplyDataDetail has index started from PeriodID
Table DimSupply has index started from SupplyID
Table DimPLProvider has index started from PLProviderID

The table TABLE has index started from column COLUMN means that you have index (idx_xxx_) defined as:

CREATE INDEX idx_xxx on TABLE (COLUMN, some other columns or empty list);

answered Aug 22 '19 at 23:04

Alexey Vlasov

355
2
5

FactSupplyDataDetail Table has an index staring with PeriodID. SupplyID is a PK in DimSupply table, and PLProviderID is a PK in DimPLProvider so there is a clustered index with *ID column on both. – DmitrySD Aug 24 '19 at 05:20

Is there a way to improve performance of this query (aggregation)

1 Answers1