In my previous post, I proposed a way of trying to figger out where performance problems are.
Now I discovered a possible problem in our existing classic ASP website. On several places, the server tag:
<%Response.Buffer = false%>
Now what exactly happens when this is set on top of the page?
When the Response.Buffer is set to false, every Response.Write is sent immediately to the client. If response buffering is enabled, then these writes are grouped and sent to the client in one larger, more efficient transaction. From IIS 5 this is by default enabled on the server (they did that for a reason).
In our case there are loops and sometimes these loops go over 100 rows. In the loop there are more then 50 Response.Write tags. So image when the Response.Buffer is set to false. It will result in 100 * 50 connections from server to client. that's 5000 connections. Now when the load on the server gets higher and 100 users are opening the same page on the same time, it's 500000 connections. This will blow up the server and breaks performance.
|5000 Response.Write||Response.Buffer = false||Response.Buffer = true|
|1 user||5.000 connections||1 connection|
|100 users||500.000 connections||100 connections|
If your page is very large, and the processing very long you may want to periodically flush the response buffer to the client. This will ensure that the client steadily receives data and does not time out. If you are building large tables in a loop you may want to add Response.Flush every 100 rows or more.
It can also happen that the Buffer Limit is reached and the response is cancelled. By default the Response Limit is set to 4Mb. You could increase the to max 20Mb. But even better, you could improve the page itself by using Response.Flush every n rows or simply check if the page could be rewritten. You do not want to sent page over 10Mb to clients.
Responsiveness can be achieved by other ways, like showing a loading message, Response.Flush it and hide it in the end of the response. Or flush loops over n number of rows.
Observation : MOSS 2007 list performance drop
Recently, we discovered that the performance of the MOSS 2007 implemenation of our client dropped significantly for a specific list. The list contains some 1200 records. Most of them contain attachments. The loadtime for the default view would extend to over 20 seconds. Switching views made things even worse!
First, the server performance was consulted while loading the list. No special or irregular behaviour of the processor or memory. Services were checked to find out if a service like for example the search was performing a heavy index. No special observations. Then we placed indexes on the key columns. This made no visible difference in performance.
How is it possible that only that list is slow in loading, while other list and doclibs in that same site have a normal loadtime? It had to be something list-specific. And what is specific to a list? It's views...
Interpretation : Optimizing the GROUP BY
After scrutinizing the views of that list, we discovered that the there were massive groups in certain views. The "group by" statement resulted in records being categorized in over 100 groups. Setting a limit to the number of groups improved performance drastically!
When showing 100 groups on a page, the loadtime took 15-20 or even more seconds. When tuning down the number of groups to 50, the page loaded under 10 seconds.
We tested further and discovered that the number of items under a group hardly alter the page loading time when the groups are collapsed on start. A page with 1 group and 500 items under it will load many times faster than a page with 500 groups and just 1 item under each of those groups.
The key is be wise with GROUP BY statements.
Conclusion : Relation between GROUP BY statement and load time
- Group your items in less then 50 categories for optimal performance
- Show as few groups as possible on a page (max 50 groups/page)
- Place column indexes on the columns you group by
Applying these principles solved our issue on the spot...
We hope this information will help you further optimizing your WSS/MOSS 2007 environment.