Webi 6.1a Performance

server administration Web Intelligence 6.x Servers
1

Hi All,

We have a very large SQL Server 2000 database running on a 4 (hyper-threaded)-processor server with 4G of RAM. The server has 1.8TB of DASD currently attached. The database currently houses about 350G and is projected to reach and maintain roughly 1TB within 80 days.

We are rolling out to 100 Webi users in 30 days. They will all have ad-hoc reporting permissions.

We cannot reduce the size of the database or the number of users. We can tune the database and efforts are in progress to do just that.

All things considered, we get decent responses when querying this database via Query Analyzer, custom applications using ADO, and Business Objects Full Client. When we use WebIntelligence, performance suffers. We routinely see Webi reports take 6 to 15 times longer to return data than the exact same queries executed in any other manner (Query Analyzer, custom application, Full Client).

Before someone offers that oh-so-helpful suggestion that I read the documentation or execute a Search first, please be advised:

  1. Although this is my very first post I’ve been searching this board for 6 months now. I’ve learned and applied a lot here. For that I am thankful and in your debt.
  2. It doesn’t matter where we execute any of these queries, the results are consistently worse for Webi. For instance, I have executed the Full Client report and Webi query from the Webi web server at the same time; I’ve executed them from the Webi web server at different times to rule out statistics-based query caching performed automatically by our SQL Server. I’ve executed these reports under varying amounts of stress - from only one user (me) to eight users. Increasing the number of users tends to affect Webi worse and sooner, but it is not a measurably linear relationship. I have also executed reports and queries in every permutation I can conceive with the same results - Webi performace is 6 to 15 times worse than any other method - and all other methods are consistent in their performance.
  3. Experience has shown that tuning the database masks the issue. I am interested in and working on tuning the database. I am not interested in masking the issue. At the current prices charged for the product, a 6 - 15 performance multiplier on a one-second query is just as unacceptable to me as it is on a 10-minute report.

Business Objects Technical Support is aware of and working on this issue with us. Why post here? I am curious if anyone else has seen this issue and if so, how you have addressed it. Also, I have immense respect for the expertise shared on this forum (with the noted exception of those whose knees jerk towards some comment regarding documentation or Search) and would appreciate any troubleshooting guidance provided.

…and I also wanted to get on my soapbox for a minute about the ‘Search’ and ‘Read The Documentation’ QuickDraws. I’m not attempting to be braggadocious (although I may accomplish it nonetheless!), but I’m an MCSD with decades of programming experience; I’ve been building data islands, marts, and warehouses for 10 years; I’m published; and I wrote and marketed one of the first web-based Manufacturing Execution Systems 9 years ago… and I had some of the exact same questions posted by other ‘newbies’ who were discouragingly responded to with “Why don’t you read the documentation?” or “Try Search first!” In the interests of keeping the forum germane, why not provide a little more help along with your jab? Just my two cents - Mr/Ms/Mrs. Moderator, edit at will.

Thanks In Advance,
Andy

aleonard :us: (BOB member since 2003-10-20)

2

  1. Are you talking about query runtime of the WebI server or computation time (which includes time for calculating variables/formulas/filters, etc.)?

  2. Did you open a Business Objects Full Client session physically on the WebI server and ran your query from there? Any difference in performance then? How about ZABO (aka 3-tier) performance?

  3. Did you check and compare the database middleware configuration on the WebI server against the database middleware configuration on your local PC?

  4. Compare latency of the connection from the WebI server to reporting database versus latency of connection from your PC to database (including checks for faulty router, switch, network interface cards, etc).

Comment:
I think it is good etiquette to put in some research work first (reading the documentation, doing a search on BOB, etc.) before one asks a question. Keep in mind that this forum is run free of charge by volunteers.

Andreas :de: (BOB member since 2002-06-20)

3

Great post! The suggestions that you have been given are good ones.

To comment on the editorial part of your post. I agree completely with you thoughts. We have lately seen a rise in a the “Search” posts. Search posts with a suggestion of what to search for are certainly more helpful, but if someone actually has a brief answer to the question, I encourage people to put it in ALONG with the “Search” suggestion.

Thank you for your comments. This is how we make the board better.

Steve Krandel :us: (BOB member since 2002-06-25)

4

Hi Andreas and Steve,

Steve, thank you - and thanks for not banning me from the forum!

Andreas, at the risk of being blunt, what difference does it make? I can think of more than 400,000 reasons why there should only be a negligible difference between Webi and Full Client performance.

If I’m the only user and running both Webi and Full Client local to the Webi web server, all else is equal: same database, same network, same OS, same router, same NIC, same protocol, same service pack, same monitor, same keyboard, same mouse, same electrons. Webi is the problem. It should be fixed… and performance tested before release (this time).

