August 17, 2012 at 1:28 pm
TravisDBA (8/17/2012)
Indianrock (8/17/2012)
At our company, security auditors are looking to see if anyone who has the term "developer" associated with their name has access to production data.That is called "Sarbanes-Oxley" (SarBox) and its standards basically involves separation of duties. More and more companies today are coming under its standards requirements, like it or not. The days of one "Jack-of-All-Trades" person wearing many hats at once are quickly approaching a sunset. In my shop, DBA's and developers are not put in the same group purposely. It is deemed a "conflict of interest", and I am not so sure I disagree with this concept either. 😀
At some point, even under SOX, you may still have a single person wearing multiple hats. If the team supporting the systems is too small for a complete separation of duties, it will still happen. The key is to have solid procedures and documentation in place to ensure that what SOX is meant to help prevent is properly dealt with in the organization.
August 17, 2012 at 3:40 pm
I'm always on the fence with this one, leaning more toward Stored Procs.
From a developer's perspective I have issues with both.
From an enterprise data perspective, I prefer stored procs because most of the maintenance, security, query optimization(thank you DBA's) can be supported by DBA staff. A DBA can look at execution plans and run traces and do what they know, but they may not know the scalability of each ORM.
Business needs change and get more complex so you start ending up with so many stored proc variations or a one size fits all stored procs.
For Testing, Proof of Concept, and dog and pony shows, ORM. For a small lightweight app I'd use ORM provided that I fully knew how it gets the data and for me it has to generate the SQL it uses so I can put it in SSMS and see an execution plan.
We've even used ORM's on a few larger apps that call both stored procs and generate parameterized sql. If it's an established and frequently used process then we tend to move it out of the ORM and over to the stored proc side.
Like most DBA's and Developers I've seen everything; the good and bad from both. I've been in heated meetings where DBAs claim that the ORM is the problem only to find out that the statistics on the table are way off so the optimizer chose CI scan instead of an index seek.
In another meeting it turned out that the (former) programmer's code was making 9 million selects due to where he retrieved the code (in a loop statement). There was nothing wrong with the stored proc. The code had passed testing because the test tables had limited records. As the production data got larger so did the response time.
I've seen apps where there were just 2 select stored procs for each table/view: A single read by ID or a get all rows and then they remove the unused rows after they created all of the ORM objects.
I love/despise the simplicity of ORMs for the data layer abstraction and I love/despise the stored procs for the things they can do quite easily with relational/non-relational data and set-based approaches that some ORMs cannot always do (CTEs, inserting multiple records from a table variable instead of all of the round trips and data conversion between ORM objects SQL objects, recursive queries, etc. ).
Stored procedures shouldn't be avoided. They have many benefits, but with a good DBA and a good development team you can make a solid database and application with indexes, partition strategies and other schemes and keep the ORM in the mix to be adaptable to your business needs.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 18, 2012 at 2:16 am
@charles.byrne
Nice post and pretty much where I stand - although I would say I am far less concerned these days at using ORM technology a little more extensively than the dog and pony show end these days, so I'm leaning slightly the other way. I am not sure if we have just found a sweet spot or if it is that hard to get right but our very DTO setup is very KISS and does not get misunderstood, works very efficiently it seems and binds and renders just beautifully. Clearly though, you will need a fair number of procs on any mid sized project whatever else you do.
August 20, 2012 at 6:08 am
charles.byrne (8/17/2012)
I'm always on the fence with this one, leaning more toward Stored Procs.From a developer's perspective I have issues with both.
From an enterprise data perspective, I prefer stored procs because most of the maintenance, security, query optimization(thank you DBA's) can be supported by DBA staff. A DBA can look at execution plans and run traces and do what they know, but they may not know the scalability of each ORM.
Business needs change and get more complex so you start ending up with so many stored proc variations or a one size fits all stored procs.
For Testing, Proof of Concept, and dog and pony shows, ORM. For a small lightweight app I'd use ORM provided that I fully knew how it gets the data and for me it has to generate the SQL it uses so I can put it in SSMS and see an execution plan.
We've even used ORM's on a few larger apps that call both stored procs and generate parameterized sql. If it's an established and frequently used process then we tend to move it out of the ORM and over to the stored proc side.
Like most DBA's and Developers I've seen everything; the good and bad from both. I've been in heated meetings where DBAs claim that the ORM is the problem only to find out that the statistics on the table are way off so the optimizer chose CI scan instead of an index seek.
In another meeting it turned out that the (former) programmer's code was making 9 million selects due to where he retrieved the code (in a loop statement). There was nothing wrong with the stored proc. The code had passed testing because the test tables had limited records. As the production data got larger so did the response time.
I've seen apps where there were just 2 select stored procs for each table/view: A single read by ID or a get all rows and then they remove the unused rows after they created all of the ORM objects.
I love/despise the simplicity of ORMs for the data layer abstraction and I love/despise the stored procs for the things they can do quite easily with relational/non-relational data and set-based approaches that some ORMs cannot always do (CTEs, inserting multiple records from a table variable instead of all of the round trips and data conversion between ORM objects SQL objects, recursive queries, etc. ).
Stored procedures shouldn't be avoided. They have many benefits, but with a good DBA and a good development team you can make a solid database and application with indexes, partition strategies and other schemes and keep the ORM in the mix to be adaptable to your business needs.
Couldn't agree more. Perfect summation.
- 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 20, 2012 at 1:30 pm
call.copse (8/18/2012)
@charles.byrneNice post and pretty much where I stand - although I would say I am far less concerned these days at using ORM technology a little more extensively than the dog and pony show end these days, so I'm leaning slightly the other way. I am not sure if we have just found a sweet spot or if it is that hard to get right but our very DTO setup is very KISS and does not get misunderstood, works very efficiently it seems and binds and renders just beautifully. Clearly though, you will need a fair number of procs on any mid sized project whatever else you do.
Thanks call.copse. More power to you. We are getting there, but there have been too many changes in leadership where I work and we've jumped from one ORM adoption to another. Rail's ActiveRecord, NHibernate and Hibernate and MS EF have had staying power though with our .net and Java developers so supporting them is getting easier.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 20, 2012 at 1:33 pm
Couldn't agree more. Perfect summation.
I thought I was a bit long-winded myself. 😉
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 20, 2012 at 2:15 pm
charles.byrne (8/20/2012)
Couldn't agree more. Perfect summation.
I thought I was a bit long-winded myself. 😉
Read a few of my posts. Long-winded doesn't even begin to do justice to my oververbosity. :w00t:
- 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 20, 2012 at 8:17 pm
Humbly beg to disagree:
1. Forcing the developers to access/modify data through stored procedures ONLY enforces whatever business logic there there is and protects your database from stupidly written queries. Any "respectable" db is meant to be used for years and years and you never know who's going to rite what say 10 years from now and what language he/she will then use.
2. The argument that SPs tie you to a particular engine is simply ridiculous: if you decide to migrate/upgrade, having all the db-related code in the db itself allows you to at least give it to knowledgeable DBAs to rewrite, in other words facilitates the partitioning of the migration process into independently manageable pieces. In contrast, having db code on the client side, forces you to first find it in order to modify it. Right now we have a MS SQL 2000 to 2005 (don't ask why 2005) migration projects with tons of non-compliant outer joins (coded as *= in cold fusion). Took me a lot of powershelling to scan all the CF files just to identify what needs to be changed.
3. What about optimization? What about making sure tables are always updated in the same order to minimize the probability of deadlocks? These are certainly better handled in SPs or at least better controlled via SPs.
August 21, 2012 at 11:29 am
GSquared (8/17/2012)
I'm not going to dignify you with further discussion. I asked about the difference between the two, if you actually read what I wrote, and you respond to it in this manner?
Edit: Part of what I wrote was inappropriate and I shouldn't have included it. To anyone who read it before this edit, I appologize for going off like that. Not professional, not appropriate, shouldn't have thought it much less wrote it.
Mr. GSquared,
I want to say, publicly, that I apologize for giving offense. I had a very bad week and reacted to what I thought was a shot at me in your previous reply. I realize now that I was wrong in that and I was wrong to take a shot at you in my response, it was unprofessional and shouldn't have been written.
August 21, 2012 at 11:34 am
venoym (8/21/2012)
GSquared (8/17/2012)
I'm not going to dignify you with further discussion. I asked about the difference between the two, if you actually read what I wrote, and you respond to it in this manner?
Edit: Part of what I wrote was inappropriate and I shouldn't have included it. To anyone who read it before this edit, I appologize for going off like that. Not professional, not appropriate, shouldn't have thought it much less wrote it.
Mr. GSquared,
I want to say, publicly, that I apologize for giving offense. I had a very bad week and reacted to what I thought was a shot at me in your previous reply. I realize now that I was wrong in that and I was wrong to take a shot at you in my response, it was unprofessional and shouldn't have been written.
Works by me.
These things can get heated for reasons that, when looking back at them, almost always look dumb, even to the parties involved. "I reacted to what by doing what? What was I thinking?!?" Happens all the time.
I think, simply put, our discussion boils down to, "When you have access to a good database pro, procs can be a good option, but they aren't strictly speaking necessary, because of modern tools". If that's the case, and we both agree on that point, then we've beaten this dead horse enough, and I think we should go find another dead horse to kick around a bit. :w00t:
- 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 21, 2012 at 1:29 pm
Lynn Pettis (8/17/2012)[/b
At some point, even under SOX, you may still have a single person wearing multiple hats.
I agree, they are still there, even under SOX, but now they are being reported to the SOX auditors when they are on site, by other employees. In some shops this is actually encouraged.:-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 29, 2016 at 11:49 pm
I would say the speed of development is 60% quicker than using a pure sp approach
I am not against the ORM approach completely and I must say the first time I developed using ORM I was not very pleased with not using stored procedures but it grew on me later on. Jeff, though, has a valid argument and what does it help me if I save 60% on development time if in the end I lose database speed.
The other thing is, although ORM is good for simple select, update, insert and delete procedures, you cannot totally replace stored procedures where you have multiple updates and inserts and some of these requires some major calculations in them. If I am developing with a complete new database with no stored procedures I might just go the ORM way but where I am just redeveloping with an existing database I think I will lean closer to stored procedures.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
August 30, 2016 at 12:24 am
manie (8/29/2016)
I would say the speed of development is 60% quicker than using a pure sp approach
I am not against the ORM approach completely and I must say the first time I developed using ORM I was not very pleased with not using stored procedures but it grew on me later on. Jeff, though, has a valid argument and what does it help me if I save 60% on development time if in the end I lose database speed.
The other thing is, although ORM is good for simple select, update, insert and delete procedures, you cannot totally replace stored procedures where you have multiple updates and inserts and some of these requires some major calculations in them. If I am developing with a complete new database with no stored procedures I might just go the ORM way but where I am just redeveloping with an existing database I think I will lean closer to stored procedures.
Going solely with ORM generated SQL? You lose the ability to properly tune the SQL used to access the database. If all you have are simple queries for SELECT, UPDATE, INSERT, and DELETE then there probably isn't much to tune. Once the queries get moderately to extremely complex, you are going to wish you had more control of the SQL code used.
August 30, 2016 at 12:31 am
Lynn Pettis (8/30/2016)
manie (8/29/2016)
I would say the speed of development is 60% quicker than using a pure sp approach
I am not against the ORM approach completely and I must say the first time I developed using ORM I was not very pleased with not using stored procedures but it grew on me later on. Jeff, though, has a valid argument and what does it help me if I save 60% on development time if in the end I lose database speed.
The other thing is, although ORM is good for simple select, update, insert and delete procedures, you cannot totally replace stored procedures where you have multiple updates and inserts and some of these requires some major calculations in them. If I am developing with a complete new database with no stored procedures I might just go the ORM way but where I am just redeveloping with an existing database I think I will lean closer to stored procedures.
Going solely with ORM generated SQL? You lose the ability to properly tune the SQL used to access the database. If all you have are simple queries for SELECT, UPDATE, INSERT, and DELETE then there probably isn't much to tune. Once the queries get moderately to extremely complex, you are going to wish you had more control of the SQL code used.
I should have said: "If I am developing with a complete new database with no stored procedures I might just go the ORM way (with simple queries) but where I am just redeveloping with an existing database I think I will lean closer to stored procedures.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
August 30, 2016 at 12:40 am
The question for me is as to whether there has been much improvement in the ORM tools over the past 4years?
My next question would be as to whether education in the development community has improved to the point where they know how to use them properly.
I've looked at Laravel, Yii and nHibernate. Configured and used CORRECTLY they can address most of the concerns that DBAs have. Separate accounts for different processes, not throwing SELECT * about like a dog's chew toy, data types, metadata, foreign keys, intelligent infex creation, database agnosticism up to a point.
I'm not convinced that people take the time to learn them properly or are receptive to the idea that they will have to evolve their development style to be collaborative, reactive to feedback and solve problems together.
A point of interest for me was when I as a database person mentioned that I was having fun learning an ORM two senior developer friends responded by saying they avoid them as an unnecessary complication because writing a DAL represents a tiny proportion of the project and they have code that writes code anyway
Viewing 15 posts - 61 through 75 (of 142 total)
You must be logged in to reply to this topic. Login to reply