I have an ssrs 2012 report that takes along time to run. I would like to look at the execution plan to see what I can do to speed up the main part of a report.
Can you tell me how I can look at the execution plan in the SSRS report and/or running the main part of the sql in sql server management studio?
are you using embedded SQL or stored procedures to fetch the data?
either way post the code here, tables involved and any indices
This is embedded sql. Here is the sql:
--debug
--DECLARE @endYear SMALLINT = 2017
--DECLARE @calID INT =5 --DECLARE @report VARCHAR(7) = 'subrpt6'
--DECLARE @Sort TINYINT = 1 -- 1 = Last Name, First Name 2 = HM, Last Name, First Name 3 = HM Teacher, Last Name, First Name 4 = Grade, Last Name, First Name
--DECLARE @filterByGrade TINYINT = 0 -- 1 = Yes, 0 = No
--DECLARE @grade CHAR(2) = '00'
--DECLARE @filterBySort TINYINT = 0 -- 1 = Yes, 0 = No
--DECLARE @sortFilter VARCHAR(30) = '1' -- 1 = A-E, 2 = F-J, 3 = K-O, 4 = P-T, 5 = U-Z or HM or HM Teacher NULL is 'No HR Assigned'
--end debug
IF OBJECT_ID('tempdb..#HM') IS NOT NULL DROP TABLE #HM
CREATE TABLE #HM (pID INT,
stuNum VARCHAR(15),
sT VARCHAR(1),
eID INT,
cID INT,
csD INT,
teacherpID INT,
teacherFirstName VARCHAR(50),
teacherMiddleName VARCHAR(50),
teacherLastName VARCHAR(50),
teacherName VARCHAR(104),
sTD VARCHAR(102),
roomID INT,
rmName VARCHAR(10),
courseID INT,
courseHM BIT,
secID INT,
sectionHM BIT,
rID INT,
trID INT,
pID INT,
PSID INT,
stucIDINT,
termID INT)
CREATE CLUSTERED INDEX HM_eID_pID ON #HM (eID, pID,cID )
INSERT INTO #HM
EXEC t.dbo.spHM @endYear, @calID, 'P,N,S', NULL
DELETE #HM
WHERE PSID IN (SELECT PSID
FROM t2.dbo.PS WITH (NOLOCK)
WHERE name LIKE '%B%') --Keep only "A" day schedules
IF OBJECT_ID('tempdb..#Students') IS NOT NULL DROP TABLE #Students
CREATE TABLE #Students (pID INT,
cID INT,
eID INT,
schoolName VARCHAR(40),
fullName VARCHAR(79),
firstName VARCHAR(35),
lastName VARCHAR(40),
gradeSeq TINYINT,
grade VARCHAR(4),
HM VARCHAR(10),
HMTeacher VARCHAR(102),
sT varchar(01),
HMNumTeacher VARCHAR(112))
CREATE CLUSTERED INDEX Students_pID_calID ON #Students (eID, pID,cID)
IF @filterBySort = 1
BEGIN
IF @Sort IN (1,4)
BEGIN
IF '1' IN (@sortFilter)
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD,
Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.lastName LIKE 'A%' OR Students.lastName LIKE 'B%' OR Students.lastName LIKE 'C%' OR Students.lastName LIKE 'D%' OR Students.lastName LIKE 'E%')
AND (Students.sT IN (@sT))
END
IF '2' IN (@sortFilter)
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.lastName LIKE 'F%' OR Students.lastName LIKE 'G%' OR Students.lastName LIKE 'H%' OR Students.lastName LIKE 'I%' OR Students.lastName LIKE 'J%')
AND (Students.sT IN (@sT))
END
IF '3' IN (@sortFilter)
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.lastName LIKE 'K%' OR Students.lastName LIKE 'L%' OR Students.lastName LIKE 'M%' OR Students.lastName LIKE 'N%' OR Students.lastName LIKE 'O%')
AND (Students.sT IN (@sT))
END
IF '4' IN (@sortFilter)
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' +substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.lastName LIKE 'P%' OR Students.lastName LIKE 'Q%' OR Students.lastName LIKE 'R%' OR Students.lastName LIKE 'S%' OR Students.lastName LIKE 'T%')
AND (Students.sT IN (@sT))
END
IF '5' IN (@sortFilter)
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.lastName LIKE 'U%' OR Students.lastName LIKE 'V%' OR Students.lastName LIKE 'W%' OR Students.lastName LIKE 'X%' OR Students.lastName LIKE 'Y%' OR Students.lastName LIKE 'Z%')
AND (Students.sT IN (@sT))
END
END
ELSE IF @Sort = 2
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND COALESCE(HM.rmName, 'No HR Assigned') IN (@sortFilter)
AND (Students.sT IN (@sT))
END
ELSE IF @Sort = 3
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND COALESCE(HM.sTD, 'No HR Assigned') IN (@sortFilter)
AND (Students.sT IN (@sT))
END
END
ELSE --@filterBySort = 0
BEGIN
INSERT INTO #Students
SELECT Students.pID, Students.calID, Students.eID, Students.schoolName, Students.fullName, Students.firstName,
Students.lastName, Students.gradeSeq, Students.grade, HM.rmName, HM.sTD, Students.sT
,CASE WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' ) then ' No HR Teacher (No HR)'
WHEN (HM.sTD is null or LTRIM(HM.sTD) = '' )
AND ( HM.rmName is not null ) then 'No HR Teacher ' + '( ' + substring(LTRIM(HM.rmName),1,10) +')'
WHEN (HM.sTD is not null)
AND ( HM.rmName is null or LTRIM(HM.rmName) = '' )
then substring(ltrim(HM.sTD),1,30) + ' (No HR)'
ELSE substring(ltrim(HM.sTD),1,30) + ' (' + substring(LTRIM(HM.rmName),1,10) + ')'
END as HMNumTeacher
FROM t.dbo.cRpt AS Students
LEFT JOIN #HM AS HM
ON Students.eID = HM.eID
AND Students.pID = HM.pID
AND Students.calID = HM.calID
AND Students.sT = HM.sT
WHERE Students.calID = @calID
AND (Students.sT IN (@sT))
END
IF 'subrpt6' IN (@report)
BEGIN
SELECT DISTINCT Students.pID, Students.calID, Students.schoolName, Students.fullName, Students.firstName, Students.lastName,
Students.gradeSeq, Students.grade, Students.HM,
CASE
WHEN LTRIM(Students.HMTeacher) = '' THEN NULL
ELSE Students.HMTeacher
END AS HMTeacher
,Students.sT
,Students.HMNumTeacher
FROM #Students AS Students
JOIN t.dbo.vRelationships AS vRelationships
ON Students.pID = vRelationships.studentpID
--Secondary HH Guardians Flagged Mailing
WHERE vRelationships.secondaryHousehold = 1
AND vRelationships.activeRelationship = 1
AND vRelationships.householdRelationship = 1
AND (vRelationships.relationshipGuardian = 1 OR vRelationships.relationship = 'Step Parent')
AND vRelationships.relationshipMailing = 1
AND (Students.sT IN (@sT))
--debug
--ORDER BY Students.fullName
END
ELSE
BEGIN
SELECT DISTINCT pID,cID, schoolName, fullName, firstName, lastName, gradeSeq, grade, HM,
CASE
WHEN LTRIM(HMTeacher) = '' THEN NULL
ELSE HMTeacher
END AS HMTeacher
,sT
,HMNumTeacher
FROM #Students
WHERE ( (@filterByGrade = 1 AND grade IN (@grade))
OR @filterByGrade = 0)
AND (sT IN (@sT))
--debug
--ORDER BY fullName
END
IF OBJECT_ID('tempdb..#HM') IS NOT NULL DROP TABLE #HM
IF OBJECT_ID('tempdb..#Students') IS NOT NULL DROP TABLE #Students