Andy

Comment - I think it’s good etiquette to offer some help to people taking the time and effort to seek out this forum - most likely in their darkest hours of frustration and utter desparation with a product that purports to be a production release. You responded to my post within a couple hours of it going up. The response is appreciated. Telling me to spend weeks reading the 10G or so of product documentation would not be appreciated. Again, just my two cents…

aleonard :us: (BOB member since 2003-10-20)

5

To clarify:
You did run Business Objects Full Client Reporter from the WebI server (and not from a local workstation)? And if you do that performance regarding query runtime is OK? But if you refresh the BusObjects document via the WebI interface you encounter performance problems?

I am not to familliar with v6, but does it not utilize separate *.key files in different folders depending if you are using WebI or a Full Client module on the WebI server?

I am wondering if Business Objects 6.1b would help…

Andreas :de: (BOB member since 2002-06-20)

6

Hi Andreas,

Thanks for the response.

Yes. I ran Business Objects Full Client Reporter version 6.1a from the Webi server.

Yes. If I run Full Client from the Webi server I get good performance. By “good” I mean performance that matches Query Analyzer and custom applications written using ADO.

No. If I construct the identical query in the Webi Java Report Panel (executing on the Webi web server), performance is 6 - 15 times slower.

This is version 6.1a. We are using the same bomain.key file for both Webi and Full Client applications on the Webi web server.

It may, but we went through the pain of upgrading from 6.0.0 to 6.1a during our development cycle and are loathe to repeat the process.

Thanks,
Andy

aleonard :us: (BOB member since 2003-10-20)

7

A quick question… (or three)

I am assuming that all of your WEBI reports are being displayed as HTML, would that be a fair assumption?

  1. What happens when you change the default display behavior to something else?

  2. In addition, if you have access to ZABO, does creation of the same report in ZABO have the same performance degradation?

My thinking is that the performance issue is based on the fact that the entire contents of your report must be brought down before BO can even attempt to format it as HTML tables… and then it has to format the tables, appropriately.

Full client reports have a “structure” all ready laid out as preset arrays that just get populated when the data arrives. That may make a world of difference when you have hundreds of rows.

So… Lastly…

  1. What happens when you restrict both WebI and FullClient reports to only return about 10-20 rows… is the performance more or less equivalent?

-RM

digpen :us: (BOB member since 2002-08-15)

8

Hi RM,

Thanks for your response and troubleshooting suggestions. In response:

  1. I am not sure I tested changing the report format. I will do that and report the results here. I agree with your thinking - something that happens differently for Webi is at the heart of the issue.

  2. I am not sure to what you are referring when you ask about ZABO. I know it stands for Zero Administration Business Objects - and this exhausts my knowledge on the subject. My BO experience started with version 6.0.0 last year. We have the Full Client, Supervisor, Designer, etc. and WebIntelligence. I will gladly learn anything you feel inclined to share on the subject.

This is awesome information - it explains much about what we’ve experienced. Thank you!

  1. We see some delta when we limit the rows returned, but it’s tough to measure with that few rows (10 - 20). I may be able to gather better metrics with more rows - perhaps 1000 - 2000 or even 100 - 200 if you think that will help.

Thanks,
Andy

aleonard :us: (BOB member since 2003-10-20)

9

Business Objects Full Client (aka 2-tier):
Connects directly to the Business Objects Repository and the databases of underlying universes, requires all database middleware to be installed and configured on the local PC.

ZABO (aka 3-tier):
Connects to the WebI server (aka Business Objects Server), which on behalf of the ZABO client connects to the Business Objects Repository and the databases of underlying universes, requires that all database middleware to be installed and configured centrally on the WebI server, but not on the local PC --> benefit: centralized administration of database middleware configuration.

Andreas :de: (BOB member since 2002-06-20)

10

Hi RM,

I can see how to change the format for saved reports but not how to change the format in the Java Report Panel. Is there a way to change the it? Our users will be mostly creating ad-hoc queries in the JRP.

Thanks,
Andy

aleonard :us: (BOB member since 2003-10-20)

11

Hi Andy:

Couldn’t help myself from responding to your initial email from last fall. I have been developing a Webi-based system since mid January 2004. So far, I have seen nothing but unacceptable and poor performance from Webi reports. I have uncoved 4 actual bugs myself on the journey and countless days of frustration trying to optimize Webi performance. What a dog! I have basically the same hardware infrastructure as you only we’re using a 2 processor server. I can see from your comments moving to a 4 will not help. What’s a Webi developer to do? :reallymad:

korzel :us: (BOB member since 2004-01-20)

12

Hi Korzel,

