x

Heavy MDX Query

Hi,

We're using SAP BPC on a .net server and it's calling SAP BW via an MDX query. We've run SQL trace on BW during the MDX query and identified a single SQL statement (attached) that takes on average 30 seconds to process.

The underlying table being queried holds 11 million records. Data storage is on XIV and our DB is on a P7 running AIX and Oracle 11g. We would like to reduce run times to sub 5 seconds. We do not believe hardware (CPU, memory, IO) is a constraint, but rather that the query is inefficient and that it takes Oracle time to process all the joins and aggregate the data.

We've run a SQL Explain (attached below) to identify the expensive parts of the SQL. Which parts of the SQL are expensive? If we pre-aggregate the table that has 11 million rows will this significantly improve response times? Or is there something else we are overlooking (Oracle tuning, indexes)?

Looking at the SQL Explain:

  • "TABLE ACCESS BY INDEX ROWID" has a high cost (92) but has 0 %CPU
  • "SORT UNIQUE" and "VIEW" are expensive (25 cost, 88 %CPU).
  • "HASH JOIN" (128 cost, 10 %CPU)

SQL Statement

SQL Explain

We've been looking at this problem for many weeks and have not been able to make much progress. The slow response times are really hurting the business users who run and refresh reports many many times throughout the day, any advice or guidance is appreciated.

Thanks for your help

Brendan

sql statement.pdf (16.7 kB)
sql explain.pdf (26.2 kB)
more ▼

asked Aug 08, 2012 at 09:25 AM in Default

Brendan2012 gravatar image

Brendan2012
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest
Hi, any ideas out there....? Thanks, Brendan
more ▼

answered Aug 10, 2012 at 11:30 AM

Brendan2012 gravatar image

Brendan2012
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x716
x378
x65

asked: Aug 08, 2012 at 09:25 AM

Seen: 986 times

Last Updated: Aug 10, 2012 at 11:30 AM