6 Replies Latest reply: Mar 4, 2013 3:28 AM by 745383 RSS

    Different SQL Generated for a query

    745383
      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
          Any ideas?
          • 2. Re: Different SQL Generated for a query
            GRK
            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
              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
                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
                  Can u share the sample queries which are generating the same?
                  • 6. Re: Different SQL Generated for a query
                    745383
                    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!