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
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.
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.
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
webjedi:
it's impractical to have 50 nullable parameters in your SP...what if my customer wants to search on 51 keywords
I am completely ingorant of LINQ but it looks like I really need to get with it. Your comment about the 50/51 parameters is very true - one of the things I don't like is that in that scenario i possibly have to 1. change a sproc signature and details, 2. change the call to that sproc, 3. create a new Criteria constructor and 4. create a business object factory overload. This before considering the client call.
What I have toyed with in the past is......(dba's look away now).... passing the parameters to all sprocs in a single consistent manner - i.e. using xml!. The Criteria class is passed through a xml engine to construct the parameters (<Parameters><Parameter name='xxx'..../>...)and having the sproc then deconstruct the search criteria into the appropriate query language. Also, I have toyed with the idea of not overloading the business object's factory method but rather exposing the inner criteria and allowing that to be the driver ('toyed' - i have not done this in production code...yet).
This means that to add that extra parameter in a search I only need add it to the Criteria (and possibly create a new constructor) and have the client(UI) use it. Can go one step further and if the sproc parameters (now xml) are named closely to the fields they represent (@pUsername relating to Username) even the sproc may not need any updating if the parsing of the xml to build sql is clever enough(this is for faily simplistic queries though).
Now, besides sql injection etc, etc, what I did find when I did all this was.....that the performance hit of parsing the xml was noticeable. Milliseconds but still enough to put me off. But I as using SQL2000 then so maybe there is merit in the approach now with 2005. Can you imagine how beautifully simple it would make 60/70% of business objects/collections and, more importantly, easily mantainable!?
I got curious about Linq performance so I wrote a little test app....trying to do an apples to apples comparison with LinqToSql vs T-SQL and a datareader that created a collection of POCO objects...I'm not certain it was a perfect test but I think it's closer. Interesting results...in larger datasets (50,000 records records) T-SQL was about 3x faster....with smaller sets (1,000 records) Linq was about 2.6x faster.
My full testing method is here: http://weblogs.asp.net/ryansmith/archive/2008/04/28/linq-to-sql-speed-test.aspx
Let me know what you think about the test. It seems weird to me...
Ryan
This is an interesting article. I too am new to LINQ. I read that it was designed to be able to query objects and collections and I completely see that, but I have some questions/concerns that arise when we start applying this to databases and hopefully others can address them.
1. Isn't putting your code inside of a compiled application worse than putting it into stored procedures? The fact that we can use stored procedures (or equivalents) in SQL Server and Oracle in our organization has proven very helpful to us from maintainability perspective. Eliminating them as some have suggested and putting them into compiled code would force us to create a new build of the entire application (or of at least an assembly) for each patch we have to release. We don't have a lot of bugs in our process, but we have a few pop up here and there and they are usually quick fixes.
2. Even if we were to keep the stored procedures and build LINQ on top of them (such as another suggested), what would be the point of that? Wouldn't that just mean now we have two layers of queries to contend with? I can understand going one way or the other, but why both?
3. What about performance? I read the two articles posted above. I know for our queries we run query analyzer on them afterwards with various sets of data to check that they run within a certain amount of time. If I just hand this off to LINQ, how do I know it's going to perform optimally? At least with stored procedures, I have a hand in performance.
4. Is LINQ intended as a replacement for ADO.NET data providers? If so, what about those data sources that do not have a way to be queried from LINQ?
I probably will have more. I'm greatful for your responses!
A
1. Isn't putting your code inside of a compiled application worse than putting it into stored procedures? The fact that we can use stored procedures (or equivalents) in SQL Server and Oracle in our organization has proven very helpful to us from maintainability perspective. Eliminating them as some have suggested and putting them into compiled code would force us to create a new build of the entire application (or of at least an assembly) for each patch we have to release. We don't have a lot of bugs in our process, but we have a few pop up here and there and they are usually quick fixes.
I think it depends on where you stand with regards to what role does your database have in the business application. If you (like me) believe that the database should (in best cases) be merely a highly performing repository, then all business logic (including data selection criteria) should reside within the domain of the business application. However if you are on the other side and the database IS a component of your business application then having that logic in the database is perfectly fine.
2. Even if we were to keep the stored procedures and build LINQ on top of them (such as another suggested), what would be the point of that? Wouldn't that just mean now we have two layers of queries to contend with? I can understand going one way or the other, but why both?
The advantage is that it is strongly typed. I know at the coding level all the properties of the resulting collection of objects and can interact with it in a programatic way.
3. What about performance? I read the two articles posted above. I know for our queries we run query analyzer on them afterwards with various sets of data to check that they run within a certain amount of time. If I just hand this off to LINQ, how do I know it's going to perform optimally? At least with stored procedures, I have a hand in performance.
If your application requires this kind of granular control over your queries then LINQ probably isn't for you. However (IMHO) if you are building a business application and a small performance variance (say of a quarter of a second difference) then it's not something to worry about. If you are building some sort of scientific application that requires every last nanosecond, then you wouldn't want to use LINQ...or .NET for that matter. You can inspect the queries that LINQ produces and paste them into Query Analyzer and I think you'd be fairly pleased with their performance. They won't be as aesthetically pleasing as your hand written queries but they will be really solid. And since the queries produced are parametized the query plan for them will be cached.
4. Is LINQ intended as a replacement for ADO.NET data providers? If so, what about those data sources that do not have a way to be queried from LINQ?
It's not intended as an ADO replacement, rather an augmentation, it's using ADO under the hood. Basically it's an abstraction layer of sorts. There are a TON of LINQtoXYZ providers out there, and new ones are being built all the time.
There are some gotcha's for instance right now people are just getting a good grasp on using LINQ in an N-tier environment and best practices are not yet universally agreed upon. However that being said it won't be long.
Ryan
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.
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
Stored Procedures should always be percieved as a good Abstraction from the database, Easier to update a Stored Procedure in a live environment than code.
I'm not sure about 'always'. And easier yes, but better? That's open to interpretation since one shouldn't be updating a live environment anyway. Having said that I certainly do use them but I also understand where their use is a leaky abstraction where in if I change db vendors I'll most likely have to tweak all the sprocs...using Linq or some other ORM I just acquire an approriate provider and rebuild my relational maps (in theory). I know replacing db vendors will require a little more work than that usually...but at least it's less work.
Admittedly, most of my hesitance to use sprocs everywhere comes from the days before I could source control my db. Now it's become very productive to be able to build a map of my tables a la Linq or other ORM and tighten down the db and have all my logic in one place.
Copyright (c) Marimer LLC