Forum Discussion
Group By or Remove duplicates
- 4 years ago
In Diagnostics, pick up the report generated which has aggregated word.
Select Exclusive duration column, Transform tab - Duration - Total seconds - Now Statistics - Sum
Note down the timing of both approaches. Whatever is lesser is the better.
Now the first principle in optimizing query is to perform vertical filtering i.e. remove all unnecessary columns.
Hence, if you perform remove columns followed by Group by, you will have very good amount of performance increase compared to do plain vanilla Group by.Horizontal filtering (i.e. remove duplicates) is also one of the steps which we can resort to after Vertical filtering. But horizontal filtering will consume some finite time which will be added up to total time but same can be compensated by next steps as they will work on smaller dataset.
It is always better to work on a small size rectangle.
But you have to see how much time is taken in Query diagnostics on your data set for different approaches and choose the best one.
When I am comparing two approaches, I just duplicate the query, modify the new one to use the other approach, and load them in parallel and see which wins. You can speed up the testing by keeping fewer rows temporarily.
Pat
Thank you for the advice. I also thought about it, but couldn't find the time to do so. Pretty easy way to find the best solution.