I love the forum, but the SQL implementation definitely needs work.
I have run SQL Profiler traces on the following pages, and the problem is mainly server side cursors. Each cursor fetch is a round trip call to the server, and many of the pages have hundreds of database calls per page.
We have found when the number of users on an active forum (>150,000 threads per quarter) exceeds a few dozen users, the ability of SQL Server to keep up with the cursor fetch demand, even on a co-located server is severely challenged.
default.asp, forum_topics.asp, active_topics.asp are prime examples.
Another problem is revealed when you run the following command in Query Analyzer
EXEC sp_MSforeachtable @command1="sp_help '?'"
Notice the twins? They do not improve read performance, but definitely hurt updatable operations.
The table structure is also very poor, with fixed data types intermixed with variable length types. For example, all bit data types should be contiguous. Why? SQL packs contiguous bits into a single byte of storage, saving space and improving performance by reducing logical I/Os, ie page density increases thereby reducing page reads. I am not referring to tblPermissions as the bits are contiguous there.
Another problem is that every time a recordset is to be opened, a check is performed on whether Access or SQL server is to be called. The entire data sets for the web sites for each provider should have been isolated in function calls in individual include files. This would allow the code base to play to each database's strength.
Client side pagination using ADO is one of the worst possible performers (see
http://www.aspfaq.com/show.asp?id=2120). In the current implementation, all the records are thrown over the wall from SQL Server to the ADO layer, where the paging is done. On large forums, most of the pages will never, ever be visited by an end user. It is a huge waste of CPU, bandwidth, and memory to fetch more records than will actually be viewed by the user.
There is more, but it is my bedtime. All of the deficiencies are relatively easy to address, and it makes a huge difference in throughput when they are corrected.
Topic Options
Post Options
Thanks(0)
