same stored proc being called in 12 datasets - report very slow

  • hi,

    execution time of my stored proc is pretty fine. issues occurs in the report.

    I have around 12 datasets that call the same proc. however, it takes substantial amount of time to load the report, this is because of the 12 datasets.

    has anybody tried a way... that the data is generated once and stored in a table perhaps... (dynamically) and then all the datasets refer to the newly created table instead of calling the stored proc again and again...

    or is there any other way ?

  • In Report Manager go to the report.

    Go to the Properties tab and select Execution.

    You can cache the report, or render it from a snapshot to improve report run time.

  • Why do you have 12 datasets? Can't you just use 1 dataset in multiple report regions/items? Are the datasets bring back different data because of different parameters?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • yep... the 12 datasets bring in different data values...

  • Without seeing the report and the business use/case for it I'm not sure what to offer. My gut tells me that you need to look at developing a procedure that returns all the data for the report in one trip to the database and then use filters in the report to display the data in the manner needed.

    What are we talking about for row counts?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • thanks... will do the required changes and get back with the updates...

    Jack Corbett (8/5/2010)


    Without seeing the report and the business use/case for it I'm not sure what to offer. My gut tells me that you need to look at developing a procedure that returns all the data for the report in one trip to the database and then use filters in the report to display the data in the manner needed.

    What are we talking about for row counts?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply