Now I understand. Cognos would read data for all the dates ( for 3 years in my case, so a 1000 dates ). It would then pull all 1000 rows in Cognos, and then it would filter only yesterdays date to show in the report.
This also explains why Cognos sometimes creates two separate SQL queries. Something like:
SELECT * FROM Table1
SELECT * FROM Table2
Cognos would fetch both tables, and then Cognos would create a join between these tables internally. Because Cognos is much slower than database, this means that report would be generated much slower.
Yesterday, I have changed current_date to CAST( #$current_timestamp# , date ) in one report. Execution time for this one report went down from 17 minutes to 1 minute. This is a huge improvement. This is definitely something to be aware of.
I don't have a clue why current_date made Cognos to process data locally. Report is simple, one crosstab and a few simple filters. Database is MS SQL, Cognos is 11.2.2. Model is simple, it is made in Framework manager and it is CQM.
------------------------------
Milan Milovanovic
------------------------------
Original Message:
Sent: Tue May 28, 2024 01:51 AM
From: Philipp Hornung
Subject: CAST( #$current_timestamp# , date ) much better than current_date
Hi Milan,
This is called lokal processing: Cognos pulls raw data from the database and transforms locally on the Cognos server. There are various factors that may have an influence:
- database type and version
- Cognos version
- Dynamic / Compatible Query Mode (if Framework Manager)
- Framework Manager/Data Module modelling/complexiity
- Report modelling/complexity
If you get this even with simple data models and reports I recommend to contact the IBM support. We got similar issues fixed in a Cognos fix pack.
Best regards
Philipp
------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
Original Message:
Sent: Mon May 27, 2024 06:54 AM
From: Milan Milovanovic
Subject: CAST( #$current_timestamp# , date ) much better than current_date
Query is much faster if instead of filter:
[Date] = _add_days( current_date, -1 )
we use:
[Date] = _add_days( CAST( #$current_timestamp# , date ), -1 )
When we use current_date, SQL code is something like this, there is no WHERE clause in it.
SELECT * FROM Table
If I try this version of a report, it is slow even if I query yesterdays data. Yesterday, it was Sunday, so there is no data for that day, but report is still slow.
When we use CAST( #$current_timestamp# , date ), SQL code has WHERE clause, something like this:
SELECT * FROM Table WHERE [Date] = '2024-05-26'
The speed is the same if both queries are cached, but if they are not, then macro version is much faster.
I don't know why is this happening.
------------------------------
Milan Milovanovic
------------------------------