Ad-hoc Query performance

building reports webintelligence
1

Hi,

Is there any impact on query performance by enable or disable Universe SQL parameters.

Please give me different scenarios to improve performance for Ad-hoc querying.

Any ideas please share.

Regards,
Chandra Sekhar J.

jinkaleo :india: (BOB member since 2007-01-25)

2

It might be better to concentrate on the problems you are having, rather than asking for examples from other people. Tell us some more about your situation.

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Here My problem is Ad-hoc query performance , before it was taking only 5 min to generate a report but it is taking now 15 min. only Differennce is one new column added to adhoc tables(not using in query) and little data valume is incresed compare old data. attached is the query.

jinkaleo :india: (BOB member since 2007-01-25)

4

This may be the cause of the problem. When volume of data in tables changes, then a database query optimizer may decide to use different execution plan.

Talk to you DBA and ask him/her how to tune the performance. Maybe a new index is needed. Maybe some tables need to be partitioned. Maybe just new table statistics need to be gathered. It all depends on the database that you use and on how significantly data changed.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Hi, I have a doubt where BO query will be executed, is it BO internal repository or on schema which we are giving while installation.

 What are the functions of BO repository and RDBMS which we see on BO architecture with respect to query execution.

Please clarify these queries. Thanks in advance.

jinkaleo :india: (BOB member since 2007-01-25)

6

klnreddy,

The BO repository is a collection of knowledge about your RDBMS schema – by virtue of the activity that a knowledgeable BO Designer user performs, it learns the names of the tables and the columns of those tables. It also learns about how some column(s) from one table might relate to column(s) of another table. All of this knowledge is known as the “meta data”. When a query is constructed, the user selects the objects that are presented and chooses which ones to display and/or filter upon. Once the the button “run query” is pressed, the meta data assembles its “best guess” construct of an SQL and passes that to the RDBMS. The RDBMS receives it and passes it to an optimizer (this part is dependent upon the vendor, some vendors optimize better than others). Once the SQL code is passed to the RDBMS engine, it scans the table(s) for the required data and brings it back to BO, where BO formats and makes it pretty to appear on your screen. I know that this a simplistic view of the operations, but hopefully it suffices your questions.

Thanks,
John

jsanzone :us: (BOB member since 2006-09-12)

7

Thank u very much Zone.

With your reply I got some Idea and I feel happy. RDBMS which you have been mentioned in the mail is part of BO architecture or the RDBMS schema which we give while Boxi installation.

Regards,

KLN.

jinkaleo :india: (BOB member since 2007-01-25)

8

KLN,

It depends. If you install BO and don’t have an RDBMS established, then MySQL, the open source SQL engine, is loaded by BO as a part of the install process. If you do have a copy of your own bona-fide RDBMS software then that is your RDBMS schema.

Thanks,
John

jsanzone :us: (BOB member since 2006-09-12)