August 13, 2012 at 1:22 pm
I am writing an SSRS report which is using the following model, because SSRS does not allow you to relate Datasets ------ : (
MAIN REPORT = 1 DataSet
+ SubReport1 = 1 Dataset
+ SubReport2 = 1 Dataset
+Subreport3 (Nested in Subreport2 = 1 Dataset
I am seeing performance issues on Subreport 2 & 3
Below is a breakdown of the tables:
1 - Courses (PK = CourseID
2 - SubCoures (PK = SubCourseID and FK course ID)
3 - SchoolQuarter (PK = QuarterID)
4 - mmStudentData (FK = SubCourseID, FK QuarterID, (2 types of grades which are FK IDs) WHERE Statement = StudentID [Pulling from data wharehouse]
The problem is for each mmStudentData there can be multiple rows because I am tracking all grades in this one table and I am trying to find the best solution becuase it is working with a SubReport presenting the Courses and Subcourse and pass the SubCourseID to Subreport3 for the Grades, but when I put it all together it runs very very slow.
I am trying to think of a better query to get all the data on Subreport2 without another nest, which I believe is causing the performance issue. When I run each individually the results are very very fast.
Looking for some t-sql gurus assistance out in the SQL Central World.
August 13, 2012 at 1:34 pm
More details - Why did I do a nested report?
The Course/SubCourse report is a tablix and I nested a matrix in order to utilize a pivot on the grades which again works as expected, but dies in performance for 786 pages it took 3 Minutes 26 Seconds, but again when I perform each piece seperetly it runs in under a second.
August 13, 2012 at 4:29 pm
You can check the execution log on the report server to see where report spends most of its time - retrieving data or rendering.
USE ReportServer
GO
SELECT
C.Name,
E.ReportID,
E.InstanceName,
E.UserName,
E.RequestType,
E.Format,
E.[Parameters],
E.TimeStart,
E.TimeEnd,
TimeDiff = DATEDIFF(n, E.TimeStart, E.TimeEnd),
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.[Source],
E.[Status],
E.ByteCount,
E.[RowCount]
FROM dbo.ExecutionLog E WITH (NOLOCK)
INNER JOIN [catalog] C WITH (NOLOCK)
ON E.ReportID = C.ItemID
WHERE Name = '<YOUR_REPORT_NAME>'
AND TimeStart > '08/13/2012'
I personally try to stay away from subreports and do most of the work in T-SQL.
--Vadim R.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply