Forum Discussion
Need help with a performance issue - optimizing QPU usage
Here are my thoughts (from the supplied sample pbix - so may not carry through to the real thing but hey-ho, there we go):
Do we need the filter to check 'open' or 'closed' status ? Seems redundant.
Do we need SkillName in the JobPostingSkillName table? Seems redundant.
Does the measure need to use DISTINCT? We can already see that the CALCULATE is redundant but why are we checking for duplicate 'JobId' s for the same skill? Even if there are (which would probably be a data quality issue) is it significant in terms of the count? If not, then the measure is a simpler COUNT (and you can replace the check for blank with '+0' too).
In business terms, is it important to see 12,855 records in the visual. No-one is going to scroll through them. What is it important to see here? Top 50? Maybe putting some thought into that would result in rewrite of the measure.
---
Also, in terms of the model, not this particular visual, the jobPostingAds is being used as a dimension table(even though it's really another Fact table) and it's got 10 million records so that's going to cause it's own performance problems.
----
Let me know what you think.
Here are my thoughts (from the supplied sample pbix - so may not carry through to the real thing but hey-ho, there we go):
Do we need the filter to check 'open' or 'closed' status ? Seems redundant.
Do we need SkillName in the JobPostingSkillName table? Seems redundant.
Does the measure need to use DISTINCT? We can already see that the CALCULATE is redundant but why are we checking for duplicate 'JobId' s for the same skill? Even if there are (which would probably be a data quality issue) is it significant in terms of the count? If not, then the measure is a simpler COUNT (and you can replace the check for blank with '+0' too).
In business terms, is it important to see 12,855 records in the visual. No-one is going to scroll through them. What is it important to see here? Top 50? Maybe putting some thought into that would result in rewrite of the measure.
---
Also, in terms of the model, not this particular visual, the jobPostingAds is being used as a dimension table(even though it's really another Fact table) and it's got 10 million records so that's going to cause it's own performance problems.
----
Let me know what you think.
HotChilli , Thank you so much for opening my eyes 😄
You are right, I don't need a Distinct Count as there won't be a duplicate JobId for a give skill. Changing this to just COUNT improved the performance drastically, I mean seriously drastically. The CPU cycles reduced from 35K to 1K and Duration from 10+ seconds to few ms.
Tons and Tons of thanks!!
To answer your other questions:
Regarding the status, the filter will always be on 'Open' only in this visual.
SkillName is not redundant in the actual model. I was trying out few changes to see if that helps.
Filtering TOP 50 or 100 in the visual causes an extra SE query with its own CPU cycles. Without TOP 50 or 100 renders the visual slower than without it 😞
And finally, JobPostingAds is really another Fact Table and business wants it that way for few reasons 😞 I couldn't do much there.
