Sign in to follow this  
  • entries
    375
  • comments
    1136
  • views
    297211

Status

Sign in to follow this  
superpig

79 views

4E5 prize allocation is proceeding, albeit slowly. I'm waiting for a few more contact details for people before I can send out all the FastCap Pro licenses - I might give up on those who haven't sent me their details and just pass those I have got along to get them issued. The rest of the pool... about 20 prizes have been allocated so far, with another 30 still to go. Drilian's having a little trouble deciding what else to pick... in retrospect the prize ordering that I selected wasn't brilliant as it's given him 20 slots and there are only 16 unique prize types in the pool, so he's guaranteed 4 duplicates...

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.
Sign in to follow this  


5 Comments


Recommended Comments

Just to chime in here ... dynamic sql is only a problem if you're embedding the where clause values directly into the string. SQL Server will cache the execution plan for dynamic sql queries -- as long as the SQL string remains the same. So if you use parameterized queries, you get the performance benefits of sprocs, and the dynami ... sism of dynamic sql :-)

In addition to the multiple recordset capabilities, I'd suggest doing some data caching where applicable.

Share this comment


Link to comment
Quote:
Original post by joelmartinez
Just to chime in here ... dynamic sql is only a problem if you're embedding the where clause values directly into the string. SQL Server will cache the execution plan for dynamic sql queries -- as long as the SQL string remains the same. So if you use parameterized queries, you get the performance benefits of sprocs, and the dynami ... sism of dynamic sql :-)


That's exactly what it's doing and exactly why it performs poorly.

Quote:
In addition to the multiple recordset capabilities, I'd suggest doing some data caching where applicable.


We do cache data in many places, the news page, etc. It's generally quite efficient.

Share this comment


Link to comment
Quote:
Original post by superpig
Thus they're quite a bit faster to execute.

Not to mention the increased maintainability, I suspect. :)

Have you tried running the profiler to find MSSQL's bottlenecks? Could more or better hardware potentially solve some of the problems (even if it was something simple like putting the table indexes on faster disks). Or is it mostly due to inefficient ASP scripting?

Just curious.

Share this comment


Link to comment
Quote:
Original post by WanMaster
Have you tried running the profiler to find MSSQL's bottlenecks?
Yep - but the problem is, we make lots and lots of little queries, and the profiler can't group the queries by HTTP request, so we can't easily see which pages should be addressed.

Quote:
Could more or better hardware potentially solve some of the problems (even if it was something simple like putting the table indexes on faster disks).
There is an issue with the configuration at the moment - the transaction logs are huge (like 115GB or something) and on the same drive as the data files, so obviously that needs addressing, and we'll be buying some new hardware to sort that out. I'm hoping that one will be a significant performance gain.

Quote:
Or is it mostly due to inefficient ASP scripting?
Mostly, yeah. There's a lot of stuff done in ASP (which is slow) that should be done using things like rules and triggers on the SQL Server side instead (which is faster).

Share this comment


Link to comment
Quote:
Original post by superpig
There is an issue with the configuration at the moment - the transaction logs are huge (like 115GB or something) and on the same drive as the data files, so obviously that needs addressing, and we'll be buying some new hardware to sort that out. I'm hoping that one will be a significant performance gain.

You could simply truncate the log. Using the simple recovery model together with the auto-shrink option turned on may keep its size reasonable in the future.

Im my experience, defragmenting and optionally reindexing of tables can help as well. But I'm not sure whether it is feasible in this case, without taking the entire database offline (primarily due to its size).

Share this comment


Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now