Lately, I've been doing whatever I can to rewrite DataGrids as Repeaters, and I must say that the performance increase has been noticable.  I haven't tried to measure and quantitatively document the increase, but suffice to say that when I redid my first DataGrid and reloaded the page once or twice to compile it and cache what the page wanted to cache, I thought “wow, that's fast compared to how it used to be,” and that's what matters to my users.  The first part of this process was to write the stored procedure that would do the paging.  Let me preface this by saying that I'm SURE that there's a better way to do this, and I recently read a blog entry with an idea that I hadn't thought of that I might try later.  I'm totally self-taught with T-SQL, as I've typically worked with other developers who were not DBAs and could write basic INSERT, UPDATE, and DELETE statements but complex T-SQL was not what they did ... so I wound up being the de facto “T-SQL guy” most of the time.  I should also say that this code is used in the messageboards of my site and that the column names, proc name, and table names have all been changed and more comments have been added.  So, here's my stored proc, and we'll break it down afterwards:

CREATE PROCEDURE spForums_Replies_List @topic_id int, @page_id int, @page_size int
AS
declare @strSQL varchar(7900)

declare @TotalCount int

select @TotalCount = count(*) from tbForum_Replies where topic_id = @topic_id

if @page_id = 0   -- first page is a simple case, so keep it that way
 begin
  select @strSQL = 'select top ' + cast(@page_size as varchar(10)) + ' reply_id, message_text, added_date, subject_line, creator_name, forum_id, topic_id from tbForum_Reply where topic_id = ' + cast(@topic_id as varchar(10)) + ' order by added_date'
 end
else
 begin
  declare @PageCount int
  
  --get @PageCount, which makes sure that we select the right number of records on the last page of the thread
  IF((@page_id + 1) * @page_size > @TotalCount)
   BEGIN
    SELECT @PageCount = @TotalCount % @page_size
   END
  ELSE
   BEGIN
    SELECT @PageCount = @page_size
   END
  

  select @strSQL = 'select top ' + cast(@PageCount as varchar(10)) + ' * from ( select top ' + cast(@PageCount as varchar(10)) + ' * from ('
  select @strSQL = @strSQL + 'select top ' + cast((@page_size * (@page_id+1)) as varchar(10)) + ' reply_id, message_text, added_date, subject_line, creator_name, forum_id, topic_id '
  select @strSQL = @strSQL + 'from tbForum_Reply where topic_id = ' + cast(@topic_id as varchar(10)) + ' order by added_date ) as x order by added_date desc ) as y order by added_date'
 end

select @TotalCount as thread_size

exec (@strSQL)

So that's it.  The first thing that we do is to calculate the number of items there are, total, in the thread.  We need this to calculate how many pages we want to display in the footer of the messageboard (which I'll soon provide code for).  Then, if there is only going to be one page, then we just build a simple SQL statement to handle that case and that's pretty much the end.  If there are more pages then we need to next decide if we're on the last page and if we are then we need to compute how many replies should be returned.  Now we know how many records to get, so next comes the nested SELECTs.  The innermost select gets all the replies from the first in the thread to the last one we're going to select, in order.  The middle select picks out just the replies from the page that we want, but it can only do that by selecting the top ones from the list that we just SELECTed in reverse.  Then the outermost select has the right result set, and it just orders it correctly.  If I wanted this result set in reverse order, I wouldn't need that outermost SELECT.

As you can see, I build up the SQL in a string and then run it at the end.  I've never been able to get the SELECT TOP statement to work with a variable, so I have always had to build the SQL up and then execute it later.  I don't like having to do this, but sometimes it's the only way.  I typically also have to do this when the ORDER BY clause is determined outside of the stored proc and passed in.  I've run this proc in Query Analyzer many times (and the database is remote from me so it all goes across the internet) and it's never taken more than 1 second, so I'm happy with it.

Also, notice the naming convention that I use for tables ... I use the “component” which in this case is “Forum” then the “object” which in this case is “Reply”.  Similiarly for the name of the stored procedure, I use the component, then the object, then maybe another noun if I need it and then the verb.  I find this easier to deal with than having the verb first because then all the related stored procedures are grouped together.  It never made much sense to me to have all the SELECT stored procedures together then all the DELETE ones together ... but that's just me.

Hopefully this has been some food for thought.  I'm sure that I'll get at least one or two “that's so inefficient” or “that's the WRONG WAY to do ...” comments and that's fine.  I will try to take them as constructive criticism and get better.