http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Very rarely sprocs will give you any real performance benefits while making things much more complecated for developer team. http://ayende.com/3836/how-did-i-end-in-this-position#comment22http://ayende.com/3836/how-did-i-end-in-this-position#comment22Thu, 29 Jan 2009 22:59:40 GMTNo one has provided a hypothesis for how Ayende ended up in this position yet. Personally I'm not sure how either, but I'd sure be interested to hear the back story. http://ayende.com/3836/how-did-i-end-in-this-position#comment21http://ayende.com/3836/how-did-i-end-in-this-position#comment21Thu, 29 Jan 2009 22:46:09 GMTUnless ORM and IoC are religions I don't understand the first post :) We all know that a good programmer simply uses or builds the best tool to solve a problem. I guess the post is for us to do some guess work. So here it is one: Performance! Nuno Lopes http://ayende.com/3836/how-did-i-end-in-this-position#comment20http://ayende.com/3836/how-did-i-end-in-this-position#comment20Thu, 29 Jan 2009 15:00:23 GMTIf you're using sprocs because your CRUD underperforms then your code or database likely needs refactored. http://ayende.com/3836/how-did-i-end-in-this-position#comment19http://ayende.com/3836/how-did-i-end-in-this-position#comment19Thu, 29 Jan 2009 14:08:16 GMT1. Cleaner to use Linked server. 2. Some queries can be better optimized (read run faster) in stored procs than .net code or Linq to Sql. A good DBA can do miracles increasing performance in complex queries. 3. Easy to modify the data access without re-compiling code. http://ayende.com/3836/how-did-i-end-in-this-position#comment18http://ayende.com/3836/how-did-i-end-in-this-position#comment18Thu, 29 Jan 2009 04:29:58 GMTStored procedures have their uses. They can prevent users from batch updating or deleting the data. Sprocs also make it easier to work with data on linked servers or perform complex data logic when entities do not directly match to tables. http://ayende.com/3836/how-did-i-end-in-this-position#comment17http://ayende.com/3836/how-did-i-end-in-this-position#comment17Wed, 28 Jan 2009 22:48:59 GMTWhat's a stored procedure? http://ayende.com/3836/how-did-i-end-in-this-position#comment16http://ayende.com/3836/how-did-i-end-in-this-position#comment16Wed, 28 Jan 2009 20:16:01 GMTI guess that what they mean by "the exception that proves the rule" http://ayende.com/3836/how-did-i-end-in-this-position#comment15http://ayende.com/3836/how-did-i-end-in-this-position#comment15Wed, 28 Jan 2009 19:39:23 GMTGo, check out Gloria Gaynor's "I will survive" lyrics, exchange "you" with "stored procedure", add a salt of fantasy and you may have a chuckle or two.. http://ayende.com/3836/how-did-i-end-in-this-position#comment14http://ayende.com/3836/how-did-i-end-in-this-position#comment14Wed, 28 Jan 2009 18:48:01 GMT@Mikael one the worst perversities i've seen is from the offshore team that redesigned a web app at a former employer. i was trying to create a webcontrol to integrate into the page pipeline, and i could not figure out where the HTML was being generated. Turns out that the code called a stored procedure, which called a .NET assembly, which used ADO.net to query the database, which generated an XML string, which the assembly manipulated to produce HTML which the procedure returned to front end, needless to say, im glad im gone. http://ayende.com/3836/how-did-i-end-in-this-position#comment13http://ayende.com/3836/how-did-i-end-in-this-position#comment13Wed, 28 Jan 2009 15:22:13 GMT@Bob, You do realize Ayende is a contributor to NHibernate right? http://ayende.com/3836/how-did-i-end-in-this-position#comment12http://ayende.com/3836/how-did-i-end-in-this-position#comment12Wed, 28 Jan 2009 14:49:11 GMTTo me it sound like it would be more interesting to know, not how you ended up in that position, but why your fellow team mate didn't ;) Especially in the view of the italic "I". Lack of dedication maybe? http://ayende.com/3836/how-did-i-end-in-this-position#comment11http://ayende.com/3836/how-did-i-end-in-this-position#comment11Wed, 28 Jan 2009 12:55:51 GMTWhat's wrong with stored procedures? I usually write my data access code in stored procedures because it's much more efficient than calling SQL via .NET code and you have more control over the data through T-SQL functions etc. http://ayende.com/3836/how-did-i-end-in-this-position#comment10http://ayende.com/3836/how-did-i-end-in-this-position#comment10Wed, 28 Jan 2009 11:14:10 GMTAyende, Yes. It seems to me that you're generally against them and this is a special case. I was asking why this is such a special case. http://ayende.com/3836/how-did-i-end-in-this-position#comment9http://ayende.com/3836/how-did-i-end-in-this-position#comment9Wed, 28 Jan 2009 09:24:10 GMTWell, if you've ever written a TSQL 'for xml explicit' query (multiple elements deep) you really start to appreciate stored procedures. Calling them from an SQL Agent job performing cleanup and other maintenance tasks is another useful usage of stored procedures. http://ayende.com/3836/how-did-i-end-in-this-position#comment8http://ayende.com/3836/how-did-i-end-in-this-position#comment8Wed, 28 Jan 2009 08:30:11 GMTI once found a stored procedure that returned html. The funny part was the TSQL logic that would set class attribute on a div depending on a query result. But I guess we can't blame the technique for that mess. ;) http://ayende.com/3836/how-did-i-end-in-this-position#comment7http://ayende.com/3836/how-did-i-end-in-this-position#comment7Wed, 28 Jan 2009 06:43:30 GMTConfigurator, Did you READ the post? http://ayende.com/3836/how-did-i-end-in-this-position#comment6http://ayende.com/3836/how-did-i-end-in-this-position#comment6Wed, 28 Jan 2009 02:41:10 GMTWe all eventually become the thing we hate; its as inevitable as the sun rising tomorrow :) http://ayende.com/3836/how-did-i-end-in-this-position#comment5http://ayende.com/3836/how-did-i-end-in-this-position#comment5Wed, 28 Jan 2009 02:05:31 GMTDefinitely useful if your standard CRUD operations underperform. Can't see any other reason for them though, can you? http://ayende.com/3836/how-did-i-end-in-this-position#comment4http://ayende.com/3836/how-did-i-end-in-this-position#comment4Wed, 28 Jan 2009 00:37:24 GMT"Stored Procedures have their time and place." Time = 1997 Place = Sql Server 7 http://ayende.com/3836/how-did-i-end-in-this-position#comment3http://ayende.com/3836/how-did-i-end-in-this-position#comment3Wed, 28 Jan 2009 00:37:04 GMTWhy are you against stored procedure? As a rule, they are bad IMO in cases of abstracting simple select/insert/update, but for complex database operations I think they're quite useful http://ayende.com/3836/how-did-i-end-in-this-position#comment2http://ayende.com/3836/how-did-i-end-in-this-position#comment2Wed, 28 Jan 2009 00:34:56 GMTStored Procedures have their time and place. [http://twitter.com/jeffhandley/statuses/1055970127](http://twitter.com/jeffhandley/statuses/1055970127) While I was comparing to Linq to SQL, I imagine it would have been a LOT of effort to make any ORM perform the way the stored proc did in that case. And performance was a primary requirement for the task at hand. http://ayende.com/3836/how-did-i-end-in-this-position#comment1http://ayende.com/3836/how-did-i-end-in-this-position#comment1Wed, 28 Jan 2009 00:20:41 GMT