Client performance issues

[wolf5370]

(see also thread - IP Chunks).

Hi,

we have been experiencing some pretty major performance issues using 7.2.1 on a particular project I am working on - it is a manual port from Mstr 6 (Not using the import tool, but recreated manually).

For this project there is a OS390/DB2 6.1 warehouse of about 0.75 terrabytes, clustered Win2K IServers (dual 1.4Mhz 4GB memory machines) and twin load balanced (NLBS) Web Servers (spec as IServers). There seems to be no performance problems from the client down as you'd expect. However (yes there is always a however), the client seems to be really stressed with larger reports.

If a report had, say 2000 rows by 15 columns, then the report may be returned to history in say 30 seconds, but takes 10 minutes to draw on the clients PC. At first we thought this was due to the old machines that users were using - in many cases NT4 SP6a 1.1 Mhz PII's with 64MB and only about 2GB free HDD space (for VM). During drawing we could see the memory maxing out at 100% and VM too. It seemed that the developer machines, much more modern of course, ran much faster and drew in about one minute. However (that word again), some users have pretty sexy machines - one in particular has a 2.4Mhz 512MB desktop which still takes an age to draw.

We have DHTML on. It gets really ridiculous if the report is run in outline mode.

We can not seem to see a pattern - and Gold Support are somewhat baffled.

I don't expect anyone to be able to resolve this for me as obviously there is not enough info here. What I was after is seeing if anyone else has had similar issues, and even perhaps have found a reason for it (or a cure?).

Any help would be great - especially for my stress level!

Cheers,
Wolf

 

[mstrNate]

Hey Wolf,

I haven't experienced this before, but the problem is too interesting to not add any thoughts. Do all the users who are experiencing the problem have the same browser version as the users who don't experience the problem? Have you tried increasing the registry setting for the JVM Heap Size on the web servers from 128 to 256? That might not help much, but I mention it anyway just in case it had not been suggested yet. Could help when you have more users on the system.

Good luck,
Nate

 

[nlim]

you need to isolate the problem first. Here's my suggestion.

1) using desktop, create a large report that has been giving you problems. Copy the report sql into a native OS390 SQL interface. Goal is to see if the SQL is bad, or if the DB is slow. Make sure the sql is run on the OS390 not using MSTR or desktop client.

if step 1) was fast, then you know that the SQL is good, and the DB is good. If Step 1 is slow, then you try to get the DBA to rewrite the SQL into the "best" possible. Run this new SQL, if it's fast, then the DB is good and you need to tweak the VLDB settings for the report. If it's slow, then the DB needs tuning.

2) assuming step (1) was fast, now we need to find out if the IServer is slow. Find a good desktop client, and run the same report but in 2 tier. This will bypass the Iserver. If report is fast, then iserver or web is bottleneck. Not likely due to network since network was also tested in the 2 tier client. Proceed to 4 if step 2 is fast.

3) If step 1 is fast, and step 2 is slow, then you either have a network issue or a slow client rendering. It's not the Iserver or the DB. To find out which, open Excel on the same client, and send a pass thru ODBC query to the DB with the same SQL used in the report. If your network is slow, then Excel will also be slow bringing back the results.

4) If step 1 is fast, and step 2 is fast, then you need to see if Iserver is slow. Now connect in 3 tier and run the same report. If it's fast, then IServer is not the problem, it's on the webserver.

By now, you should have isolated the performance bottleneck to DB, bad SQL, network, Iserver, bad client, or webserver.

Let us know what happens, and we can continue from there.

 

[wolf5370]

Hi guys,

MstrNate - Yep Heap Size already set - fell foul of that one several years ago on Mstr 6. All users using IE - various versions from 5 to 6 (and OS NT4 SP6a, a few Win2K users (not many) and XP) - it seems to make no difference.

Nlim -
1) Yep, done that. SQL is fast (and much more efficient than 6x as it uses Global Temp Tables). Side by side tests with 6x put 7 much in the lead with regards to SQL execution times - but not draw times (or overall times as a result) when report exceed a few thousand cells.

2) Done this too. IServer is not our bottle neck - also sniffed the network to discount that although we did see too much comunication here (see IP Chunks thread).

3) There seems to be no problem accessing the DB directly - it is fast. Infact, the 6x project is still live |(until we can sort this out!) and has no problems.

4) STill good (as long as it is in Desktop).

If we switch off DHTML the reports render on the client very fast - unfortunatly the users are not haqppy with the result quality or loss of functionality. It seems to me like it is deffinatly either the drawing itself (why?) or some communication during rendering with the web server - downloads, chunk sizes etc that is cuasing it to slow down.

Thanks Guys, hope this helps (You can see why I'm pulling my hair out!)
Wolf

 

[nlim]

So either the XML communication is slow, DHTML generation or DHTML rendering is slow...

1) in web, change the default row number for the report grid to 10, and the columns to 5. Then run the report in DHTML. In this scenario, in theory IServer is only sending back 10 rows to the webserver for DHTML generation. So if the report is fast here, then at least we have a baseline. that normal DHTML rendering is ok. Take a look at the size of the DHTML page. Do the same for HTML only. If slow try to reduce the row number to 1.

2) Increase the row numbers until there is a significant difference between the HTML and the DHTML. Call it X. Check page sizes at row size = X. As you do this, see if the DHTML performance degrades somewhat linearly with the number of rows. DHTML has lots of RMC code that has to be applied for each row of the grid...Or whether it's more of a step function. If linearly, then we know it's related to the number of rows, something about the DHTML is slowing down with each new row added.

3) If when rows are much greater than X, and HTML is still fast, then definitely the XML communications is ok. Not Iserver XML api problem, nor is it web XML result retrieval issue. My guess is that the HTML will degrade, but at much higher numbers than X.

4) Now to debug the DHTML part. The report has lots of things on it with DHTML. I would start to disable each one and see whether performance improves at row = X.
- turn off drilling.
- make drop down menus only on clicking instead of onmouseover
- disable sorting
- disable subtotaling
- if you have more than 1 attribute on the report, move one to the page-by axis and see what happens
- see if locking column headers will help.

Good luck. My gut tells me it's a browser issue with rendering dhtml...Maybe you can try it out on a IE6 browser especially with the latest MSFT browser patches.

 

[nlim]

wolf5370, how's it going?

 

[wolf5370]

Hi Nlim,

just got back from Bank Holiday weekend, so had not seen your latest - agree with your conclusion (although I think there is the possibility of IO slowing it down too - a network sniff shows lots of handshakes while retrieving the report - poll interval set to 10 seconds, handshake 8 times a second! - not sure what is happening here, but am arranging further sniff for the retrieval if a report from history - that should eliminate all but the drawing and coms involved in retrieving trhe cache).

I will look into what you suggested here and post back.
PS: We are also looking at changing NLBS (Microsoft's web Load Balancing software) for a hardware (Cisco) solution.

Wolf