I can sympathize!

Since my initial post, our case was escalated in Business Objects Tech Support. They recently sent someone on-site to conduct tests personally. They have not reached any conclusions yet, but here’s what they tried:

  1. Within two hours of arrival on-site, the Tech installed Tomcat on an identical IIS server that already had Webi 6.1a loaded and configured exactly as our production Webi web server and was aimed at the production database. He executed a single-dimension query we have been using as a performance metric and got the same results we’ve been seeing for months.

  2. Next, the Tech analyzed the database. I’m unsure what he found as the system was (and is) still undergoing final tweaking and tuning, but the fact that Full Client was still returning records in 2 minutes or less (compared to 30+ minutes in Webi) didn’t seem a symptom of a database issue.

  3. The next step is to engage someone else to run tests against our system. I believe arrangements are currently underway to accomplish this in the near future.

In the interim (since the last post), I started exaiming Profiler traces (hitherto requested by and delivered to BO Tech Support) myself. I noticed something different when I executed my performance metric query in Full Client and when I executed it from Webi. From Full Client, I saw a call to a stored procedure called “sp_prepexec.” Two minutes later, I had data. From Webi, I saw a call to “sp_cursorprepare,” then a call to “sp_cursorexecute,” then several calls to “sp_cursorfetch” - some of them 30 seconds or so apart and some as much as 18 minutes apart.

[Editorial ON]
:roll_eyes:
Where’s this in the documentation? And if you have 90 seconds to kill, execute a Search on these items…)

[Editorial OFF]

With 40 hours (literally) remaining before our Go Live! date, we’re getting excellent support. :shock: I believe (or rather, hope), now that BO has sent someone to personally view the issue we’ve been describing since December, things will progress quickly.

Contrary to the tone of my complaint(s), I think Webi is a good product. I like the interface a lot and admire (and simultaneously cringe) at the power it places in the hands of users. I hope they get the bugs out soon - for both our sakes!

I wish you well with your project. My recommendation is tune the database as best you can. It will mask the issue somewhat. I believe BO will address the issue(s) with Webi in upcoming releases, though I have little history to base this upon (7 months, to date).

Good Luck! :slight_smile:
Andy

aleonard :us: (BOB member since 2003-10-20)

13

Andy,
I have read the thread a few times and I dont think I see anything about your Web V6.1a stack… just about the Database tier. Can you tell us about the specific Webi Stack you are using. You mentioned that the BO tech support people did some IIS vs. Tomcat/Apache testing… But what about the rest of the equipment?

Are you using a Cluster… Cluster Manager and multiple nodes? Is your proposed usage 100 Concurrent users? Or 100 Distinct users throughout the business day? Are they all in one location? Different states or time zones? If you are just using one Cluster Manager to serve up 100 Concurrent / Distinct users is this stack also on Windows? Have you thought about Unix? Is your webi Stack if in a a cluster all on one subnet with a big fat pipe connecting the clusters?

Have you monitored the Memory usage of the various processes that Webi spawns per concurrent user? wiqt.exe, wireportserver.exe in windows? et al… or in unix …bolight etc…

There are some big differences as to how BO works in 2-tier (BO Full Client) and 3-tier (Webi) mode… So some of the above questions would be the first thing that I would ask if I was on site and doing some CSI work…

Oh yeah in 3-tier mode hows does yor network handle the traffic… tcp/ip or is it netbouy(that micro$oft protocol)? I’m at a client with Netware, ip/tcp, old and new technology and we had just one subnet for the entire company… no routers… web traffic WAS real bad… packets bouncing all over… fixed with some cisco routers and subnets. Then we had some network card problems with only using half-duplix… more slowness with 3 tier… moved the cards to full duplix and WHAM big performance gains…

So just some questions and ideas…

David Smith :us: (BOB member since 2002-07-10)

14

—*** NEW INFORMATION - UPDATE ***—

Hi All,

I really appreciate all the WebIntelligence configuration suggestions offered above. Thanks to all who pondered. Special thanks to all who responded.

Update: Our issue appears to have been RESOLVED!

The solution was a patch to the 6.1b International version supplied by Business Objects.

We’re still in hold-our-breath-with-fingers-crossed mode, but the results thus far are promising.

Our best indicator has been a Webi report querying a single dimension table in our data warehouse. This query forces a table scan of the table, and the table contains 5.5 million rows. (Our data warehouse now contains 1.6 TB of data and runs on SQL Server 2000.)
This WebI report was executing in 15 - 45 minutes prior to the upgrade and patch. The current runtime is between one and two minutes. :smiley:

Again, we’ve only had the patch installed in Production for a few days. Before that, we tested both 6.1b - and then the patch - for a week.

