Keep checking back for more. We've got a lot of great stuff lined up! 🙂
Category Archives: 10
ROW_NUMBER() OVER Not Fast Enough With Large Result Set
So I'm working on improving the performance of the next version of Channel 9. For those of you not familiar, our team created a new platform for our department to build community sites (blogs, forums, videos, tagging, etc). You can see an example of the platform running on 10. Currently, there's not a whole lot of data in the 10 database so we've been able to get away with murder from a performance standpoint. Now that I've imported all the data from Community Server (Channel 9) to 10 (EvNet Platform), we've been finding that 250,000 rows doesn't perform very well with our current code.
The architecture of our platform is pretty simple. There's an Entry table that houses Entries, Threads & Comments and the relationships between them. When you're viewing a blog, you're looking at this table. When you're looking at a forum, you're looking at this table. We differentiate everything in our system by tags. There's one for each forum, each blog, and content tags to describe what's in each entry.
Currently, to do paging we've been using the ROW_NUMBER OVER function. It's a new feature of SQL Server 2005 and is very simple and easy to use. Unfortunately, it doesn't work very well with a lot of rows (250,000 for example). I did some searching and came across this gem of an article. It uses an interesting trick to use SET ROWCOUNT to get the first record to start with in a paged result set, then you just run the query again and set the row count again to the number you want where the values are greater than the first row from the starting point of the paged result set and man is it snappy. Do check it out if you're having troubles with performance of ROW_NUMBER() OVER.
Oh yah, I forgot to mention how much of an improvement this change made. Before the change queries were taking about 8 seconds on average. After the change, the queries now take less than 1 second. Depending on if SQL has decided to cache the results or not, it's pretty much instantaneous.
Soapbox Video and Invites on 10
Laura posted a video all about Soapbox on 10. If you're not familiar Soapbox…think…YouTube. 😉 Want to know how it's different than YouTube? Watch the video, then get an invite (which have been hard to come by as of late), courtesy of 10! 🙂
Our Team on Channel 9 Again
We recently launched a new version of 10. There were a few cosmetic changes, lots of bug fixes and a HUGE update to the guts of it. It is now much more generic on the inside. At its simplest, our platform is now just a bunch of Entries and Tags and associations between the two. When viewing a forum, you're just looking at tagged entries. When viewing someone's blog, you're just looking at tagged entries. The list continues…
This major re-organization/generalization of our platform lines us up to update Channel 9 soon to this new platform, enabling us to make very quick updates to all of our sites. There will be some new sites over the next year.
Anyway, we put up a video of us launching the new site on Channel 9. Check it out. It's good for a laugh and a little insight into our crazy team. 🙂
http://channel9.msdn.com/Showpost.aspx?postid=238667
Also, expect to see these videos turn into a regular thing (but in much shorter and more frequent chunks).
Channel 9 and Microsoft 10 are Hiring
Jeff spills the details…
http://www.jeffsandquist.com/WereHiringComeJoinTheChannel9And10Team.aspx
Windows Live Spaces Goes LIVE!
The new Windows Live Spaces (formerly MSN Spaces) has just gone live. They have some really neat new features. Of course we have a video on 10 about the new release. 🙂
Microsoft Photosynth from Live Labs
This thing is unbelieveable! It meshes still images together in 3D space and lets you fly around through them.
Check it out on 10 AND Channel 9 for different perspectives on the product.
Now what I really want to see is navigating images using Play Anywhere. 😉