Post reply

T-SQL - For Dataset for SSRS - Looking for input

  • August 13, 2012 at 1:22 pm

    #265826

    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

    #1525252

    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

    #1525329

    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