Need help with slow query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with slow query

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2011-02-18 : 06:45:23

Hi,

I have a query that takes 6-7 minutes to run and I've tried a few things get errors. Its been a long time since I did any TSQL and just thought you guys might be able to help.

It basically need to trawl through three categories capable, experienced, and advanced, total the rows and join them up.

Query below:

DECLARE @capable TABLE
(
UserID int primary,
Planned decimal,
Completed decimal,
Achieved decimal,
PercentCapable decimal(3,0) default 0.0
)

DECLARE @experienced TABLE
(
UserID int,
Planned decimal,
Completed decimal,
Achieved decimal,
PercentCapable decimal(3,0) default 0.0
)

DECLARE @advanced TABLE
(
UserID int,
Planned decimal,
Completed decimal,
Achieved decimal,
PercentCapable decimal(3,0) default 0.0
)

-- capable
INSERT INTO @capable (UserID, Planned, Completed)
SELECT userid, SUM(CASE WHEN (passed IS NULL or getdate() > expirydate)THEN 1 ELSE 0 END) + SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) as Planned,
SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) Completed
FROM
(

SELECT
urpb.UserID,
cc.Passed,
cc.expirydate
FROM
UserRoleProfileWithBookings urpb
LEFT OUTER JOIN CoursesCompletedAndValid cc ON cc.BookingID = urpb.BookingID

WHERE urpb.RoleLevel = 0
) AS profiles

GROUP BY userid

-- experienced
INSERT INTO @experienced (UserID, Planned, Completed)
SELECT userid, SUM(CASE WHEN (passed IS NULL or getdate() > expirydate)THEN 1 ELSE 0 END) + SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) as Planned,
SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) Completed
FROM
(

SELECT
urpb.UserID,
cc.Passed,
cc.expirydate
FROM
UserRoleProfileWithBookings urpb
LEFT OUTER JOIN CoursesCompletedAndValid cc ON cc.BookingID = urpb.BookingID

WHERE urpb.RoleLevel = 2

) AS profiles

GROUP BY userid

-- advanced
INSERT INTO @advanced (UserID,Planned, Completed)
SELECT userid, SUM(CASE WHEN (passed IS NULL or getdate() > expirydate)THEN 1 ELSE 0 END) + SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) as Planned,
SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) Completed
FROM
(

SELECT
urpb.UserID,
cc.Passed,
cc.expirydate
FROM
UserRoleProfileWithBookings urpb
LEFT OUTER JOIN CoursesCompletedAndValid cc ON cc.BookingID = urpb.BookingID

WHERE urpb.RoleLevel = 4

) AS profiles

GROUP BY userid

INSERT INTO @capable (UserID, Planned, Completed, achieved, percentcapable)
SELECT userid, planned, completed,
SUM(CASE WHEN (planned = 0 AND completed > 1) THEN 1 ELSE 0 END) as achieved,
SUM((completed / (completed + planned)) * 100) as percentcapable

from @capable
GROUP BY userid, planned, completed

INSERT INTO @experienced (UserID, Planned, Completed, achieved, percentcapable)
SELECT userid, planned, completed,
SUM(CASE WHEN (planned = 0 AND completed > 1) THEN 1 ELSE 0 END) as achieved,
SUM((completed / (completed + planned)) * 100) as percentcapable

from @experienced
GROUP BY userid, planned, completed

INSERT INTO @advanced (UserID, Planned, Completed, achieved, percentcapable)
SELECT userid, planned, completed,
SUM(CASE WHEN (planned = 0 AND completed > 1) THEN 1 ELSE 0 END) as achieved,
SUM((completed / (completed + planned)) * 100) as percentcapable
from @advanced
GROUP BY userid, planned, completed

SELECT u.firstname as Firstname,u.lastname as Lastname, u.KCSUserID, r.name as JobRole, o.code as DepotID, o.name as DepotName,
c.Achieved as cAchieved, c.PercentCapable as cCapable, c.Planned as cPlanned, c.Completed as cCompleted,
e.Achieved as eAchieved, e.PercentCapable as eCapable, e.Planned as ePlanned, e.Completed as eCompleted,
a.Achieved as aAchieved, a.PercentCapable as aCapable, a.Planned as aPlanned, a.Completed as aCompleted

FROM @capable c
INNER JOIN @experienced e
ON c.UserID = e.UserID
INNER JOIN @advanced a
ON e.UserID = a.UserID
INNER JOIN users u
ON c.UserID = u.ID
LEFT OUTER JOIN Roles r
ON u.RoleID = r.ID
LEFT OUTER JOIN OrgUnits o
ON u.OrgUnitId = o.ID

WHERE c.Achieved IS NOT NULL
AND e.Achieved IS NOT NULL
AND a.Achieved IS NOT NULL

AND u.enabled = 1
ORDER BY u.lastname

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-18 : 07:00:04

