Let me start by agreeing with @HENK CAZEMIER: take a look at whether you're preserving or removing duplicates (i.e., performing a UNION vs. a UNION ALL). It seems whenever I need a union, it's in circumstances where there's no possibility of duplicate rows, so dispensing with the costly need to check for them is a significant savings.
But if the difference in processing time (i.e., the cost) of your SQL query alone, vs the cost of Cognos running it is significant enough for you to raise this question, it's likely not to be so simple. I've certainly seen this kind of situation before, where the SQL I know will return the desired results runs fast, and the Cognos version runs very slow. And the proper solution to this problem is to run down why. Most likely, if you look at the queries Cognos is actually sending, they will not be united with a UNION operator, but be separate, such that Cognos is performing the UNION on the Cognos server rather than passing it down to the database and letting the DB server do the work (in Cognos parlance, it's a "stitched" query). But the problem might not be the union itself, but elsewhere in the query.
Troubleshooting and fixing this involves a non-trivial amount of work, and always seems to require more time than I have. So I often instead implement the "wrong" solution to the problem, for lack of time to do the right thing. I write my UNION [ALL] query myself, and use a SQL object within my report (using Native SQL, but you might need Pass-Through SQL, depending on your environment), so that my report runs [almost] as fast as the SQL I think it should be running.
There are valid reasons not to use this solution. Your code will not be portable; your report will not be responsive to any changes in your data model made in Framework Manager, etc., etc. But this "quick and dirty" approach may resolve your performance issue while saving you many hours of problem isolation and rewriting in the Cognos environment.
No doubt, there's a solution to your performance issue to be found without taking such a shortcut, and you'll find it given enough time. The question is, in a production environment, will you be given enough time?
Hope this helps. Best of luck.
------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------
Original Message:
Sent: Fri March 12, 2021 09:35 AM
From: Ali KEÇE
Subject: DQM Behavior against Union or Join queries (reports run slowly)
We have some reports that has a lot of row on it and their queries are union. And that reports run very slowly. When run union query on database server it returns less than a minute.
When we run that reports and see what is going on database server, we saw two separate query, that means cognos DQM mode send two separate query and try to union on its own. Thats why our reports run slowly.
Than we try to change queries behavior to make cognos send "union query" as it show as when we click "Genereted SQL". Changed "Rollup Processing" settings to "database" doesnt help.
Finally we changed Model, "dynamic" to "compatiple". And its worked fine, after that change cognos send only one union query to database server.
The question is "How we change dqm behavior against union or join query?"
DQM mode, should send single query.
------------------------------
Ali KEÇE
------------------------------
#CognosAnalyticswithWatson