Ssrs 2008 calling subreports

SSAS and SSRS
1

I have an ssrs 2008 report that has 1 main ssrs report and 7 subreports. The main ssrs report and the 7 subreports are all executed at the same time. Sometimes the executions are extremely slow.

Thus my question is there a way to improve the performance of an ssrs 2008 report calling 7 subreports?

If so, would you tell how how to solve my performance problem and/or point me to urls that will tell me how to improive the performance of the reports?

Also if there is no way to improve the performance of these reports, would you let me know that also?

2

Are you using stored procedures for each report?

3

The majority of the reports are using inline sql. not stored procedures

4

please post them here if possible?
If not please answer the following

  1. Where you are joining tables join table1.column1 = table2.column1 do you have proper indices?

on second thought please post your inline SQL code here. basically what I am trying to get at is your issue might not be the sub reports but the inline sql and underlying design of tables etc.

5

Here is the sql from the subreports. Do you think there should be some kind of indexes on views and/or tables? If so, what tables would that be, what columns should have indexes and what kind of indexes would you use?
What other changes do you think could be made here?

  1. O_PA_AnnualComparisonRecords only key to table is never used in this table
    SELECT
    [endYear]
    ,[staffNumber]
    ,[PrincipalName]
    ,[cryrschoolID]
    ,[cryrschoolname]
    ,[pryrschoolID]
    ,[pryrschoolname]
    ,[sourceID]
    ,[source]
    ,[domainID]
    ,[domain]
    ,[itemID]
    ,[number]
    ,[item]
    ,[comparisonSchoolYear]
    ,[schoolYear]
    ,convert(decimal(5,2),[comparisonMean]) as comparisonMean
    ,convert(decimal(5,2),[principalMean]) as principalMean
    ,convert(decimal(5,2),[comparisonDifference]) as comparisonDifference
    ,convert(decimal(5,2),[districtcomparisonMean]) as districtcomparisonMean
    ,convert(decimal(5,2),[districtMean]) as districtMean
    ,convert(decimal(5,2),[districtComparisonDifference]) as districtComparisonDifference
    ,[ActiveYear]
    FROM [dbo].[O_PA_AnnualComparisonRecords] WITH(NOLOCK)
    where staffNumber = (@PstaffNumber) and
    schoolYear = (@PschoolYear) and cryrschoolID = (@PschoolID)
    order by sourceid, domainid, itemid

  2. O_PA_PrincipalComments only key to table is never used in this table
    SELECT distinct
    c.[staffNumber]
    ,sy.label as schoolYear
    ,c.[schoolID]
    ,c.[comments]
    ,ua.lastName + ', ' + ua.firstName as PrincipalName
    ,sch.name
    FROM [dbo].[O_PA_PrincipalComments] c WITH(NOLOCK) join
    ODataStore.dbo.SchoolYear as sy WITH(NOLOCK) on c.endYear = sy.endyear join
    O_APPS_DATA.dbo.vw_O_APPS_EmployeeList AS ua WITH(NOLOCK) on c.staffNumber = ua.staffNumber join
    dbo.vw_O_IC_SCHOOL_LIST sch WITH(NOLOCK) on c.schoolID = sch.schoolID
    where
    c.staffNumber = (@PstaffNumber) and
    sy.label = (@PschoolYear) and
    sy.label <> '10-11' and
    sch.schoolID = (@PschoolID)

  3. O_PA_ResultCountTotals only key to table is never used in this table
    SELECT
    rsc.[endYear] as rsendYear
    ,syrs.label as rsschoolYear
    ,rssch.[schoolID] as rsschoolID
    ,rsc.[StudentCount]
    ,rsc.[ParentCount]
    ,rsc.[StaffCount]
    ,rsc.[TeacherCount]
    FROM [dbo].[O_PA_ResultCountTotals] rsc WITH(NOLOCK) join
    ODataStore.dbo.SchoolYear as syrs WITH(NOLOCK) on rsc.endYear = syrs.endyear join
    dbo.vw_O_IC_SCHOOL_LIST rssch WITH(NOLOCK) on rsc.schoolID = rssch.schoolID
    where
    (syrs.label = (@PschoolYear)
    or
    syrs.label = convert(char(2),convert(int, substring(@PschoolYear, 0, charindex('-',@PschoolYear)) )-1)+'-'+substring(@PschoolYear, 0, charindex('-',@PschoolYear))
    )
    and
    rssch.schoolID = (@PschoolID)

  4. SELECT
    [endYear]
    ,[staffNumber]
    ,[PrincipalName]
    ,[cryrschoolID]
    ,[cryrschoolname]
    ,[pryrschoolID]
    ,[pryrschoolname]
    ,[sourceID]
    ,[source]
    ,[domainID]
    ,[domain]
    ,[itemID]
    ,[number]
    ,[item]
    ,[comparisonSchoolYear]
    ,[schoolYear]
    ,convert(decimal(5,2),[comparisonMean]) as comparisonMean
    ,convert(decimal(5,2),[principalMean]) as principalMean
    ,convert(decimal(5,2),[comparisonDifference]) as comparisonDifference
    ,convert(decimal(5,2),[districtcomparisonMean]) as districtcomparisonMean
    ,convert(decimal(5,2),[districtMean]) as districtMean
    ,convert(decimal(5,2),[districtComparisonDifference]) as districtComparisonDifference
    ,[ActiveYear]
    FROM [dbo].[O_PA_AnnualComparisonRecords] WITH(NOLOCK)
    where schoolYear = (@PschoolYear) and staffNumber = (@PstaffNumber) and cryrschoolID = (@PschoolID)
    order by sourceid, domainid, itemid

  5. SELECT distinct
    c.[staffNumber]
    ,sy.label as schoolYear
    ,c.[schoolID]
    ,c.[comments]
    ,ua.lastName + ', ' + ua.firstName as PrincipalName
    ,sch.name
    FROM [dbo].[O_PA_PrincipalComments] c WITH(NOLOCK) join
    ODataStore.dbo.SchoolYear as sy WITH(NOLOCK) on c.endYear = sy.endyear join
    O_APPS_DATA.dbo.vw_O_APPS_EmployeeList AS ua WITH(NOLOCK) on c.staffNumber = ua.staffNumber join
    dbo.vw_O_IC_SCHOOL_LIST sch WITH(NOLOCK) on c.schoolID = sch.schoolID
    where sy.label = (@PschoolYear)
    and sy.label <> '10-11'
    and c.staffNumber = (@PstaffNumber) and sch.schoolID = (@PschoolID)

  6. SELECT
    rsc.[endYear] as rsendYear
    ,syrs.label as rsschoolYear
    ,rssch.[schoolID] as rsschoolID
    ,rsc.[StudentCount]
    ,rsc.[ParentCount]
    ,rsc.[StaffCount]
    ,rsc.[TeacherCount]
    FROM [dbo].[O_PA_ResultCountTotals] rsc WITH(NOLOCK) join
    ODataStore.dbo.SchoolYear as syrs WITH(NOLOCK) on rsc.endYear = syrs.endyear join
    dbo.vw_O_IC_SCHOOL_LIST rssch WITH(NOLOCK) on rsc.schoolID = rssch.schoolID
    where
    (syrs.label = (@PschoolYear)
    or
    syrs.label = convert(char(2),convert(int, substring(@PschoolYear, 0, charindex('-',@PschoolYear)) )-1)+'-'+substring(@PschoolYear, 0, charindex('-',@PschoolYear))
    )
    and
    rssch.schoolID = (@PschoolID)

6

Ever used execution plans before?

7

I used an execution plan in a different type of database. How would I use an execution plan in an ssrs report? Would yu show me how to accomplish this goal?

8

  1. Performance test
    baseline performance test. Copy each of these inline queries into SSMS and create test harness for each using different possible parameters. In SSMS on the menu choose the following settings
    a. Display estimated execution plan and b. Include actual execution plan. Then run query and you will see execution plans

  2. Show the results you see using screen shots of the execution plans etc. or describing what you see, which part of the query is eating up more process time than the other etc

  3. From those execution plans one might be able to recommend any indices

1 Like