Cognos Analytics

 View Only

  • 1.  DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 09:36 AM
    Reply
    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


  • 2.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 09:52 AM
    Edited by System Test Fri January 20, 2023 04:09 PM
    Reply
    Hi,

    Are you using the same data source in your FM project for all query subjects?

    At Query Processing property of data source, select Database only. May help.


    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 3.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 10:13 AM
    Reply
    Yes, these two query almost identical we changed only date filter.

    ------------------------------
    Ali KEÇE
    ------------------------------

    Original Message


  • 4.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 10:03 AM
    Edited by System Test Fri January 20, 2023 04:33 PM
    Reply

    Hi Ali,
    I remember I had such an issue with a query which performed well on de database server, but not in Cognos.

    I changed the setting 'Use local cache' to 'No' in all the queries and that worked. We saw no local processing anymore and the correct query sent to the database server.

    Hope this helps.



    ------------------------------
    Thomas van der Meer
    ------------------------------

    Original Message


  • 5.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 10:19 AM
    Reply
    Hi Thomas,
    I tried it too. 
    Sometimes changed to "Use Local Cahe=No" works fine, sometimes doesnt work.

    I can not understand why cognos try to be like database server?
    Its not your job, right? We have oracle exadata and it work fine.

    ------------------------------
    Ali KEÇE
    ------------------------------

    Original Message


  • 6.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Fri March 12, 2021 10:42 AM
    Reply
    Hi Ali,
    I also use a query reference. So the result query has the union query as the source. And still all queries with Use local cache set to No.

    ------------------------------
    Thomas van der Meer
    ------------------------------

    Original Message


  • 7.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Mon March 15, 2021 09:35 AM
    Reply
    Hi Ali,
    It sounds like you're using an Framework Manager model.
    The two governors that influence the SQL formulation for this situation the most are:
    1 - (DQM) Local Cache Policy
    2 - Allow usage of local cache
    You could also check if the union option for Duplicates is set properly. The default is to remove duplicates, which is relatively expensive compare to preserve. 
    //Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 8.  RE: DQM Behavior against Union or Join queries (reports run slowly)

    Posted Mon March 15, 2021 09:54 PM
    Reply

    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


Related Content