Again, SPROC VS Dynamic SQL, performance wise RSS

• • Reply
  

  JimRoss [MVP...

  Star

  10080 Points

  2008 Posts

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 27, 2003 12:15 PM|LINK

  Stephen, Yeah in what possible way is it easier to maintain dynamic sqls within a dll? Maybe I misunderstand the meaning, but actually storing SQL in a dll means recompiling the dll any time you have a sql change. Yup. exactly. And it's much easier to test, too.
  Jim
  
  
  MS MVP ASP.NET [VC++/MFC emeritus]
  
  
  Old Dog Learns New Tricks
  
  Preferred programming language: cuneiform on clay tablets
• • Reply
  

  ericy3kok

  Member

  579 Points

  117 Posts

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 27, 2003 11:33 PM|LINK

  Personally, I use SPROCS for the basic operations: insert, delete, update, etc... like for the Orders example, pr_Order_Insert @CustomerID=1 foreach( item in Cart ) pr_OrderDetail_Insert @OrderID=[orderId from above], @ItemID=n, @Quantity=x, @Price=y, @Discount=z so the business rules still exist in the C# class, with the sprocs being an "interface" to the actual database. but as always, sometimes a DynSQL statement is the easiest to deal with... so it really just depends on the frequency of use, IMO...
  ----
  E.Newton
  ASP.Net/C# Solutions Developer and Consultant
  Ensoft Software
  http://www.ensoft-software.com/
  eric@ensoft-software.com.cc (Remove the CC)
• • Reply
  

  russnem

  Contributor

  7001 Points

  1389 Posts

  ASPInsiders

  MVP

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 01:46 AM|LINK

  To be honest with you I've NEVER found any circumstance in which a dynamic SQL statement is easier. Can you perhaps provide an example?
• • Reply
  

  PaulWilson

  Contributor

  3715 Points

  745 Posts

  ASPInsiders

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 02:07 AM|LINK

  I have an application right now that "requires" dynamic sql. It basically matches on an arbitrary list of integers using an IN clause in the sql statement, and parameter arrays are not supported in stored procedures so that isn't an option. There are some "work-arounds", but dynamic sql is both easier, more efficient, and simply more "correct" in this case.
  Thanks, Paul Wilson, ASPInsider, MC**
  
  For the best .NET code, examples, and tools, visit:
  WilsonDotNet.com, WilsonWebPortal.com, ORMapper.net
• • Reply
  

  SoulOfRealit...

  Participant

  775 Points

  155 Posts

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 02:41 AM|LINK

  :: To be honest with you I've NEVER found any circumstance in which :: a dynamic SQL statement is easier. Can you perhaps provide an example? If you have an intelligent broker, (like an O/R Mapper) that creates select/insert/update/delete dynamically based on your data-model, you'll understand the benefits dynamic SQL gives you. Also, its a matter of trust. Have you ever used an O/R Mapper that you trusted generating SQL-statements for you? I haven't. I ended up writing my own, and of course I trust my own, but not many people who know SQL are comfortable letting a tool try to do all the work for them. I've enjoyed reading this thread and realize some performance gains can be had with SPROC's (probably not much). I'm wondering if an ideal O/R Mapper would dynamically create SPROC's instead of prepared SQL cursors. Seems there could be some marginal benefit there.
• • Reply
  

  thona

  Member

  20 Points

  2923 Posts

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 04:43 AM|LINK

  ::To be honest with you I've NEVER found any circumstance in which a dynamic SQL ::statement is easier. What applications have you written? Here is a sample: Online shop, search product. Issue: * Product can ahve an attached attrribute (like product group, color of wine etc.). Basicalyl an attribute is a hierarchy ir values (subvalues allowed, like product group). A product follows a product template in what atrtributes it may have. Make a search function to find a special product. User selects product group first (wine, book etc.), then he CAN select any of the assigned attributes. This boils down to a non-determined number of IN clauses (one for every attribute, the selected value and all subvalues as possible values in the IN), as the number of attributes is determined by the admin, not the developer. You CAN NOT handle this type of query with a SPROC unless you go to EXECUTE In there, which makes it dynamic SQL again.
• • Reply
  

  ngtstlkr

  Member

  95 Points

  19 Posts

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 01:35 PM|LINK

  I have managed 13 developers at a .COM a few years ago, and I wouldnt have trusted 12 of them to be playing around with dynamic SQL or the database for that matter. There are a few choices people have in this: 1. Write bad dynamic SQL and / or database architectures 2. Use an O/R Mapper similar to yours which is a very elegant solution 3. Write their own O/R Mapper which is very time consuming, not practical unless you have a big shop that will pay you to sit and write this, not to mention the skillset required 4. Have a DBA that knows good database architecture write the Sproc API's that all developers must use, impliment the right security mechanisms, etc. All this really does it ensure your datastore is "solid". Most of the times, projects and shops are very small in scope and fall into either doing number 1 or number 4. I have come into too many places since the .COM bomb to find that number 1 seems to be the prevailing way of writing applicaitons. I have come to love starting a project from scratch, so that I dont have to re-architect bad architecture. I am also of the mindset to take any processing time off the database server as possible, keep business logic outside the database. SQL Server does not scale. It load balances, it has failover, it has documented clustering capabilities that Microsoft themselves say does not work and hopefully will in Yukon. Cursors = Bad, dont ever use them if you dont have. 9 out of 10 times the same logic that your doing procedural can be done in set based logic. The last 5 applications I worked with, and re-wrote were using Cursors that I switched to using Set based logic, and turned 1-5 minute queries into sub-second running queries (also a lot of times Cursors are used is due to bad database design in the first place). Temp Tables = avoid... use table variable if needed, unfortunately its really the only way to do recursion unless you understand adjacency list models, and can impliment them correctly.
  Kolchak - The Night Stalker
  
  
  Stalking lines of code that shouldnt be there...
• • Reply
  

  russnem

  Contributor

  7001 Points

  1389 Posts

  ASPInsiders

  MVP

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 02:47 PM|LINK

  In this case I'd still use a stored procedure. I'd pass in those integers as XML to accomplish what you're doing.
• • Reply
  

  russnem

  Contributor

  7001 Points

  1389 Posts

  ASPInsiders

  MVP

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 02:49 PM|LINK

  What is an "O/R mapper"? I haven't used any kind of graphical interface to create SQL statements since my Access 95 days. :) I have noticed much more than "not much" performance increase using stored procedures. But that's just been my experience.
• • Reply
  

  russnem

  Contributor

  7001 Points

  1389 Posts

  ASPInsiders

  MVP

  Re: Again, SPROC VS Dynamic SQL, performance wise

  Aug 28, 2003 02:52 PM|LINK

  I've written tons of applications. The way you're describing this circumstance I don't see any need to use dynamic sql. Can you perhaps post a SQL script so I can get a better idea of what your table schema is?