Forum Discussion
PowerBI battering the database
Our database is a fairly low-powered Azure instance that houses a large amount of data and has one chief summarizing query in a stored procedure that takes anywhere between 30s and 2 minutes to complete. This sproc never really played nicely with PBI so it was converted to a MSTVF, which does seem to be better supported, but now we're finding that PBI shoots the CPU and IO utilization to 100% for hours at a time. When we check concurrent sessions there will be multiple concurrent attempts to run the TVF over and over again. Kill them all, PBI unjams, CPU drops to 0, start working with PBI again and sooner or later it's back at 100% with pBI incessantly and repeatedly running the TVF.
We use Direct Query, and don't want to use Import, but are looking for some facility where we can better control the refreshing PBI does from the TVF. Really, I'd like it that PBI loads the data once at startup, then we can work with it cached locally (but not imported; we don't want this data saved inside the PBI file but don't mind if it is all loaded into memory during design), and only manually refreshed when the developer requests a refresh - is it possible?
4 Replies
MSTVF = Multi-statement Table Valued Functions? Those can be performance killers for data queries.
Can you share the actual T-SQL that is getting passed to the SQL server? (properly obfuscated, of course)
Sounds like you may need some query tuning of the stored procedure, which, BTW, is probably not good if you are doing Direct Query as it need to call that "chief summarizing query" for every change in a visual!
I've dug into the PBI file a little (I'm very new to PBI) and I see:
A page with 10 measures on
Each measure is slightly different, for example here is one:
CountLocalHighAppleAllocation = CALCULATE(COUNTROWS(GetSummaryMSTVF),
GetSummaryMSTVF[NumberOfApples],
GetSummaryMSTVF[PercentAllocatedLocally] > 0.5,
GetSummaryMSTVF[PercentAllocatedLocally] < 0.75 )And another one:CountLocalExcessiveAppleAllocation = CALCULATE(COUNTROWS(GetSummaryMSTVF),
GetSummaryMSTVF[NumberOfApples],
GetSummaryMSTVF[PercentAllocatedLocally] > 0.75,
GetSummaryMSTVF[PercentAllocatedLocally] <= 1.0 )There are 10 like this, all doing some variation on counting or summing the number of apples that have been low, high or excessively allocated. Opening the page fires 10 queries at the DB, basically just running the TVF (If I ask for currently executing queries I get the TVF definition) so I now understand the why. Perhaps there is some other technique for working with this data (I didn't come up with this way but i can relay suggestions back) - it's a "for these ~100 rows, bucket into 3 bands based on percentage X and give a count/sum/average of 2 different data items from the bucket.If it were SQL I'd describe it as something like:
SELECT SUM(apples), COUNT(apples), SUM(oranges), COUNT(oranges)
...
GROUP BY CASE WHEN percent < 0.5 THEN 1 WHEN percent < 0.75 THEN 'high' ELSE 'excess'---I could look at the query again, but there wasn't much to do with it when it was last reviewed; it's got a huge amount of data to summarize..>>it's got a huge amount of data to summarize..<<
How big is "huge"? how much space does the table take up?
And this is accessed via a stored procedure, and via Direct Query? I tried to spin up something similar but it failed. I could do IMPORT of a stored procedure, but not a Direct Query.
Can you share the exact (or near exact) Power Query M statement used? (In Power Query, click Advanced Editor).
And can you share the contents of the stored procedure? Is it calling Functions?
In the end with this one we just added another table to the DB into which the calc'd results from the sproc were stored, and made PBI work from the cached data. It's imposed minorly more work in keeping the summary sync'd with the data that generates it but at least it meant the PBI team could get on with stuff at a reasonable pace
