Help optimizing t-sql queries
- or -
Post a project like this1874
$43/hr
- Posted:
- Proposals: 6
- Remote
- #2295309
- Awarded
Description
Experience Level: Expert
I need to combine the two t-sql queries below and improve their efficiency - they work very slowly for the number of total rows in the database.
SELECT DISTINCT
TOP (100) PERCENT uas.UserIdentifier, REPLACE(uas.UserIdentifier, 'docebo/', '') AS simpleusername, dbo.Users.SpecialtyIdentifier, ISNULL(derivedtbl_1.numpassed, 0) AS numpassed, ISNULL(uas3_1.numfailed, 0)
AS numfailed, uas4_1.numachievable, dbo.vw_totalmilestonespercompetency.nummilestones AS totalmilestones
FROM dbo.UserAggregateScores AS uas INNER JOIN
dbo.Users ON uas.UserIdentifier = dbo.Users.NameIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numpassed
FROM dbo.UserAggregateScores AS uas2
WHERE (MilestonePassed = 1)
GROUP BY UserIdentifier) AS derivedtbl_1 ON uas.UserIdentifier = derivedtbl_1.UserIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numfailed
FROM dbo.UserAggregateScores AS uas3
WHERE (MilestonePassed = 0) AND (MilestoneAchievable = 0)
GROUP BY UserIdentifier) AS uas3_1 ON uas.UserIdentifier = uas3_1.UserIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numachievable
FROM dbo.UserAggregateScores AS uas4
WHERE (MilestonePassed = 0) AND (MilestoneAchievable = 1)
GROUP BY UserIdentifier) AS uas4_1 ON uas.UserIdentifier = uas4_1.UserIdentifier FULL OUTER JOIN
dbo.vw_totalmilestonespercompetency ON users.specialtyidentifier = vw_totalmilestonespercompetency.id
GROUP BY uas.UserIdentifier, uas.MilestoneId, uas.MilestoneScore, uas.MilestonePassed, derivedtbl_1.numpassed, uas3_1.numfailed, dbo.Users.SpecialtyIdentifier, dbo.vw_totalmilestonespercompetency.nummilestones,
uas4_1.numachievable
ORDER BY uas.UserIdentifier
and:
SELECT TOP (100) PERCENT exlsd.UserUniqueID, exlsd.Branches, exlsd.UserName, exlsd.FirstName, exlsd.LastName, COUNT(DISTINCT exlsd.CourseCode) AS numassigned, ISNULL(xsld3.numinprogress, 0) AS numinprogress,
ISNULL(xsld2.numcomplete, 0) AS numcomplete, ISNULL(ROUND(CAST(xsld2.numcomplete AS float) / CAST(COUNT(DISTINCT exlsd.CourseCode) AS Float), 2), 0) AS percentcomplete, MAX(exlsd.UserLastAccessDate)
AS lastaccessdate, ISNULL(AVG(NULLIF (exlsd.FinalScore, 0)), 0) AS AverageScore
FROM dbo.ExternalLMSUserData AS exlsd FULL OUTER JOIN
(SELECT UserName, COUNT(DISTINCT CourseCode) AS numcomplete
FROM dbo.ExternalLMSUserData
WHERE (Status = 'completed') AND (CourseCode LIKE 'r[0-9]%')
GROUP BY UserName) AS xsld2 ON exlsd.UserName = xsld2.UserName FULL OUTER JOIN
(SELECT UserName, COUNT(DISTINCT CourseCode) AS numinprogress
FROM dbo.ExternalLMSUserData AS ExternalLMSUserData_1
WHERE (Status <> 'completed') AND (CourseCode LIKE 'r[0-9]%')
GROUP BY UserName) AS xsld3 ON exlsd.UserName = xsld3.UserName
WHERE (exlsd.CourseCode LIKE 'r[0-9]%')
GROUP BY exlsd.UserName, exlsd.FirstName, exlsd.LastName, xsld2.numcomplete, xsld3.numinprogress, exlsd.Branches, exlsd.UserUniqueID
ORDER BY exlsd.Branches, exlsd.UserName
SELECT DISTINCT
TOP (100) PERCENT uas.UserIdentifier, REPLACE(uas.UserIdentifier, 'docebo/', '') AS simpleusername, dbo.Users.SpecialtyIdentifier, ISNULL(derivedtbl_1.numpassed, 0) AS numpassed, ISNULL(uas3_1.numfailed, 0)
AS numfailed, uas4_1.numachievable, dbo.vw_totalmilestonespercompetency.nummilestones AS totalmilestones
FROM dbo.UserAggregateScores AS uas INNER JOIN
dbo.Users ON uas.UserIdentifier = dbo.Users.NameIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numpassed
FROM dbo.UserAggregateScores AS uas2
WHERE (MilestonePassed = 1)
GROUP BY UserIdentifier) AS derivedtbl_1 ON uas.UserIdentifier = derivedtbl_1.UserIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numfailed
FROM dbo.UserAggregateScores AS uas3
WHERE (MilestonePassed = 0) AND (MilestoneAchievable = 0)
GROUP BY UserIdentifier) AS uas3_1 ON uas.UserIdentifier = uas3_1.UserIdentifier FULL OUTER JOIN
(SELECT UserIdentifier, COUNT(DISTINCT MilestoneId) AS numachievable
FROM dbo.UserAggregateScores AS uas4
WHERE (MilestonePassed = 0) AND (MilestoneAchievable = 1)
GROUP BY UserIdentifier) AS uas4_1 ON uas.UserIdentifier = uas4_1.UserIdentifier FULL OUTER JOIN
dbo.vw_totalmilestonespercompetency ON users.specialtyidentifier = vw_totalmilestonespercompetency.id
GROUP BY uas.UserIdentifier, uas.MilestoneId, uas.MilestoneScore, uas.MilestonePassed, derivedtbl_1.numpassed, uas3_1.numfailed, dbo.Users.SpecialtyIdentifier, dbo.vw_totalmilestonespercompetency.nummilestones,
uas4_1.numachievable
ORDER BY uas.UserIdentifier
and:
SELECT TOP (100) PERCENT exlsd.UserUniqueID, exlsd.Branches, exlsd.UserName, exlsd.FirstName, exlsd.LastName, COUNT(DISTINCT exlsd.CourseCode) AS numassigned, ISNULL(xsld3.numinprogress, 0) AS numinprogress,
ISNULL(xsld2.numcomplete, 0) AS numcomplete, ISNULL(ROUND(CAST(xsld2.numcomplete AS float) / CAST(COUNT(DISTINCT exlsd.CourseCode) AS Float), 2), 0) AS percentcomplete, MAX(exlsd.UserLastAccessDate)
AS lastaccessdate, ISNULL(AVG(NULLIF (exlsd.FinalScore, 0)), 0) AS AverageScore
FROM dbo.ExternalLMSUserData AS exlsd FULL OUTER JOIN
(SELECT UserName, COUNT(DISTINCT CourseCode) AS numcomplete
FROM dbo.ExternalLMSUserData
WHERE (Status = 'completed') AND (CourseCode LIKE 'r[0-9]%')
GROUP BY UserName) AS xsld2 ON exlsd.UserName = xsld2.UserName FULL OUTER JOIN
(SELECT UserName, COUNT(DISTINCT CourseCode) AS numinprogress
FROM dbo.ExternalLMSUserData AS ExternalLMSUserData_1
WHERE (Status <> 'completed') AND (CourseCode LIKE 'r[0-9]%')
GROUP BY UserName) AS xsld3 ON exlsd.UserName = xsld3.UserName
WHERE (exlsd.CourseCode LIKE 'r[0-9]%')
GROUP BY exlsd.UserName, exlsd.FirstName, exlsd.LastName, xsld2.numcomplete, xsld3.numinprogress, exlsd.Branches, exlsd.UserUniqueID
ORDER BY exlsd.Branches, exlsd.UserName
Barrie M.
100% (2)Projects Completed
2
Freelancers worked with
2
Projects awarded
67%
Last project
14 Feb 2019
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
hi , can you please attach the execution plan for each query and number of rows in each table as well.
766280
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies