Business Objects Performance

[jakeH]

Hi,

Hope someone can come up with an answer to this problem.

I'm running a report in BO which times out. The universe limits are 10 minutes and 50,000 rows and the report has two prompts, one for "begin week" and the other, "end week".

When I copy the SQL and paste it into a query tool and run it, the query always comes back with results (349 rows) in approx 5-8 minutes. The only change to the SQL is that I have to remove the @variable syntax and enter the specific week numbers that I want to query on.

Why does BO not return results? What is the overhead that BO is adding to the query process? How can I get round this problem?

Thanks.

 

[skrandel]

Give us more info.

What version of BO? What's the database? What is your database connectivity?

Does it ever complete? What if you remove the 10 minute limit? Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

If you are working with ORACLE there is an interesting issue to be looked at here:

http://www.businessobjects.com/services/InfoCenter/tips/unvdes/tip/ut002/ut002.htm

By the way,

Are you sure you are satisfied with query performance that takes 5-8 minutes in the first place............? T. Blom
Information analyst
tbl@shimano-eu.com

 

[josephjmendicino]

Do you have a datatype mismatch? What database are you using, and does the week column in the database have an index?

 

[jakeH]

Thanks for the responses so far.

The database is NCR Teradata and the version of BO I've been using is 5.13, but the problem also occurs in 5.02 and 5.15.

In response to Steve's question:

The report will complete if I run it without the time limit but it is still significantly slower compared to running the report in my SQL query tool (NCR Queryman).

And to Tom's question:

The performance is acceptable considering the fact table is 9 million rows and 4 of the reference tables are approx 500,000 rows each. The query is also pulling back information for a whole year.

And to Joseph's question:

The fact table is at date level (which is indexed) and the calendar table index (where the financial week is obtained from) is at date level.

Hope the additional info helps!!!

 

[jnicho02]

I'd take a look to see whether it is using indexes properly. Some data servers don't get to use an index if you are using a variable rather than an actual value.

Can you define variables in your query tool and look at the equivelant of showplan? Jeremy Nicholson, Director of

http://www.firstlightassociates.co.uk

a UK-based Java and Data Warehousing consultancy

 

[jnicho02]

.....assuming that NCR Teradata has indexes...I don't know it....I hope i'm not demonstrating my ignorance! Jeremy Nicholson, Director of

http://www.firstlightassociates.co.uk

a UK-based Java and Data Warehousing consultancy

 

[deaco]

Completley off the ball but it's worth checking that the user id that your connection, setup in supervisor is using, to connect to teradata is the same one as you are using in your query tool. If the id that the connection in BOj is using is different, it may be running out of spool space, in which case BOj doesn't tell you this.

What about if you enter the date ranges manually into the condition rather than leaving it as a @variable. If this still times out then I would look at your universe and see if something stupid is going on. If you want to check properly what your query is doing then run an 'explain' in your query tool.

Thanks

Simon Deacon
email : int_deacon @ hotmail.com

And for Jeremy Nicholson, yep Teradata does have index's.


Can you let me know if this helps, I don't get much chance to check the forum these days but if you update it I will find out someday.