Report performance massively slower if filtered by prompt

[hespora]

Hi there,


my colleague stumbled across this, and I'm unable to grasp what is happening:

I have a report with one query and several query references, couple pages, nothing big. In the main query, I have a hardcoded filter on "[namespace].[dimension].[field] = 'value1'". Report executes in 10-20 seconds (measured from clicking run button to excel downlod pop-up), depending on different values for value1. Great.

Now we build a prompt page for [field], disable the hardcoded filter, and instead filter on "[namespace].[dimension].[field] = ?pField?". From clicking "finish" on the prompt page to download pop-up takes 2-5 minutes.

What is going on here? My original thought was that it's a cache issue - my colleague has been building all day, probably the query result had been cached, and so the 10-20 seconds we experience with the hardcoded filter is just due to cached results. But even if we, in the hardcoded filter, specify values that he hasn't been testing with, the result comes within seconds on hardcoded filter, and 2-5minutes if using the prompt and parameter.

[RichardP]

Just a couple of quick inquiries...

1. Is the Query used for the Prompt - a separate query?
That is - it only contains columns from the Dimension or are you basing the prompt on the MAIN query itself?

2. Your FILTER uses a Equal sign (=) comparison - so I assume you are only selecting on a single value.
Do you see any better performance if you switch the (=) to an (IN) value ?

3. is this a Relational or DMR Package ?
If DMR - Any chance you are mis-matching the selecting Prompt value with againat a LEVEL vs an ATTRIBUTE ?

4. Have you examined the underlining SQL?
Comparing the SQL with the hard-coded FILTER vs SQL that uses the FILTER based on the prompt value?
Just wondering if another table or two comes into play for the FILTER based on the PROMPT that ...

[hespora]

hey there,

1 - yes, ofc it's a separate query.
2 - nope, no change with "in" (in fact, the original in was used, and I assumed equals would improve performance, but no change)
3 - relational
4 - no change that I wouldn't expect. some 300 lines sql that stay the same, and really just

Code Select Expand

[...] where [...] AND "DIM_xyz"."Field" = 'value'
changes into

Code Select Expand

[...] where [...] AND "DIM_xyz"."Field" = :pField:

[AnalyticsWithJay]

That's certainly strange behavior if that's the only change.

Try simplifying this issue to a blank page with your data container, and a prompt page containing the problematic prompt. If what you're describing is truly the issue, I would expect to see performance degradation using this simple report. I would open a blank report first, and copy/paste the queries into the new report, then the data container, then prompts. This approach will also help avoid any issues that were potentially caused by previous upgrades.

If the issue persists:
For my own sanity, I would first capture the native SQL via a trace and run both directly against the database. The SQL in report studio (make sure you're checking it from Tools->Show Generated SQL, not from the query properties) is not always the same as the native SQL that is sent to the database. This is such a simple report change that I would expect them to be the same, but it's a necessary step in being absolutely sure IMHO.

If performance is the same, I would move on to an IPF trace. I've written an article on how to read and troubleshoot using these traces:
http://www.cognoswithjay.com/troubleshooting-report-performance-with-ipf-traces/

This will allow you to see where Cognos is spending its time, and will help you narrow down the problem further.

[Lynn]

I don't see how local processing would come into the picture but perhaps worth a quick check to see if there are any major differences between native and Cognos SQL from one approach to the other.

Is 'value' a string representation of a number representing a code of some sort? Is it possibly doing implicit conversion somewhere (e.g., without the number enclosed in quotes)?

Perhaps a couple of red herrings....

[hespora]

Probem is solved, although I do not quite understand why or how... ;)

We're in an international company, with the cognos instance having user-driven security: A UK user can only see UK data, a France user can only see France data, and so forth. This already being in place, it's kind of easily forgettable to filter your reports for market manually, and that's exactly what happened here. Without a market filter, the behaviour I originally described is happening. If I filter market, performance between hardcoded and parameterized filter is comparable.

Thanks for all your inputs! :)

[BigChris]

Does that suggest that there's a table being used in your report that isn't being filtered as you're expecting? Might be worth checking (or getting someone else to check  ;) ) that the data security has been set up on all of the tables...I know it's easy to miss when you're adding stuff to FM.

[hespora]

yea, something like that is what I am assuming - but I'm just a measly report designer; I know too little about admin or security to go from here. I'm giving this to my IT to figure out. ;)