Hi,

In Marco's most excellent book, there are instances where SQL code is sent to the database directly from a c# class. Page 397 shows such an example.
It is done this way so the SQL code can be built dynamically depending on the 'sortExpression' argument passed to this method.

However, it strikes me that you are losing out on the optimisation that the database can do by re-using the query plan which happens when Stored Procedures are used.

It struck me that I could create a Stored Procedure which holds separate SELECT queries for each of the sort orders I am likely to need. I can pass the 'sortExpression' as a Parameter, and use CASE statements on it to choose the appropriate SELECT query.


But is this overkill? Will I get any real performance benefits, and would these benefits be cancelled out by the processing required for the CASE statements?

Indeed, if you have several SELECT queries in one Stored Procedure, separated out by CASE blocks, are they each optimised with their own query plans, or is the query plan created based on the whatever SELECT query happens to be kicked off the first time the Stored Procedure is run?

Any thoughts much appreciated.

SQL 2000 started caching query plans of regular SQL, so there's no real advantage in the stored proc method.

Besides, in this particular situation you're not going to benefit much from a cached query plan, anyway.

Eric

Thanks for the reply, Eric; that's definitely worth remembering.

I have been doing some further digging on this and have found lots of interesting stuff.

Some seem to think using the new ROW_NUMBER() function in SQL Server 2005 is not the most efficient way to do paging anway...
http://weblogs.asp.net/eporter/archi...esult-Set.aspx

There are links off of this article to other ways of doing it which are, according to some, more efficient with large result sets.

There is an interesting comparison of techniques here (unfortunately not including ROW_NUMBER):
http://www.codeproject.com/aspnet/PagingLarge.asp
This also goes into more detail on the non-unique items in ORDER BY column issue.

Unfortunately though, I need to do sorting too, and also sort on the 'in-line view' query result. For example, take the following code:

SELECT PollID, AddedDate, AddedBy,
            QuestionText, IsCurrent, IsArchived,
            ArchivedDate, IsApproved, ExpireDate,
            (SELECT SUM(Votes) FROM vs_PollOptions WHERE PollID = vs_Polls.PollID) AS Votes
FROM vs_Polls
ORDER BY AddedBy DESC


In this case, I am sorting on the 'AddedBy' column. But I also need to be able to sort on the 'Votes' column. However, as this column value is dynamically generated as the result-set is built, I cannot sort on it! It looks as though to acheive this I have no choice but to use a VIEW or INNER JOIN (sigh!).

I'm sure your info is correct on paging, but my point is that nobody would ever want to page over half a million rows. I'd include some kind of date cutoff to restrict the number of rows I need to page over.

But in the polls example I'm sure we won't have to worry about that - it's not likely that you'll have that many polls during your lifetime!

Eric