BO performance

general discussion
1

Performance is a BIG, BIG problem with the BO 4.1. Has anyone got tips or
tricks or various techniques to improving performance ?

Listserv Archives (BOB member since 2002-06-25)

2

Abhijit,

You can try weighing the tables in the Designer module. This will place row
counts for each table to place them in an optimized order in the from
clause. This can greatly improve your query speeds.

Thanks!
-rm

Listserv Archives (BOB member since 2002-06-25)

3

I can tell you one of the things that happened to us when we upgraded from
BO3.1 to BO4.1.

When the “Multiple SQL statements for each measure” option is checked on for
your universe, if you have multiple measures in your query (that rely on
different tables) then BO4.1 generates multiple SQLs. In BO3.1, this
multiple SQL generation did not exist.

We had one query that we ran in BO3.1 that pulled objects from two tables, A
and B. We had one measure object based on table A (something like
COUNT(DISTINCT A.AUDIT_ID)) and another based on table B (like
SUM(B.UNITS_FAILED)). In BO3.1, this generated a single SQL:

    SELECT A.LOCATION, COUNT(DISTINCT A.AUDIT_ID), SUM(B.UNITS_FAILED)
    FROM A, B
    WHERE A.AUDIT_ID = B.AUDIT_ID (+)

But when we migrated our universe to BO4.1, the “Multiple SQL statements”
option got turned on, by default I think. This means that the above query
run in BO4.1 got split into two:

    SELECT A.LOCATION, COUNT(DISTINCT A.AUDIT_ID)
    FROM A

    SELECT A.LOCATION, SUM(B.UNITS_FAILED)
    FROM A, B
    WHERE A.AUDIT_ID = B.AUDIT_ID (+)

So now instead of one query, we’re running two. The second 4.1 query alone
takes as long as the original 3.1 query, plus however long it takes to run
the first 4.1 query.

Take a look at the SQL being generated by your queries. You may be
surprised by what you find. I think for BO3.1 universes migrated to BO4.1,
you might as well turn off both “Multiple SQL statements for each context”
and “Multiple SQL statements for each measure”, since BO3.1 did neither of
these.

Erich Hurst
Compaq Computer Corporation

“It is so easy to break eggs without making omlettes.” – C.S. Lewis

Listserv Archives (BOB member since 2002-06-25)