[code]SELECT urpb.UserID,
urpb.RoleLevel,
SUM(CASE WHEN passed IS NULL or getdate() > expirydate THEN 1 ELSE 0 END) +
SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) AS Planned
SUM(CASE WHEN passed = 1 AND (expirydate IS NULL or getdate() < expirydate) THEN 1 ELSE 0 END) AS Completed
FROM dbo.UserRoleProfileWithBookings AS urpb
LEFT JOIN dbo.CoursesCompletedAndValid AS cc ON cc.BookingID = urpb.BookingID
WHERE urpb.RoleLevel IN (0, 2, 4)
GROUP BY urpb.UserID,
urpb.RoleLevel[/code]

N 56°04'39.26"
E 12°55'05.63"

g_r_a_robinson
Starting Member

45 Posts

Posted - 2011-02-21 : 09:16:00

Thanks for this, but even when I tried this in isolation the query still took 5 minutes to complete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-21 : 10:24:19

Do you have an index over columns (UserID, RoleLevel)?

CREATE INDEX IX_Peso ON dbo.UserRoleProfileWithBookings (UserID, RoleLevel) INCLUDE (BookingID, Passed, ExpiryDate).

N 56°04'39.26"
E 12°55'05.63"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-21 : 10:37:37

Try using temp tables to get around indexing problems.
This should at least show you the area to concentrate your tuning problems.
Something like:


SELECT RoleLevel, UserID, BookingID
INTO #u
FROM UserRoleProfileWithBookings
WHERE 1 = 0

ALTER TABLE #u
ADD PRIMARY KEY CLUSTERED (RoleLevel, UserID, BookingID)

INSERT INTO #u
SELECT RoleLevel, UserID, BookingID
FROM UserRoleProfileWithBookings
WHERE RoleLevel IN (0, 2, 4)
ORDER BY RoleLevel, UserID, BookingID

SELECT BookingID
	,CAST(1 AS int) AS Planned
	,CAST(1 AS int) AS Completed
INTO #c
FROM CoursesCompletedAndValid
WHERE 1 = 0
	
ALTER TABLE #c
ADD PRIMARY KEY CLUSTERED(BookingID)

INSERT INTO #c
SELECT BookingID
	,CASE
		WHEN passed IS NULL OR CURRENT_TIMESTAMP > expirydate
		THEN 1
		ELSE 0
	END
	+
	CASE
		WHEN passed = 1 AND (expirydate IS NULL or CURRENT_TIMESTAMP < expirydate)
		THEN 1
		ELSE 0
	END AS Planned
	,CASE
		WHEN passed = 1 AND (expirydate IS NULL or CURRENT_TIMESTAMP < expirydate)
		THEN 1
		ELSE 0
	END AS Completed
FROM CoursesCompletedAndValid
ORDER BY BookingId

SELECT RoleLevel, UserID
	,CAST(1 AS int) AS Planned
	,CAST(1 AS int) AS Completed
	,CAST(1 AS tinyint) AS achieved
	,CAST(1 AS float) AS Completed
INTO #Results
FROM #u
WHERE 1 = 0

ALTER TABLE #Results
ADD PRIMARY KEY CLUSTERED (RoleLevel, UserID)

;WITH CPlanComplete
AS
(
	SELECT U.RoleLevel, U.UserID
		,SUM(C.Planned) AS Planned
		,SUM(C.Completed) AS Completed
	FROM #u U
		LEFT #c C
			ON U.BookingID = C.BookingID
	GROUP BY U.RoleLevel, U.UserID
)
INSERT INTO #Results
SELECT RoleLevel, UserID, Planned, Completed
		CASE WHEN planned = 0 AND completed > 1 THEN 1 ELSE 0 END AS achieved
		CASE ((completed / (completed + planned)) * 100) AS percentcapable
FROM CPlanComplete
ORDER BY RoleLevel, UserID

With the #Results table your final query becomes:


SELECT u.firstname as Firstname,u.lastname as Lastname, u.KCSUserID, r.name as JobRole, o.code as DepotID, o.name as DepotName,
c.Achieved as cAchieved, c.PercentCapable as cCapable, c.Planned as cPlanned, c.Completed as cCompleted, 
e.Achieved as eAchieved, e.PercentCapable as eCapable, e.Planned as ePlanned, e.Completed as eCompleted,
a.Achieved as aAchieved, a.PercentCapable as aCapable, a.Planned as aPlanned, a.Completed as aCompleted 
FROM Users u
	JOIN #Results c
		ON c.RoleLevel = 0
			AND c.UserID = u.UserID
	JOIN #Results e
		ON e.RoleLevel = 2
			AND e.UserID = u.UserID
	JOIN #Results a
		ON a.RoleLevel = 4
			AND a.UserID = u.UserID
	LEFT JOIN Roles r 
		ON u.RoleID = r.ID
	LEFT JOIN OrgUnits o 
		ON u.OrgUnitId = o.ID
WHERE u.enabled = 1
ORDER BY u.lastname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources