CSLA .NET

You can use LINQ with stored procedures.

I've not used LINQ yet, but if I was to guess...a few might be...stored procedures can be optimized and their data plans cached on SQL Server, so they are more efficient to run on subsequent calls. Store procedures can be changed on the server to fix or enhance their efficiency without having to recompile the application. Having a layer of stored procedures decouples the application from the actual data structures in the database, so again the stored procedure can be modified to reflect data structure changes, but still maintain the same contract with an application.

Des

Query plans since SQL 2k get cached whether they are in a sproc or not... being in a sproc has almost nothing to do with performance... run of the mill queries, once they are run more than once, get their plans cached.

As for changing sproc on the server... I have seen that done a lot in prod in emergency situations, and that normally is a big "oh !!@#" situation that usually should scare the holy hell out of anyone responsible for running a prod environment.  Code in C#/VB usually has more of a change control and test mechanism than sql does, which tends to work better in said change controlled environs.

Sorry... pet peeve :) (sproc vs sql)

Pet peeve acknowledged, and I really don't want to get into a lengthy exchange, especially as mine's probably the reverse, and one eventually needs to decide on how they like their code organized. So I'll close with just one additional post.

Personally, I find SQL code so much harder to read mixed in with regular code, and have encountered plenty of issues by many developers with setting parameter values where there are such things as spaces, commas, semi-colons, etc. in the value. 

Also, I still do like the layer it provides which helps decouple the application from the structure of the database. In my case this is important, as there is a real chance I may need to support a second database down the road, and I want to retain the option to more easily use a different database.

On some minor notes, caching may work second time around, but for expensive SQL statements, (and I've known a few in my time), execution plan information will be retained with SP.

And I don't give anyone direct access to the tables of my database, everyone needs to go through SPs, which allows more security, and more flexibility in what happens when they do so.

At times this separation of code allows me to pass stored procedures to someone who's more knowledgeable about SQL then me to have him write a more efficient SP for me, and thus take advantage of specialized knowledge.

I guess, I feel the larger a system, or the more widely the application is likely to be used, the more likely the need is for having SPs.

Des Nolan

ABC Systems, Inc.

 

"And I don't give anyone direct access to the tables of my database, "

...and therein lies my own pet peeve...DBA's who "own" the data rather that facilitating its use. I recently left a company after 9 years because a DBA kept locking down one permission after another until the .NET development team could not accomplish anything.

The company management was so scared of the DBA that they would not give direction so that the developers could actually do their jobs.

The environment had 3 databases, prod, staging, and dev with appropriate change control, When the developers were prevented from making table changes or writing procedures in DEV (forget prod or staging for the moment) and since a simple change to a stored procedure took a month to work through all the meetings and arguing that the DBA demanded in order to make a change...it just got too difficult to work at that company. In supporting the application for the users, I often had to do ad-hoc queries into the data in prod (read only). When I found my access to the tables had been terminated by the DBA I knew the party was over...it was time to find a company where the DBA had not been to DBA school and was not infected with these rediculous ideas.

Sorry to rant but this is a hot button....

 

Michael,

I feel your pain, however, I believe that some level of control must be provided for a production environment. When your DBA started controlling the development environment the same way, that is where he/she over-stepped their bounds.

Our environment (dev-->prod) has some 20 client databases and the maintenance is getting increasingly more difficult. We do not have a DBA and so the developers are the ones making custom changes to data, stored procedures and structure within all environments...and this drives me crazy.

I suppose the bottom line is to have an environment somewhere in between where your were and we are.

---

Marc Gervais, MCAD

I love how this discussion goes in cycles...SP's good...then SP's bad.  This is my favorite long running debate in development circles.  IMHO SP's have a place for sure, but they do tie you to a database vendor.  They also tie your hands a bit when it comes to flexibility.  Have you ever tried to write a stored proc that had n-parameters?  This is where LINQ beats SP's and even dynamic T-SQL.

SQL will automatically cache the query plan of an SP, and will TRY to cache the query plan of T-SQL if everything matches...however you can promote the resuse of the plan of T-SQL by using parametized queries...in that event there is no difference in performance between SP's and T-SQL.

 

---

Ryan
http://weblogs.asp.net/RyanSmith
http://www.codeplex.com/CSLADesignLibrary3

These are some links about ling performance

http://blogs.msdn.com/ricom/archive/2007/07/16/dlinq-linq-to-sql-performance-part-5.aspx

http://www.codeproject.com/KB/dotnet/LINQ_Performance_Test.aspx

 

Well, I don't think the benefit in using Linq is performance, the benefit is the maintainablity it adds.  Also, the first link mentions something the second doesn't... that Linq is doing other stuff, like making sure the data types match, indexing, etc. that flat Ado.Net code won't do.  Also, it looks like those are both from CTP code, not RTM code..

If you need 100% performance, then maybe linq isn't for you.. but for most people, the other gains from using it outweight the performance costs. 

One of the things that i think stored procedures are great for that is often overlooked (but might be of interest to this group) is their ability to support/encourage data access "patterns". We all love patterns in the UI and business layers but so often I see all that good work fall away when it comes to accessing the data.

I too "encourage" developers to access data via sprocs and not directly. I even go a step futther and encourage developers to access the data via "known" sprocs.  There are a million bads and goods so I never dictate but do "encourage" - there will always been that one instance when something can be done better but these must be the exceptions. I want all my data to be accessed via known CRUD sprocs:
Users->Search(search parameters)
Users->Fetch(known to exist key)
Users->Update(..)
etc. - you get the point - and this is not OR mapping. And often I will use dynamic sql within the sproc if performance is an issue. But, often, I will admit I might return more fields than are needed - i don't believe in this day and age it is a serious problem to return 5 fields when you only need 3 and it makes maintenance much easier.

Too often I have seen projects where there are exponentially more sprocs/queries than tables - stuff like GetUserByName, GetUserById, GetUserByAge....leading to stuff like LoadUserByName....FetchUserByName.....etc.  Especially on large projects in companies that don't traditionally "manage" software. I really have seen some utterly utterly crazy databases out there - especially if dev teams have high turnover - people want to be seen to produce something quickly and get overwhelmed with the existing db so roll their own Fetch/Get/Load/Find..With/By/Using exacerbating the problem (and probably leading to even higher dev turnover :)

Another nice thing about this approach is that with even the simplest data access 'block' we can port our business objects to work on multiple databases literally without any change to the c# code. I have done this with sqlserver, oracle and db2 so far. I even actually do use the naming convention above in sqlserver - makes it much closer to the packages concept in oracle and db2 and i just find it easier to find the sprocs that i might need to manipulate, say, a user.

Now, having said all that we can "pattern" any data access style - its just that I find the sproc way to be more intuitive and easier for developers to appreciate and get with.

 

Our own system was starting to experience the same problem that jh72i described, having 10 different stored procedures for each table; that is we did a massive review and got rid of any silly stored procedure that did nothing else other than "SELECT * FROM TABLE".

I must admit that I've not really had a chance to look at LINQ, however, the one thing that I do like about stored procs is the ability to assign permissions and especially in SQL 2005, the ability to escalate permissions within a stored procedure so that uses can do standard CRUD operations without the need for permissions to do them at a table level.  That's the main reason I use stored procs, the cached plan benefits having been mostly negated these days by using parameter based queries.

Marjon

Ya, I don't find SPs useful for selecting data.  That's what views are for. 

You're also spot on about the permissions... WITH EXECUTE AS OWNER can be useful if used properly.

Finally, there's one more thing I like about SPs.  They help you find things you may have forgotten.  I had an error in my application, and the error was that the proc was getting too many parameters specified.  I had updated my code entity to add a new column, but I had forgotten to update the stored procs.  I fixed the proc, and problem solved.

Now, my point is that it could have easily gone the other way.  I update the table, but forget to update my code.  If I was using an UPDATE query, the new column would have never be updated and it would have been a silent error.  Who knows when I would have found that.  But with a proc, I have to update the table and then the proc.. so if I updated just my proc and table, but forgot to update my code, I'll get an error as soon as that proc is called. 

HTH
Andy

I usually reserve my use of SPs to Inserts, Updates and Deletes...and even then I make sure there's no business logic there...just DB plumbing.

For selecting data (especially searching data) I find I need the versatility of dynamic SQL and now LINQ.  This is most important in say a product searching scenario where you could be passing one, two or three or more keywords to search on, it's impractical to have 50 nullable parameters in your SP...what if my customer wants to search on 51 keywords.  This is where LINQ shines IMHO.

Ryan

---

Ryan
http://weblogs.asp.net/RyanSmith
http://www.codeplex.com/CSLADesignLibrary3