I've done quite a bit of work on GDNet as well:
- There was an issue with posts made in private Gathering forums showing up in people's profiles. That's now fixed.
- The forum's core stored procedures - the one to retrieve the posts in a topic, and the one to retrieve the topics in a forum - were previously using temporary tables to do their work. I changed them to use table variables instead, which means they do less locking and should be able to run in parallel more easily.
- The GDNet+ maintenance script (the one that runs to check for expired accounts and newly paid-up ones) has been rewritten, with Michalson's help, to fix the bug where people with expired GDNet+ accounts were having their settings reset repeatedly. (This included Emmanuel Deloget's staff title).
- The user profile page (profile.asp) has been rewritten using a stored procedure that returns multiple recordsets. There's now only one query made across the entire page. See below.
- I've worked with Oli (evolutional) to make more progress on the new version of NeHe - the NeHe news script now has its own (locked-down) database user, and the old news items have been imported into the DB.
- I've started writing a project proposal / plan for V5 of the site. We all know roughly what we're doing, it's just not been written down before.
One of the big performance issues in the GDNet codebase is the extensive use of dynamic SQL - SQL statements that are built on-the-fly through string composition. The string operations themselves are nothing, but when the SQL arrives at the server, it has to parse it and then generate an execution plan for it - kinda like compiling it - and that's not so good, especially when much of the SQL we submit is fairly complex. The alternative is to use database-side objects like stored procedures, views and UDFs - these are created from an SQL statement or set of SQL statements, but because they live at the server end and don't generally change, they only need to be parsed and compiled once. Thus they're quite a bit faster to execute.
Also. In SQL Server/ADO terminology, a recordset is one set of rows/columns, one 'table' of data. It's supposed to contain some number of 'similar' records - yet often the different pieces of information that a page needs aren't similar to each other. For example, the user profile page needs four recordsets - one that contains the basic user profile information, one that contains the items for the 'Contributions' listing, and one each for the 'Recent Topics' and 'Recent Replies' tables. While we could potentially pick a column structure that is the union of all of those, and set the values to NULL for columns that don't apply to a record, it's a bad plan; making the queries and getting the results separately is really the only way to go. However, there's another nice thing you can do with SQL Server and ADO, and that's to use "Multiple Recordsets." Instead of having an SQL statement that only returns a single recordset, you combine a bunch of statements into a stored procedure and have them /all/ return recordsets. So, my little "EXEC viewUserProfile" stored procedure call returns all four recordsets that I can just iterate through. One query is set to the DB server, all the data is set back, and aside from the parse/compile step on the EXEC statement itself, everything is prebuilt and spins into use immediately.