+ New Ticket

Analysis of Query Plan



  • Avatar
    George L'Heureux

    Hi Mrinal!

    I'm glad to try and help, although without knowing your data or much about what you're trying to accomplish it's going to be limited.

    My first observation is that I think your error is starting in Snippet 4, not Snippet 6.

    My second observation is that you have some very large tables and that it may be useful to perform a full GENERATE STATISTICS on all of the tables that are part of this query. That should help the optimizer find a better query plan if possible.

    If this query has run successfully in the past and only now is showing problems, you may want to examine the data in each table to see whether there are records with duplicate key columns, which could be causing a Cartesian or a Cartesian-like join.

    Sometimes I've found it useful to make the query small first, performing only one of the joins I need, and making sure that the cardinality etc of the result of that small version looks right, before adding a second, and then a third, fourth, etc., joins.

    Best wishes

    George from Aginity

    Comment actions Permalink
  • Avatar
    Mrinal Bhatt

    Hey George,

    So here we updated a cube with new metrics and some report limitations. 

    Then running it to get published takes ages as it gets stuck here.

    Before running this process we create tables

    create temp table dmt_build_trademark_brand_key as
    rank() over (order by trademark_brand_desc) as trademark_brand_key,
    from (select distinct trademark_brand_desc from trade_item_v) a;

    CREATE TEMP TABLE dmt_pres_brief_main_calc_1
    cat_nbr INTEGER,
    cat_desc NVARCHAR(400), /*Addition of DESC to resolve INSERT more than TARGET*/
    cnsmr_id_key BIGINT,
    site_key BIGINT,
    trademark_brand_key BIGINT,
    trademark_brand_desc NVARCHAR(400), /*Addition of DESC to resolve INSERT more than TARGET*/
    trnfm_last_yr_nbr INTEGER,
    sales numeric(38,2),
    units numeric(38,0),
    trips bigint
    DISTRIBUTE ON (cnsmr_id_key);

    cat_nbr INTEGER,
    cat_desc NVARCHAR(400),
    cnsmr_id_key BIGINT,
    site_key BIGINT,
    trademark_brand_key BIGINT,
    trademark_brand_desc NVARCHAR(400),
    trnfm_last_yr_nbr SMALLINT,

    This did run before but years back, the only change done to it was addition of few metric and report filters.

    Comment actions Permalink
  • Avatar
    George L'Heureux


    Thanks for the information. Since none of the tables you just supplied the CREATE sql for are used (it doesn't seem) in your original, problematic, query, they probably are not involved in the issue or its solution.

    Unfortunately I will probably have to suggest that you collaborate within your team to identify possible issues.

    If you have tried the suggestions above:

    • Generate Statistics on all involved tables
    • Examine involved tables for potential data irregularities
    • Construct smaller queries to test performance/results of each join independently

    And these have not provided you any clarity, you may want to involved other data Subject Matter Experts on your team.

    If you haven't already tried it, you might give Aginity Pro or Aginity Team - our latest products - a shot. These allow you to stash SQL into an Active Catalog that you can then leverage to build "object-oriented" SQL one "brick" at a time. Especially if you're trying my third suggestion, above, this could be helpful, as it avoids having to rewrite queries over and over again, avoiding the risk of making a typographical error that could set you back.

    Best Wishes

    George from Aginity

    Comment actions Permalink

Please sign in to leave a comment.