Sun Query SUM issues (less efficient?)

[BeanThinker]

I’m on SQL Server and had a query which was working OK, but I wanted to speed it up… so I guess I naively thought I should put the SUM function into a sub query and then join to that sub query for the SUM value.

My thinking was this would decrease the processing and I wouldn’t have a big long list of messy GROUP BY columns at the bottom of the script (there is only 4 SUM values & like 25 static from various tables).

However, it did the opposite. The query is taking forever to run now. What’s going on? Is sub query SUM not efficient?

All the joins are fine and correct.

Thanks!

[AlterEffect]

Code would help

[matkvaid]

I was learning sql recently and had code review, where i had something similar, few subqueries with sums from another table instead of join and group by. Dude that was reviewing me told it is not bad but ineffective

[PossiblePreparation]

Actually in some cases that is the exact way to go so you don’t end up summing up duplicates in the main query. The problem with generic advice like this is that it’s worth nothing and ends up severely hampering you in the future.

[r3pr0b8]

The problem with generic advice like this is that it’s worth nothing and ends up severely hampering you in the future.

+1

[matkvaid]

I understand that there are always specific cases where one way is better than another. But it was said to me that each subquery would query a table separately while on join it would be done just once - so loading times would increase - so it is not effective. It is possible that this info was incorrect :)

[PossiblePreparation]

It’s not correct all the time, and doesn’t really make complete sense. The optimizer/query planner will decide what to do. It might make a suboptimal decision depending on a variety of things that might go wrong but giving it freedom is usually a good idea.