I use the "Show Generated SQL" tool a lot during development and debugging of reports. But we have a report now which takes a very, very long time (as in 2 hours) to get a response. Seriously, I click the option in the Tools menu and wait for 2 hours before it finally presents me the prompt page where I can supply the parameter values to complete the query definition. I then specify the parameter value, click OK, and the SQL is generated shortly afterwards.
What could possibly be causing this behavior? I have not seen it in other reports.
The particular report is a list of accumulated values (mostly sums) with grouping over 3 columns. Nothing very special, in my opinion.
Are any of the filters looking at a fact table query item to render a pick list for one of your parameters? The engine would need to do a select distinct to present the choices and that might cause the behavior.
I am very sorry for the lack of followup...thought I had marked this as Notify, but apparently not.
Lynn is, of course, right. There was a pick-list prompt involved which was doing a scan across a very large fact set.
Our bad.
Quote from: cccschip on 10 Nov 2011 10:32:20 AM
...
Lynn is, of course, right.
...
8)
This is not always the case so I will bask in my moment of glory!