Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 25th, 2006, 08:09 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post
Default SQL in C# class vs. Dynamic Stored Procedure

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.

  #2 (permalink)  
Old October 25th, 2006, 06:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old October 26th, 2006, 10:04 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!).
  #4 (permalink)  
Old October 26th, 2006, 06:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure in Class prasanta2expert C# 2 July 18th, 2007 09:57 AM
stored procedure dynamic insert harpua Classic ASP Databases 3 January 21st, 2005 12:50 AM
Dynamic stored procedure bsa SQL Server 2000 2 October 12th, 2004 10:05 PM
error building dynamic sql in stored procedure paaf64 SQL Server 2000 3 October 6th, 2004 03:08 PM
Stored Procedure - Dynamic Where Clause Terry_Pino BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 2nd, 2004 04:39 PM



All times are GMT -4. The time now is 03:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.