Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have an embedded report that takes roughly 45 seconds to filter/drill down, deeming it entirely unusable.
Which, if any, of these factors could be causing the speed degradation?
I started by looking at the relevant SQL Queries in the Azure Query Performance Insight tool. Attached is a sample of one SQL Query. And it appears that one of these is being executed for each pie chart in the report (there are ~12 such pie charts on each report page). This query took 47 seconds to run:
SELECT TOP (1000001) [t0].[racekey], COUNT_BIG([t0].[employeeid]) AS [a0] FROM ( SELECT [userid],[datasetname],[timestamp],[datasetid],[location],[department],[title],[gender],[eeo1],[perf],[grade], CAST([rangemin] AS FLOAT) AS [rangemin], CAST([rangemid] AS FLOAT) AS [rangemid], CAST([rangemax] AS FLOAT) AS [rangemax],[race],[latest],[employeename],[employeeid], CAST([annualizedbase] AS FLOAT) AS [annualizedbase], CAST([incentive] AS FLOAT) AS [incentive], CAST([lti] AS FLOAT) AS [lti],[division],[jobgroup],[jobfamily],[birthdate],[dateinjob],[tenuredate],[flsa],[ptft],[benefits],[supervisor],[evalpoints],[country],[currency],[disabled],[education],[lgbt],[religion],[marital],[email],[jobcode], CAST([overtime] AS FLOAT) AS [overtime],[edu],[racekey],[eeo],[performance],[value],[url],[ageflag],[ageflag value],[ageflag url],[disabled value],[disabled url],[minority value],[minority url],[timestampedit],[filterkey] FROM ( (SELECT DISTINCT ai.value AS 'ageflag value', ai.url AS 'ageflag url', dim.value AS 'disabled value', dim.url AS 'disabled url', mi.value AS 'minority value', mi.url AS 'minority url', gi.value, gi.url, d.userid, datasetname, timestamp, d.timestampedit, latest, di.datasetid, employeename, employeeid, title, annualizedbase, grade, rangemin, rangemid, rangemax, incentive, lti, department, division, jobgroup, jobfamily, location, gender, di.race, birthdate, dateinjob, tenuredate, flsa, ptft, eeo1, perf, benefits, supervisor, evalpoints, country, currency, disabled, di.education, lgbt, religion, marital, email, jobcode, overtime, edu, rm.race AS racekey, eem.eeo, pm.performance, di.ageflag, filterkey FROM datasets D full outer join datasetitems DI on d.datasetid = di.datasetid LEFT JOIN education e ON di.education = e.education FULL OUTER JOIN edumaster em ON e.educationkey = em.eduid LEFT JOIN races r ON di.race = r.race FULL OUTER JOIN racemaster rm ON r.racekey = rm.racekeyid LEFT JOIN eeos ee ON di.eeo1 = ee.eeo FULL OUTER JOIN eeomaster eem ON ee.eeokey = eem.eeokeyid INNER JOIN performance p ON di.perf = p.performance INNER JOIN perfmaster pm ON p.performancekey = pm.performanceid AND p.userid = d.userid LEFT JOIN ceo ON di.employeeid = ceo.ceoid LEFT JOIN genderimg gi ON di.gender = gi.value LEFT JOIN ageimg ai ON di.ageflag = ai.value LEFT JOIN disabledimg dim ON di.disabled = dim.value LEFT JOIN minorityimg mi ON di.minority = mi.value) ) AS [t0] ) AS [t0] WHERE ( ([t0].[userid] = N'1ad3c8c0-7ce9-45c3-9ca6-cca56a3fc2a0') AND ( ([t0].[gender] IN (N'Female',N'Male')) ) ) GROUP BY [t0].[racekey]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |