The joy of RowNumber #announcement fstdt.com blog
Poking at the database and having a bit of fun if I'm honest. Over the years I've refined the paging code pretty well, but it still involved more work than it absolutely needed too. To make a page it had to go in, figure out how many Comments there were, what order they belonged in, which page of them we wanted and then pull the data out for the page. Still, plenty fast enough and anything more in depth would require a considerable bit of house cleaning to keep it up, because I'd have to make a note of which page the quote belonged on and keep that notation updated or we'd wind up with odd numbers of comments on pages and bad quote counts.
I figured I'd test and make sure it's worth doing, turns out having the page numbers pre-generated is about five times faster. Even when I'm actually saying I want Quote X's Comments 1-20 rather than specifying a page. So by taking the time to rebuild the site so that it generates and updates these numbers not only should it run faster, I should be able to let people select how many comments they pull per page with relatively little system impact. Within reason.
Now I just need to decide if it's worth blowing an extra half a gig of space to have the absurdly fast index on the table that contains all the relevant data for quote comment pages, or if I do the one that is just really fast and is about 3 megs. But the little one would stand up to user comment modifications better and the index generates in about 5 seconds rather than the two minutes it takes to generate the larger one. Either way, they're both faster and easier to understand than what's running now. They just take more work ahead of time.
Specific details for this, on the current site I'm using RowNumber to generate the row numbers that are used to create pages now. What I intened on doing instead is actually tracking the row number as part of the comment, and for history's sake I'll be using RowNumber(Partion By QuoteID OrderBy ID) which generates exactly what I need, a number of 1-total comment count on a per quote basis and storing those details while all new ones would be generated along with the comment after release. Then when it comes to actually pulling the page all I need to do is say which Quote and what range of Row Numbers I want for a page, which with the right index means it works fast as hell. If anyone's interested I'll go into more detail on the indexes later.