This discussion is archived
6 Replies Latest reply: Mar 4, 2013 1:28 AM by 745383 RSS

Different SQL Generated for a query

745383 Newbie
Currently Being Moderated
Hi all,

I noticed that running the same report in OBIEE (keeping the same prompt selections) generates different SQLs every time. The query in general is the same but some intermediate queries have their columns' order changed or alias used (SAWITH0, SAWITH1, etc) is different. This creates some problems when trying to tune the queries in the database.

Does any one know a way of "locking" the SQL generated? Any option in the RPD or config files?


Thanks!
  • 1. Re: Different SQL Generated for a query
    745383 Newbie
    Currently Being Moderated
    Any ideas?
  • 2. Re: Different SQL Generated for a query
    GRK Journeyer
    Currently Being Moderated
    User,

    Disable the cache on those tables from both rpd and answers.
    So, now everytime it will hit the Database rather than hitting the logical columns - or cache.

    Try the above method and see now whether your query is same or not everytime.

    Thanks.
  • 3. Re: Different SQL Generated for a query
    Sasi Nagireddy Expert
    Currently Being Moderated
    Hello User,

    Purge the Cache Manually and starting looking the query u will find the difference:-

    Ways to Purge the Cache Manually

    Go to Analytics and Create Direct Database Request with "DelCache"."AnalyticsWeb"
    CALL SAPURGEALLCACHE()

    Call SAPurgeAllCache() -- Deletes all cache info
    Call SAPurgeCacheByTable( 'Datwarehouse', '','DWH', 'XW_SALES_F'); -- deletes specific table cache
    Call SAPurgeCacheByDatabase( 'Datwarehouse' ); -- Deletes all cache related to a specific database
    Call SAPurgeCacheByQuery('SELECT 0 s_0, "Sales Subject Area"."Time - Dimension"."MONTH_NAME" s_1, "Sales Subject Area"."Fact Sales"."COST_PRICE" s_2 FROM "Sales Subject Area" ORDER BY 1, 2 ASC NULLS LAST ; ') -– Deletes specific logical SQL Cache

    Do try this and let me know the updates.

    Mark as correct if it is helpful.

    Thanks.
  • 4. Re: Different SQL Generated for a query
    745383 Newbie
    Currently Being Moderated
    Hi all,

    Thank you very much for your responses.
    I did try clearing the cache for the individual query I am testing but still the queries generated are different. If the query is complex enough to have sub-queries than these change at every execution. If I do find any solution for this I will post here.

    Thank you very much!
  • 5. Re: Different SQL Generated for a query
    Sasi Nagireddy Expert
    Currently Being Moderated
    Can u share the sample queries which are generating the same?
  • 6. Re: Different SQL Generated for a query
    745383 Newbie
    Currently Being Moderated
    Hi,

    take the following example. The same report issues these two different queries after cache cleared every time for this purpose.
    You can see the code logic is the same but the SQL generated is different which does not permit to, for instance, force it to a profile in the database.

    1.
    (select sum(T64332.COLUMN1) as c1,
    sum(T64332.COLUMN2) as c2,
    D3.c4 as c3,
    D3.c8 as c4,
    D3.c5 as c5,
    D3.c7 * 100 + D3.c5 as c6,
    D3.c6 as c7,
    ROW_NUMBER() OVER (PARTITION BY D3.c7 * 100 + D3.c5 ORDER BY D3.c7 * 100 + D3.c5 ASC) as c8

    2.
    (select sum(T64332.COLUMN1) as c1,
    sum(T64332.COLUMN2) as c2,
    D3.c3 as c3,
    D3.c6 as c4,
    D3.c7 as c5,
    D3.c4 * 100 + D3.c7 as c6,
    D3.c8 as c7,
    ROW_NUMBER() OVER (PARTITION BY D3.c4 * 100 + D3.c7 ORDER BY D3.c4 * 100 + D3.c7 ASC) as c8

    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points