Jeff and .NET

The .NET musings of Jeff Putz

Sponsors

News

My Sites

Archives

December 2006 - Posts

Paging in SQL 2005 like a ninja

I'll be honest, I really haven't looked much at SQL Server 2005 because I really didn't have any incentive to. Sure, I've been using the Express version in production and it's super sweet, but honestly I haven't bothered looking any deeper into it. I did get the programming book Microsoft put out, and even read the new stuff, but didn't think much about it.

I realized in rewriting my forum app that the bane of my existence, in terms of performance, has always been paging results. There has never been a great way to do it in the 2000 days, but you could do OK with some nested queries and cursors and things. That's how I've been doing it for a long time, as most articles online led me in that direction. The down side is that you still can't avoid scanning the entire table, at least for the primary keys.

But porting over some data to my test forum, I saw some render times I wasn't really happy with. My goal has always been to keep render times below one-tenth of a second if at all possible, and generally I can get there, until you get into these paging situations where there are tens of thousands of records. I was seeing times as high as a third of a second, and I wasn't happy about it.

Then by chance, I came across the concept of common table expressions (CTE's), and the function ROW_NUMBER(). Combining the best of both worlds, I finally came up with something that was performing extremely well. There are a whole bunch of different articles that got me there, and Googling for certain words will easily get you there. Here's the sproc that gets pages of topics:

CREATE PROCEDURE pf_PagedTopics(
@StartRow int,
@PageSize int,
@ForumID int
)

 AS

DECLARE @Counter int
SET @Counter = (@StartRow + @PageSize)

SET ROWCOUNT @Counter;

WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY IsPinned DESC, LastPostTime DESC)
AS Row, pf_Topic.TopicID, ...
FROM pf_Topic WHERE ForumID = @ForumID AND IsDeleted = 0)

SELECT TopicID, ...
FROM Entries
WHERE Row between
@StartRow and @StartRow + @PageSize

SET ROWCOUNT 0

GO

Here's a breakdown of what goes on. The ROWCOUNT is set because presumably even in the CTE it makes sense to stop reading when you have enough rows. I'm stopping with last record I should get. The "Entries" CTE is pretty straight forward, the interesting part being that the ORDER BY is at the top, and we're calling this first column in the CTE "Row." After that, you just select the columns in "Entries" without the "Row" column.

While I'm getting those sub-tenth of a second page renders I've always dreamed about, the number of reads in the trace is still a little on the high side, at 400+. If I understood more about how this worked, I'd be thrilled, but I might have to revisit this some other day. For now, here's hoping fast disks are enough!
 

DropDownList with optgroup

Many moons ago, I asked if anyone else thought it was strange that there was no way to add optgroups to the DropDownList. I've seen a number of solutions, including those involving control adapters, but I wanted to make something a little more simple, if hackish, for the old toolbox. Believe it or not, this tag has been around since HTML 4, and you've probably seen it before as non-selectable headings in a drop down or list box.

The first issue is that, not surprisingly, ListItem is sealed and can't be messed with. I say not surprisingly because it's used in more than one type of control, so what might appear in one might not make sense in another. RadioButtonLists obviously would have no use for this, but DropDownList and ListBox do.

Anyway,  it seemed to me that the easiest thing to do was to find the method that did the ListItem rendering, hack it, and use it as the replacement. DropDownList has just such a method called RenderContents, so I Reflector'd it and hacked it and came up with this:

protected override void RenderContents(System.Web.UI.HtmlTextWriter writer)
{
    if (this.Items.Count > 0)
    {
        bool selected = false;
        bool optGroupStarted = false;
        for (int i = 0; i < this.Items.Count; i++)
        {
            ListItem item = this.Items[i];
            if (item.Enabled)
            {
                if (item.Attributes["optgroup"] != null)
                {
                    if (optGroupStarted)
                        writer.WriteEndTag("optgroup");
                    writer.WriteBeginTag("optgroup");
                    writer.WriteAttribute("label", item.Text);
                    writer.Write('>');
                    writer.WriteLine();
                    optGroupStarted = true;
                }
                else
                {
                    writer.WriteBeginTag("option");
                    if (item.Selected)
                    {
                        if (selected)
                        {
                            this.VerifyMultiSelect();
                        }
                        selected = true;
                        writer.WriteAttribute("selected", "selected");
                    }
                    writer.WriteAttribute("value", item.Value, true);
                    if (item.Attributes.Count > 0)
                    {
                        item.Attributes.Render(writer);
                    }
                    if (this.Page != null)
                    {
                        this.Page.ClientScript.RegisterForEventValidation(this.UniqueID, item.Value);
                    }
                    writer.Write('>');
                    HttpUtility.HtmlEncode(item.Text, writer);
                    writer.WriteEndTag("option");
                    writer.WriteLine();
                }
            }
        }
        if (optGroupStarted)
            writer.WriteEndTag("optgroup");

    }
}

I said it was hackish because of the way you create the groups. You'll need to add ListItems to the collection, and add an attribute to them to let this rendering piece know you mean business. Something like this:

