none
report calls a query multiple times

    Question

  • Hi all

    I found this strange behaviour when I was looking at SSAS traces of a specific reprot.

    It seems that if you configure report parameters to get default values from a dataset, then the query of this dataset will be executed multiple times. This has a very negative impact on performance.

    I've tried to change the advanced settings to "never refresh" but it didn't work.

    Thanks

    Friday, June 14, 2013 6:41 AM

All replies

  • Stanhe,

    Perhaps I am misinterpreting what you're saying, though I think you're problem can be solved by checking 'Process transactions in a single query' on the related data source which your data sets are querying from. 

    Regards,

    Craig

    Friday, June 14, 2013 9:50 PM
  • Let say I have in my report 3 parameters all getting their default values via data set/MDX query:

    • param1 - MDX1
    • param2 - MDX2
    • param3 - MDX3

    What I've noticed is that e.g. MDX1 will be executed multiple times, first to get param1 default value and... I assume that additionally param1 will be reloaded 2x, when default values for param2 and param3 are set, i.e. MDX1 will be executed 3 times...

    Monday, June 17, 2013 10:45 AM
  • Well as far as i know this is general behavior that if you are having parameters which consume values from dataset fields. the query seems to run multiple times ( 2 times if you have 2 params using the fields of that datset).

    But if you check the profiler, the subsequent executions of the datset will be against the cache.

    Monday, June 17, 2013 2:52 PM
  • Sure, the subsequent queries are answered from cache... Nevertheless these queries are consuming server resources, e.g. query pool threads etc., which is really bad in multi-user scenarios...

    Is there any way to "switch off" this behavior?

    Monday, June 17, 2013 2:58 PM
  • Hi Stanhe,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Wednesday, June 19, 2013 2:19 AM
  • Hi,

    You can check from this article http://blogs.msdn.com/b/jtupitza/archive/2011/11/23/improve-the-performance-of-reporting-services-parameterized-reports.aspx, especially under topic "How to Disable the "Cascading" Behavior of Multiple Parameters"


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, June 19, 2013 3:24 AM
  • Hi christian,

    i guess the issue stan mentioned is as described below.

    There is a report which two parameters . lets suppose they are called p1 and p2.

    we have a dataset(d1) which is getting me two fields called cur month and cur year

    iam mapping my parameters to pick values from the above dataset . And in this case p1 and p2 are not dependent on each other ( no cascading effect)

    Now if you run the report and check in profiler you will notice that the dataset (d1) query is executed twice.

    Why is this happening and how can we stop this.

    Wednesday, June 19, 2013 7:24 PM