We’ve seen one or two quirks that seem to be related to the 6.1b full client, but we have yet to isolate them further. And because all our 50 current users hit Webi, we’re not very concerned about full client issues.

Thanks,
Andy

PS - Happy Birthday (a little early), BOB! :smiley:

*** NEW STUFF ***

The patch we received from Business Objects continues to perform well as of this update (12 Aug 2004). The patch number is 286 and was written for the International version (only - we asked about the English version) of WebIntelligence 6.1b for IIS and ASP on Windows.

One unexpected (positive!) side-effect is a performance boost in our data warehouse replication processes. It makes sense: the unpatched Webi v6 would bring back pieces of data in “a million little chunks.” The new version brings it all back at once.
An analysis revealed we have reclaimed the time previously lost to hard drive seek times. Between reads for the “million little chunks,” the SQL Server engine would pick up the heads, find the next place to write transactionally replicated data, and begin writing - only to be interrupted again by another request for 10 more rows to read. :frowning: The short version is: selects and updates, inserts, and deletes sped up - markedly.

Business Objects Tech Support came through for us on this one. :smiley:

Andy

aleonard :us: (BOB member since 2003-10-20)

15

Hi Andy,

Could you please provide some more information on the patch/change applied to your WebI configuration.

We have an identical situation in our environment, on 6.1b.

TIA,
Maeaia

Maeaia (BOB member since 2003-08-18)

16

Hi Maeaia,

The patch is called HotFix 286. From what I’ve observed, it changes the way Webi accesses SQL Server data from a “fetch and assemble pieces” method to a “grab all the data in one pass” method.

So far it looks good, but we’ve been in Production less than one week.

Hope this helps,
Andy

aleonard :us: (BOB member since 2003-10-20)

17

Anather thing that might slow down WebI is CORBA. To produce a report, data is pulled from DB, HTML report is generated, and the HTML code is made available to JSP/ASP through CORBA calls.

Request :
Browser–>Web Server --> Tomcat–>JSP/Servlet----CORBA—>WebI

Response :

WebI–CORBA–>JSP–>Web Server–>Browser

In my experience Marshalling / Unmarshalling large pieces of Data (Method parametrers or objects) with CORBA is a big performance hog.

This CORBA step is additional burden on WebI (As compared to Full Client).

Any thoughts on this?

shamit (BOB member since 2004-07-01)

18

I was looking for others’ experiences here, because we think we learned a LOT about Webi 6.1x performance in the last 3 months… We had big (rollback big) performance issues when we tried v6 in a large, clustered environment - Windows, Apache-Tomcat.

What we eventually learned was not what we tried first - we assumed our clustering was an issue, so we flattend our clusters to 2 boxes - manager, node.

Did NOT help.

We have since learned that the only workable configuration was a single box running everything, multiplied by our whole cluster - this case, 11 standalone webi boxes hanging off a CISCO.

The reason is not what we thought, tho - it’s not Webi, it’s Tomcat / Apache 1x/ JDK 1.3.1. These versions turned out to be single-threaded, and our 4-processor web boxes couldn’t handle more than 25 users at a time. We tested this using an install set from Webi 6.5 - Apache 2, Tomcat, and JDK 1.4.2. A substantial increase in performance.

We’re regrouping now, looking at running with Webi 6.51, as it becomes available - didn’t want to run in a technically unsupported environment.

Just wanted to pass this on…

Good luck,
Brent

bdouglas :switzerland: (BOB member since 2002-08-29)

19

Can you elaborate a bit? Apache/Tomcat should not be single threaded IMHO… :confused:

shamit (BOB member since 2004-07-01)

20

Apache 2 and JDK 1.4.2 seem to be the key here - my tests showed that until I made this switch, all the processing was stuck on one of the 4 CPUs. Afterwards, the work started spreading across all 4 and the throughput went up immensely.

Maybe I’m saying this wrong - I have heard it referred to as “Green” threads… Our web guys, BO used the word single-threaded, and I’m reading that in terms of the JDK/Tomcat.

The final test we did to prove (to us) what was happening was to set up Apache / Tomcat on a dead node and load the node up with 50 users. We ran the cluster manager up to 100% cpu utilization, our test took about 10 minutes - the webserver only was hit at about 30%. 1 cpu at 100, some background noise.

Upgraded the webserver to Apache 2, etc - reran test. It completed in just over 6 minutes. The Cluster manager was still being hit about 100%, but the webserver was working much more - percentage was not much higher, 35% maybe, but it’s throughput was better.

I’m still contemplating that one, but it feels like it was no longer the bottleneck we were seeing before. Kapeesh?

bdouglas :switzerland: (BOB member since 2002-08-29)