ListItem item = new ListItem("some group name", String.Empty);
item.Attributes["optgroup"] = "optgroup";
myDropDown.Items.Add(item);

Works like a champ, and has the desired output. You don't need to worry about someone selecting a group since you can't. There are still some viewstate and postback issues I haven't worked out, but nothing that an experienced control developer (i.e., someone other than me) hasn't seen before.

 

Posted: Dec 27 2006, 01:23 PM by Jeff | with 14 comment(s)
Filed under:
No love installing Visual Studio SP1

I decided I'd give the SP1 install a shot Friday night, before leaving for Christmas activities. Boy was that a waste of time. First try, there wasn't enough space on the drive, which happens to be a Parallels virtual drive on my Mac. So I fattened it up with about five gigs to spare, and no love. Got the ever popular and incredibly useful error 2908. You know, the 2908! Duh! It was completely hosed after that, and I couldn't even start VS.

I was pretty annoyed. It took about an hour just to get that far. Seriously, how does this stuff ever get out of QA like this? Thank God this wasn't a consumer product, or everyone would have another excuse to start slamming Microsoft again.

Fortunately, being a Mac user with Parallels, I backed up the machine before starting and just started over. Same problem on second attempt, but I tried. The stuff I could Google was all from the initial launch of the product, and a lot of explanations that offered no real solutions.

So there are two things that suck about the installation experience (aside from the fact it simply doesn't work). The first is that you need lots of free disk space, and the installer doesn't bother to check it before doing its thing. Someone should be fired for that. The second problem is the absolutely useless error code. I've yet to encounter anything on the Mac that doesn't tell me why something is wrong when it breaks, but you get nonsense like this all over Windows.

This was very much the moment that I realized just how screwed up Windows really is. I've read some good explanations about why the SP takes so long to install, but the bigger picture issue is that most problems are related to the Windows architecture that Microsoft can't let go of. .NET itself is fabulous, but VS still has too many hooks to the OS, not the least of which is the damn registry, apparently.

I don't know what I'm going to do at this point. I don't really have time to keep fighting the OS. My MSDN incidents expired. I guess I'm going to go on un-SP'd. This does not inspire a lot of confidence at work either, where we were hoping to get some of the reported performance gains.

Chatting with John Battelle of Federated Media

I talked for a little while with John Battelle today, the founder of Federated Media (and co-founder of Wired, Industry Standard, author). CoasterBuzz was recently accepted into his "federation" for ad representation, and I have to say that he really, really gets it.

We talked about quite a few things, but what really impressed me is that he's taken what he knows about the magazine business and applied it in a logical way to the online world. That's something most every old-school media company (including Penton Media, my former employer) has largely failed to do. The most important distinction he made is that the advertisers themselves, and their agencies, haven't changed all that much. The truth is, they're not as interested in reaching a demographic or niche as much as the online ad industry has led us to believe. They still want to align themselves with brands and see an attractive package, not just that they reach x number of 18-34 males who like golf (because where do you find people like that? ;)). That's why they invited my site, doing just a million pages a month, along side of giants like Digg. Anchors like Digg's entertainment section are the cake, and CoasterBuzz is like the icing, the decoration that makes it even more attractive.

He said the typical ad network now deals in a commodity market. It's cheap inventory that's hard to sell because there's little indication about what the quality of the content is. I would go as far as saying that's precisely the reason that Google ads work: Your ad dollars go where the quality content is that people care about. The FM approach is more labor intensive than Google's, but it obviously does work.

He got called away while were talking, and said he'd like to talk more later. I'm very interested to hear some of the other things he has to say.

No, really, I'm making progress on the forum app!

I just got back from a week long vacation in Orlando. Had a good time, though I'm a little exhausted. Even for a theme park dork like me, six days is over-doing it.

Before I left, I felt like I finally hit a fair number of milestones for the next version of POP Forums. It seems almost absurd to talk about it anymore since I've been doing so for, uh, three years now. I essentially started over in October, and things really started to flow pretty quickly at that point. Back in August I also thought a lot about what I would do with it outside of my own projects, and I think I've settled on "License it inexpensively, with source code, on the honor system."

There are some basic things to work out, and the next major milestone is to implement a search mechanism, which I prototyped more than a year ago. My goal is to get that done before the end of the month, and then shift gears and blitz through a re-development of CoasterBuzz with the forum in whatever state it's in. At least that way I'll have some real-world usage to test with. If I'm going to actually ask for money this time around, I want to be reasonably sure that it's not going to choke.

I got a lot more motivated when Federated Media picked up CB as a new author. It's too early to see if they'll deliver quality advertising (so I can get rid of that pop-up crap), but the fact that John Battelle will take a phone call from me is encouraging. Being in the company of Digg and BoingBoing certainly doesn't hurt!

Still no Xbox Live support in XNA
I see while I was on vacation that the XNA Studio Express was released, but still doesn't include any Xbox Live support. Considering that they seem to be fostering a "farm system" of development shops, where product would be distributed by way of Live anyway, I can't for the life of me figure out why they aren't figuring out this support. Who is really that interested in developing games you can't play with other people?
More Posts