cancel
Showing results for 
Search instead for 
Did you mean: 

sql execute about 1s,but report run time 20s

Former Member
0 Kudos

I execute the sql which is copied from webi query in database, it take less than 1s.

There are 6 queries in report, so I assume the time used to retrieve data from DB is 6s.

and the report responding time is about 20s(from the moment I click "refresh data").

so there are 20-6=14s,including network transfer time,report server generate report time.

Could anyone tell me what can I do to optimize the performance? Normally the report

responding time can reduce to how many seconds?

Thanks in advance.

Regards,

Tony Renjie

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Hi Tony,

why do you use 6 queries in your report? Is it possible to get all data with one query? Do you have any charts? What kind of data source are you talking about here. Do you use a native or an ODBC driver in your universe? Does your report have a simple or complex layout? Do you let your universe connection remain open and be shared (Check the Connection pool mode in the properties of your universe connection)?

Regards,

Stratos

Former Member
0 Kudos

6 queries, their condition is different, some is for calculate Current Period data,some is for YTD data and full year data.

No charts, sql server2005, Use native driver, I think the layout is not very complex, how to set the universe connection that can improve the report performance? thanks again.

Regards,

Tony

0 Kudos

Hi Tony,

do you use a union of queries or do you merge the data over the "merge dimensions" button?

Regards,

Stratos

PS: Connection sharing: Start in the universe designer, open your universe and select "File->Parameters" . Choose to edit your connection and navigate to the screen where the "Connection pool mode" setting is visible. Let your connection be shared, save and export your universe.

Former Member
0 Kudos

Hi Stratos,

I don't click "merge dimensions ", it automatically merge the same name column.

In the report, I used many variables, I think it also take many times,right?

my Conection type is Secured, not Shared, Shared is faster than Secured?

Regards,

Tony

0 Kudos

Hi,

please consider the following for optimizing the performance:

1) Reduce the number of variables you are using. Let your DB server do as many calculations as possible.

2) If you have a single universe please consider using a union of queries instead of 6 separate ones in the WeBi Query Panel (Use the "Add a combined query" instead of the "Add a new query button").

3) Edit in the properties of your connection (Screen 3 of 4 in BO XI 3.0) and select "Keep the connection active for" in the "Connection pool mode" setting.

Regards,

Stratos

PS: The connection type should not have any impact in the performance.

Former Member
0 Kudos

Stratos,

Many thanks for you suggestion,I will try them one by one.

Regards,

Tony