Why is Embedded so slow?

ats1958 — Mon, 24 Sep 2018 20:26:09 GMT

I have an embedded report that takes roughly 45 seconds to filter/drill down, deeming it entirely unusable.

It is only using ~1500 rows of data
The SQL Query feeding it uses a number of joins across approximately 12 tables
There are numerous filters, bookmarks, etc.
It's using RLS

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]

topic Why is Embedded so slow? in Developer

Why is Embedded so slow?