Hi everybody,
The problem encountered is the behaviour of Query Studio when preparing a list of prompts (reference values) to create a filter and select records from a detail table (facts) using then one ore more of these values to set up the inner join.
Expected behaviour is: I fill in the list of prompts from a reference table (containing all distinct values of the field considered as criteria in the facts table) and then I run the select on the big table. Doing so, the performance is excellent as I avoid running a "select distinct" on this field in the facts table (the big one). I mean, references tables are pre-built to avoid decreasing performance when filling in the list of values (prompts).
But... although I force the underlying meta-data to use the reference table when building the query in Framework Manager, this results anyway in a query like "select distinct" on the facts table with a join on the references table (visible with a SQL trace), therefore still reading the entire facts table and resulting in a very bad response time...
And I can't use option "limited data" to restrict (and speed up) selection from the facts table as it would indeed result in omissions in the "select distinct" for the prompts list!
I know this problem doesn't exist using Report Studio, but this is really a critical issue for us as 80% of our users will be using the Query Studio as a maximum, with regard to their knowledge and technical level (they are definitely not advanced users...)
Any idea on a solution?
Any help or advice will be much appreciated, thanks in advance.
Kindest regards,
Yves
I feel this is how RN will create the sql. when contacted one of the DBA person he said select distinct is used for performance improvement. I think select distinct in fact table is used on the values retrieve by the table. As u also said there is a relation b/w ur fact and reference table in sql.
Well anyhow let me find how to tell QS not to use select distinct as we do in RS.
Srik
What's the status of this topic?