difference between using “date in_range ?p1?” and “date between ?p1? and ?p2?”?

[Echo_ada]

Hey everyone,

i'm kinda new to cognos and experienced this week an odd behavior in report studio.

Database is oracle 11 g, Main-table is keeping dimensions as well as facts (I'm aware that this is not a good model, but by now not changeable), Range-partition(month) by date-column (call it TRANSACTION_DATE), roundabout 220 mn rows, about 1 mn in each partition.

Cognos is 10.2.1.
I created a report with a filter on TRANSACTION_DATE and a prompt page to select the range to be analyzed. Parameters are set with two date prompts (UI = Calendar). Filter is "TRANSACTION_DATE between ?p_range_from? and ?p_range_to?" with some additional filters to narrow it further down.

On execution with ?p_range_from?= 01.01.2014 and ?p_range_to? = 30.09.2014 it takes up to 10 minutes to create the report.

When I change the prompt UI to Edit box with range = YES and the filter to "TRANSACTION_DATE in_range ?p_range_from?" the report retrieves the data within one minute. (for the same range of TRANSACTION_DATE)

Has anyone experienced something similar? Does it matter that I'm querying on the partition column? My expectation was that this is a fast way to reduce the amount of rows to be scanned. What is the difference between using "date in_range ?p1?" and "date between ?p1? and ?p2?"?

Thanks for any information.

[cognostechie]

You are comparing the expression two different expressions on two different columns so that doesn't give a fair picture. Compare the 'in_range' and 'from' and 'to' on the same column . What is the data type of the 'range' column? Is it character? If so, is there an index on that? What is the data type of the 'Transaction_Date' column? Is it Date? That would make a whole lot of difference? Even if both the columns are Date data type, do both of them have the same type of index?

Most importantly, check the SQL that the report is generating. That will tell you what is the expression that is actually being sent to the DB. The 'in_range' is a Cognos expression and may nor neccesarily become part of the query which is being executed in Oracle.

[Echo_ada]

Thanks for your response.

Maybe my description wasn't precise enough. I filtered my query in both versions on the same column which is TRANSACTION_DATE which is of DATE type. Just changed the expression from ... between to ... in_range
There is no index on this column as the table is partitioned by this column.

[cognostechie]

Are you selecting the same Date range when you use 'in_range' and 'From' and 'To'?  As I said earlier, check the SQL that it generates for both the scenarios. From the 'Tools' menu, select 'Show generated SQL/MDX'. If the date range is the same then you will definitely see the difference in SQL between both the scenarios.

[Echo_ada]

Yes i did use the exact same Date range.

On the generated SQL it shows the following:

on selection "... in_range": (note that FKT_GU_BUCH_DAT is the original name of my so called TRANSACTION_DATE column)
"FKT_GU_BUCH_DAT" between TIMESTAMP '2014-01-01 00:00:00.000000000' and TIMESTAMP '2014-12-31 23:59:00.000000000'

on selection "... between":
"FKT_GU_BUCH_DAT" between :PQ2 and :PQ1

So is it that in this second scenario cognos is retrieving the whole Set from database and applying the filters only afterwards?

[cognostechie]

The SQL you are seeing in the second case is not the actual SQL but it is only showing the parameter names. The values that you select from the Prompts should be embedded in the SQL. I am also surprised that 'between' function is in both the cases. Did Cognos generate these filters or did you write those filters manually? Try to run a trace on the DB and see if you can track the SQL that goes there?