August 13, 2012 at 7:43 am
vliet (8/13/2012)
Í am both a developer of ASP.NET MVC solutions and a professional MSSQL DBA....
+1000
Couldn't agree more.
On portability, I agree with Steve that nowadays it's not as important as it used to be, let's say, 10 years ago.
I used to work in a software house selling proprietary ERP and CRM products. Both were said to be "portable" across RDBMSs, but the price to pay to gain the portability was poor SQL all around and complete absence of set-based code. The result was an application that could bring Oracle as well as Informix to its knees.
There was no ORM there, they arrived some years later, so the point is against portability per se, not against ORMs in particular.
At the time, many clients were concerned about mixing *NIX systems and Windows systems and the ability to sell cross-database products was a plus.
Nowadays having a mixed environment is very common and technologies and patterns such as SOA and ESB changed the rules of the game. So, what's the point about portability again?
In my humble opinion and experience, what I have seen over the years is good databases surviving much longer than the applications running on them. Under this light, portability is one of the weakest arguments in favor of ORMs.
-- Gianluca Sartori
August 13, 2012 at 7:48 am
Paint me cynical, but I've never yet seen anyone but a database professional build a database, or the code to access it, in such a way that it didn't end up crippling the application.
"Why Devs Shouldn't Write Procs" would have been a better title for that article.
LINQ and other ORM tools can make writing code faster. But they also make it, in the real world, so that a DBA cannot come along and optimize data structures, without having to re-write code in higher levels.
If all CRUD is through procs, I can completely rebuild a database, allowing for significant business-changes, without having to change the database APIs (procs) in terms of inputs or outputs. In other words, legacy applications can continue working without having a team of devs rebuild and re-test all of them, since the API calls are unchanged. While still allowing for radical changes in data architecture.
I've had to do this in every business I've worked for. Every time, the devs find it makes their lives easier, more productive, etc.
In other words, we do the exact thing that has always worked best for every human endeavor that's ever succeeded: We specialize our knowledge and skills, and use each other as symbiotes that way.
In a perfect world, where data is perfectly modeled the first time, and no business change ever happens that renders the model obsolete, where every dev knows how to write perfect code and perfect database calls to that perfect database, and where legislation never causes any data retention rules to ever changes, and so on, ORM would be the perfect solution that so many devs dream about. Fortunately, we don't live in that perfect world (because it would be one heck of a boring place!), so specialization of knowledge and skills is better than trying to fix every problem with a hammer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 7:55 am
Like everyone else, I think I'm there on the CRUD stuff. Although, with some of our apps where the business logic is inherently deeply engrained with the data, I often produce what I call "handlers" for my .NET folks - which is basically a one stop shop procedure that interacts with all the CRUD procs for a particular entity group and makes decisions based on myriad items in the DB at that moment.
I can see his approach working on smaller stuff. But, over the last few years I've been working on custom integration applications that interact with our ERP. Just about any screen of real value sits on top of a sproc that heavily uses CTE's, UNIONS, INNER/OUTER/FULL JOINS, subqueries, user defined scaler functions, user defined table functions, in-line views (rarely ever persisted views though), and 200 to 1500 lines of code.
Lots of times I spend a portion of my time switching out approaches to goose performance and review query plans.
Forget my heaviest data mod procedures...I don't even want to think about those outside the DB.
I guess my point is once you are doing some real heavy lifting (and I don't mean ETL) with data or logic that naturally sits close to the DB, I can't imagine wanting to do it in .NET across the network from my DB server.
August 13, 2012 at 9:00 am
Alex Fekken (8/12/2012)
FIshNChipPapers (8/12/2012)
I agree with the vast majority of both the original article and your comments. I was endeavouring to identify a scenario where the ability to develop code that can be ported between database back ends could be a requirement.Maybe I caused some confusion: I meant that Steve's counter-argument is circular.
I think there are lots of reasons why portability would be desirable or even a requirement. But as soon as stored procedures become a "best practice", portability goes out the window as a result (and not because we didn't want it in the first place).
I'd disagree. Most companies write internal apps. Those aren't ported among platforms for a few reasons, but the fact that people have expertise in one area, and there is rarely a business benefit for changing platforms, means that porting doesn't happen.
If you develop software for sale, this can be an issue, but that's not 99.9% of software.
August 13, 2012 at 9:02 am
vliet (8/13/2012)
...I say 'rather crippled' because every programmer can give you plenty examples about the impedance mismatch between object-oriented and relational data models. DBA's should act a lot more like data consultants, understanding the needs of programmers and translate those needs into flexible dadatabase models.
...
But DBA's are expensive and generally considered a hurdle in the development process, so it's our task to change that image and cooperate instead of pointing at each other.
Very true. When I've worked closely with developers (that let me do so), we were a team and the stored proc wasn't a slowdown in the process.
August 13, 2012 at 10:22 am
Here are just some of the reasons that I use stored procedures:
Security
The first and most important reason to use stored procedures is to take advantage of the security benefits that they offer. Using stored procedures can prevent SQL injection attacks, something that all of us have probably experienced and something these other data access methods can’t totally eliminate. Parameterize queries are much better than dynamic SQL, granted, but it does not eliminate the threat completely
Performance
Parameterized queries are cached versus Dynamic SQL, granted. However, other factors that can affect performance besides caching such as security checks and controls, stored procedures offers advantages that parameterized queries just cannot.
Organization
Stored procedures encapsulate the logic of accessing and retrieving data and expose an interface that is accessed via the connection. Placing SQL code in the data access layer removes the interface capabilities of SQL Server, making it an extension of the data access layer code. All SQL code maintenance now needs to be done in the .NET code instead of in the database itself. Bad idea, and it will ultimately lead to app code being pushed to production with much more regularity..
Maintenance and Scalability
If a stored procedure needs to be modified and still returns the same structure of data, then the only piece of the entire enterprise application that needs to be touched is SQL Server itself. The stored procedure can be changed without requiring any recompilations of any other part of the application. By not using stored procedures, you are pretty much guaranteed to have to recompile and distribute application code more often, as stated above
Simple applications that use parameterized queries might very well perform as well as an equivalent application written using stored procedure, but will they always stay that way? As the application grows in complexity and needs to scale, stored procedures offer the best solution for growing and maintaining a scalable, enterprise-level application. Just another take on this to consider.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 13, 2012 at 1:25 pm
TravisDBA (8/13/2012)
Here are just some of the reasons that I use stored procedures:Security
The first ...
Performance
Parameterized queries are ...
Organization
Stored procedures encapsulate the logic of accessing and retrieving data...
Maintenance and Scalability
If a stored..
I will give rhunt their point, I think they have a very good case. vliet speaks a lot of sense too. SSRS needs (IMO) to be based on procs. However I really think you are still considering the issues from an outdated perspective however, perhaps you should re-examine? Security - sps can be vulnerable to injection, L2S/EF are extremely unlikely to be (our systems are under constant humdrum attack, like most web facing systems). Performance, I think is a moot point, scalability might be an issue if you are not careful. Organisation and maintenance in particular however are very strong points in favour of ORMs. Compile time error location in particular is really helpful. Is it really harder to roll out a new logic library than a new proc?
I obviously do not know your circumstances but nothing you say tips the balance in favour of SP usage for your case. If your customers (like ours) want very rapid, accurate development then perhaps consider a rethink.
August 13, 2012 at 1:48 pm
I obviously do not know your circumstances
True Story. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 13, 2012 at 1:51 pm
call.copse (8/13/2012)
TravisDBA (8/13/2012)
Here are just some of the reasons that I use stored procedures:Security
The first ...
Performance
Parameterized queries are ...
Organization
Stored procedures encapsulate the logic of accessing and retrieving data...
Maintenance and Scalability
If a stored..
I will give rhunt their point, I think they have a very good case. vliet speaks a lot of sense too. SSRS needs (IMO) to be based on procs. However I really think you are still considering the issues from an outdated perspective however, perhaps you should re-examine? Security - sps can be vulnerable to injection, L2S/EF are extremely unlikely to be (our systems are under constant humdrum attack, like most web facing systems). Performance, I think is a moot point, scalability might be an issue if you are not careful. Organisation and maintenance in particular however are very strong points in favour of ORMs. Compile time error location in particular is really helpful. Is it really harder to roll out a new logic library than a new proc?
I obviously do not know your circumstances but nothing you say tips the balance in favour of SP usage for your case. If your customers (like ours) want very rapid, accurate development then perhaps consider a rethink.
ORM has a definite advantage in time-to-market.
Security, I agree with you. Either can be secured adequately. SPs can be more secure, if access is gained to the DAL without going via the higher layers, but that's (a) uncommon, and (b) an indication that you probably have worse problems than simple data security issues.
Performance, can go either way. No advantage to either. If you have issues with network bandwidth saturation between your app and you database server, then SPs have an advantage in terms of bytes you have to send to the DB server. Again, that's going to be a rare situation.
Stored procedures as a database API vs ORM as a database API, neither has any mechanical advantage.
The only real difference is who builds and maintains them. Hence, the point of my prior post. In over a decade of working with devs, both skilled and unskilled, I've never yet met one who can get the kind of performance, scalability, reliability, et al, that a database specialist can get. But a database specialist can't get the kind of speed of development in layers above the database, that a skilled software specialist can get. It's not uncommon, however, for a good database specialist to be able to architect a database that will be fast in the data layer, and to build and optimize an SQL-based API for that layer, with speed, etc., built in.
ORM gets to market first. And then it runs into performance, scalability, corruption, and other issues, and they can't be fixed in the database or DAL layers by the very people who are most qualified to fix them. Instead, they have to be fixed in higher layers, by the very people who created the problems in the first place.
Every day, I see devs build ORM code that is standard, quick to write, easy to test, and which kills performance, and/or corrupts data (non-ACID transactions), or relies on features in the database that don't actually exist (order of rows in a table, for example). I see skilled devs writing one-true-lookup tables, and then complaining that "the database is slow". I see violations of first normal form, and then complaints about how complex the code has to be to compensate for that. All of these things stem, not from stupidity or lack of competence in their own field, but from lack of knowledge outside their specialty.
Nobody expects a professional baker to be able to rebuild a transmission. But, on a regular basis, people, including the devs themselves, expect that software specialists can build a good database and a good API into it. It's the same thing as expecting a Windows administrator to know how to do point-in-time restores on database backups. But, too many people don't see software development and database work as being different enough to be separate specializations.
It's a form of the Dunning-Kruger effect, honestly. (http://en.wikipedia.org/wiki/Dunning-Kruger_effect)
There are exceptions. They are extremely rare, just like any other type of real polymath.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 1:59 pm
GSquared (8/13/2012)
If all CRUD is through procs, I can completely rebuild a database, allowing for significant business-changes, without having to change the database APIs (procs) in terms of inputs or outputs. In other words, legacy applications can continue working without having a team of devs rebuild and re-test all of them, since the API calls are unchanged. While still allowing for radical changes in data architecture
This.
I do a lot of optimisation for clients. When the data access is via stored procedures then I can do all the work, the testing and all the client has to do is run the alter statements (and create index statements) and pay the bills.
When the data access is direct from the app, embedded T-SQL in VB/VBScript/C#/whatever then all I can often do is advise the client on how their developers need to change their front end code. Doesn't work well, the devs are often too busy with new features to make the recommended changes and everyone gets frustrated (client because they don't see the performance gains, me because I see the same problems month after month)
Simple single-table CRUD, by all means use an ORM. Saves everyone's time. As soon as the database request is more than a trivial select, please use a procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 2:36 pm
When the data access is direct from the app, embedded T-SQL in VB/VBScript/C#/whatever then all I can often do is advise the client on how their developers need to change their front end code. Doesn't work well, the devs are often too busy with new features to make the recommended changes and everyone gets frustrated (client because they don't see the performance gains, me because I see the same problems month after month).
I agree, this is a bad idea, as I stated above. Also, as you stated, ORM is fine for submitting very SIMPLE (emphasizing the word simple here) queries. But most industrial strength SQL code is just not that simple.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 13, 2012 at 2:49 pm
TravisDBA (8/13/2012)
When the data access is direct from the app, embedded T-SQL in VB/VBScript/C#/whatever then all I can often do is advise the client on how their developers need to change their front end code. Doesn't work well, the devs are often too busy with new features to make the recommended changes and everyone gets frustrated (client because they don't see the performance gains, me because I see the same problems month after month).
I agree, this is a bad idea, as I stated above. Also, as you stated, ORM is fine for submitting very SIMPLE (emphasizing the word simple here) queries.:-D
And even then, on simple queries, it has to assume no underlying database object will be changed without also recompiling, redeploying, etc., the code that accesses it.
If a table has X columns, and the default for LINQ is to call all columns (equivalent to Select *), which it is, adding a column will require at least verifying every piece of code that accesses that table.
If all access to the table is done via procs, and they use explicit column naming, then NO code will have to be rewritten to accommodate an added column. Zero. Just whatever will take advantage of the new column, which may very well be new code with new features. This means zero work in legacy apps.
If the legacy apps use LINQ, and use the default Select * equivalent, you have to assume they'll all break. And suddenly that new column could require months of work to add without breaking them.
So, even for simple CRUD, I prefer procs.
And since it's dead easy to generate default CRUD statements in proc format for any given table, with a simple dynamic SQL statement that addresses sys.columns by object_id, I'm not even sure it really saves and dev time.
I have a script that will create default CRUD for all tables in a database, or for a specific object, just depending on whether an input parameter is provided or not (input param is an object ID). If a second parameter is provided, it will just to whichever type of CRUD is needed for the object specified or all objects. Takes almost zero time to create them all. And they'll all follow pre-established naming conventions, won't accidentally miss any columns, won't try to insert/update ID columns, and so on.
Of course, they can be customized afterwards, if desired. They don't yet follow prefered formatting conventions (they look horrible in an editor, till I use SQL Prompt on them, which again takes very little time, and has no functional need). But they can include auto-documentation, if desired.
Will do most databases in about 1 second. Is that so much slower than using LINQ for that kind of thing? I haven't found it to be so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 2:52 pm
Had a belated thought on "portability", too. Just to be contrarian.
A database accessed through a .NET DAL, or JAVA DAL, you may be able to swap out the data layer with no work, but you won't be able to use a different development platform on it. If you swap from being a .NET shop to a JAVA shop, you'll have to rewrite your DAL to the new engine. If, however, your database uses stored procs, you won't have to rewrite your DAL at all.
So my database with procs is more "dev-platform agnostic" than your "agnostic data persistence layer".
So, let's call the whole thing off. 😛
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 3:05 pm
Wow, that describes us perfectly. DBAs coming in very late in the game to try and improve performance when the ORM generates the SQL. We can tweak around the edges with index changes and show how something like gathering an initial record set into a temp table can be faster than multiple joins on huge tables in one statement.
But changing the ORM to do things our way leaves us dependent on, as you say, DEVs who are too busy or don't know how to full our requests.
August 14, 2012 at 3:35 am
GSquared (8/13/2012)
And even then, on simple queries, it has to assume no underlying database object will be changed without also recompiling, redeploying, etc., the code that accesses it.
If a table has X columns, and the default for LINQ is to call all columns (equivalent to Select *), which it is, adding a column will require at least verifying every piece of code that accesses that table.
If all access to the table is done via procs, and they use explicit column naming, then NO code will have to be rewritten to accommodate an added column. Zero. Just whatever will take advantage of the new column, which may very well be new code with new features. This means zero work in legacy apps.
If the legacy apps use LINQ, and use the default Select * equivalent, you have to assume they'll all break. And suddenly that new column could require months of work to add without breaking them.
So, even for simple CRUD, I prefer procs.
And since it's dead easy to generate default CRUD statements in proc format for any given table, with a simple dynamic SQL statement that addresses sys.columns by object_id, I'm not even sure it really saves and dev time.
I have a script that will create default CRUD for all tables in a database, or for a specific object, just depending on whether an input parameter is provided or not (input param is an object ID). If a second parameter is provided, it will just to whichever type of CRUD is needed for the object specified or all objects. Takes almost zero time to create them all. And they'll all follow pre-established naming conventions, won't accidentally miss any columns, won't try to insert/update ID columns, and so on.
Of course, they can be customized afterwards, if desired. They don't yet follow prefered formatting conventions (they look horrible in an editor, till I use SQL Prompt on them, which again takes very little time, and has no functional need). But they can include auto-documentation, if desired.
Will do most databases in about 1 second. Is that so much slower than using LINQ for that kind of thing? I haven't found it to be so.
I say old chap, you are obviously a top dba but I would comment that your post is a just a tad 'What is this beatles band?' 🙂
On your first point how many times do you get procs breaking because of a renamed column or other refactored ? Of course you have the discipline to get that right and use suitable tooling to alter the procedures. LINQ or EF give you the broken stuff just attempting a recompile. This is very good for less experienced types.
Point 2 I don't know how you achieve a 'SELECT *' - it heas never been emitted from any ORM I have used (straw man).
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!
I happily accept your obviously superior database expertise (I'm primarily a dev). I also think you need to fight your battles on the right front and this is one where you are content with your way of working. I do think though if I could show you how I have set things up you would be able to see many obvious advantages - I (and the team in our shop) like it plenty and think we avoid heinous database transgressions pretty well. I guess I should STFU and document my methodology in an article 😉
On portability (later post) I might add Fluent NHibernate is supposedly another brilliant way of working. I thought we were not worried about that except for COTS though?
Viewing 15 posts - 16 through 30 (of 142 total)
You must be logged in to reply to this topic. Login to reply