> I'm reliably informed that there's no way to cook squirrels so they are appetizing to anyone who isn't desperate
Misinformed. Squirrels are pretty tasty. Need a few, though.
More to the point of the article, I appreciate that Fowler calls out stored procedures as something often misused but secretly pretty awesome. If you are working in an environment where you can handle a SQL database as a canonical datastore--and of course there are literally kajillions of opportunities for that--then sprocs can do a lot to isolate business logic at a level lower than the application. I wouldn't use them in every case, but I've had good success using them in many cases.
Unfortunately, most database abstraction libraries and ORMs have the effect of reducing available database capabilities to the lowest-common denominator.
For example, look at how many ORMs have facilities to add commonly-required conditions to every query (e.g., "is_active = true"), but lack the ability to use views.
Use better ones. =) I'm a big fan of Sequel, for when I'm in an environment where I can deal with having models schema expressed via introspection of the database itself.
Well the fact that the "n+1 query problem" is a fatal mistake ORMs force you to worry about, whereas you never had to worry about it with plain SQL, is pretty damning in and of itself.
This is the first time I've heard of the N+1 query problem. I went and looked into my code and found 2 places where I had applied it, and have now measurably improved the performance of those pages. (Alas, if only I had enough traffic for it to make a real difference.)
You really learn something new every day if you're observant. Thanks!
That's not entirely true; I actually ran into a problem today where we've got a (thankfully minor) performance issue due to a classic n+1 query problem involving an old non-ORM persistence layer with hand written SQL queries.
There's nothing about plain SQL that means you "don't need" to worry about the n+1 queries, although I suppose the author of our authentication class apparently thought that way, because while we have a way of getting all objects from the DB, and we have a way of checking the DB to see if the user has permission to view an object, we don't have a way of getting all the objects a user can view.
But at least all our queries use delightfully artisanal hand-crafted SQL! :)
I think the better lesson is that if you're writing code that touches the DB, you need to be aware of what your code (and whatever libraries or frameworks you're using) are actually doing to the DB, with a vague idea of how DBs work. The real crime of ORMs is they hint (or sometimes outright promise) that the DB is being abstracted away and you no longer need to worry about indexes or joins or constraints, or even whether you're data is being persisted to a RDBMS or a NoSQL datastore. Literally: I ran across one ORM recently that used as a major selling point that you could use MySQL or MongoDB interchangeably with no changes in your app code and I just shuddered.
So yeah, I'd say that the issue with ORMs isn't that they force you to worry about the n+1 query problem; it's that they encourage you not to worry about it. Everyone should be worried about it! :)
What if I was to tell you that the "n+1 query problem" only happens when you misuse ORMs? I know, "no true scotsman" and all that :)
Ok... what if I was to tell you that you can get SQL injection with stored procedures? I worked for a company where we had that problem. I hadn't written them, but I knew that was impossible so I bet my boss that's not the reason.
And then I looked at them and discovered they were doing something like
s = "SELECT * FROM table WHERE id =" + @id
and then
exec_sql s
(I don't remember the exact syntax, but it was something like that.)
Misuse of what a stored procedure is supposed to do? Yeah, so's the "n+1 query problem" with ORMs... Though I will grant you that the second one is much easier to get wrong, the guy who wrote the SP had to work to make it vulnerable to injection...
The problem there is not understanding the abstraction. That's a general problem of most abstraction layers, and the solution is not avoiding abstraction but using a good one and learning how it works.
Of course I realise lots of people will happily continue to use horrible ORMs and not learn how they work, but that's not a ORM specific problem.
i s'p it's properly a database toolkit w/ an ORM component, written in such a way that the ORM can be refactored out once the database structure begins to stabilize.
... also (and this is _totally_ a crucial point), as someone else commented, squirrels are not terrible. probably better than McDonald's, depending on how they're cooked.
Knowing that this was something written by Martin Fowler, I knew it wouldn't be about this particular Carrot [0] but hopefully someone disrupts this area.
I suppose the problem is that many (most?) people use stored procedures without source control?
Stored procedures are often created and modified manually, directly in place, maybe by some external team/consultant, with total opacity about what SPs exist on which databases and with what code, when they changed etc.
There is of course no need for things to be this way.
Years ago, I worked on two projects in two diff companies, and both mandated stored procs be used as a default data access method. In one case, sprocs were the only thing allowed - your credentials had no access to anything except whatever stored procs the DBA had granted you.
In both situations, the stored procedures were written by the DBA, and the DBA only. And they simply added them to the databases you needed, and then granted access. In one case, you did have 'permission' to write ad-hoc SQL, but it all had to be reviewed by the DBA.
And that was the big crux of the problem - the DBA. In both cases, they simply wrote sprocs and applied them to the databases. That was it. No version control, no testing, no application review, no sitting in on regular project meetings to understand the full problem. You'd have to describe to them what needed to be done, and they'd draft up something, put it on the server, and give you access.
So yes, these were not problems inherent to stored procedures. They were definitely a cultural problem. But those have been my exposures to "sprocs first" thinking (or "sprocs only" thinking). On most of my other projects, I use an ORM with schema definitions that are versioned, that are used to generate tables/indexes/migrations/etc, and then some queries are done by hand (usually complex reporting queries), and in some cases, stored procedures are used - maybe 90-95% queries run are ORM-generated, 3-5% are by-hand, and occasionally sprocs on an as-needed basis (slight perf boost, or logic reusable by multiple external applications are the two most common reasons).
And when I write the sprocs, they get version controlled and added to a migration process so they can be recreated in the build process as needed. By far the biggest issue I see with sprocs not being in version control is that it's DBAs writing/forcing the sprocs, and they're "too busy" for version control (or don't have the same tools, or don't understand it, or whatever other excuse is given on any day).
You haven't lived until you had to change a 1200-line stored procedure that has at least one 8-way JOIN in it. I've worked in two different companies that had something like that.
The tastiness is in the preparation. Boiling all the nutrients and flavor out of a vegetable, and then discarding the cooking liquid, is the height of stupidity.
In this case, steam carrots.
(Apologies for not adding anything to the actual discussion!)
Yes! I hate most boiled vegetables. Makes potatoes seem slimy and flavorless. Funny thing is I actually love eating carrots raw. They have a lot of flavor uncooked.
(might as well keep the off topic in the same thread)
That's not fair. Most of what we eat today is not very tasty. Most large scale farming has chosen to overweight the genetic qualities of larger yields and hardier structures that are more suitable to shipping.
In the past, regional dishes were a result of not just culture but the taste of foods grown locally. Things following "old" recipes with today's food will never capture the flavor. The foods are just that different.
That all depends on where you get the raw foods. Ripe tomatoes just picked from the vine are an entirely different experience than ones that are picked hard and green, shipped hundreds of miles, and then allowed to turn red before they are stocked on the grocery store shelf. Add to that the fact that grocery stores generally only stock varieties of produce that ship well and look nice, rather than ones that are grown specifically for flavor. The same applies to virtually all produce, in my experience.
Disclosure: I have a back yard garden and fruit trees.
The problem in my experience is that people will emotionally defend their style of carrot boiling, to the point that they loudly and forcefully attempt to convince me that carrots come out of the ground pre-boiled, or that if I try to cook the carrot another way it would still come out equally mushy.
The problem here, as always, is people. Pointing this phenomenon out to someone causes cognitive dissonance (e.g. "Rails doesn't suck, so I must suck?") which is often harder to deal with than eating boiled carrots.
> Several friends of mine commented how stored procedures were a disaster because they weren't kept in version control
There are pre-made tools to do this, and its relatively easy to author your own extracting of the DDL from all DB objects into a tree and commit them into revision control.
There is little difference between SQL and other languages in this context.
I came here to post a link to Ron Jeffries' "we tried baseball" article [1] but that's already linked at the bottom of OP. Since it's a good read, but slightly tucked away, here it is anyway.
34 comments
[ 3.7 ms ] story [ 88.8 ms ] threadMisinformed. Squirrels are pretty tasty. Need a few, though.
More to the point of the article, I appreciate that Fowler calls out stored procedures as something often misused but secretly pretty awesome. If you are working in an environment where you can handle a SQL database as a canonical datastore--and of course there are literally kajillions of opportunities for that--then sprocs can do a lot to isolate business logic at a level lower than the application. I wouldn't use them in every case, but I've had good success using them in many cases.
For example, look at how many ORMs have facilities to add commonly-required conditions to every query (e.g., "is_active = true"), but lack the ability to use views.
Almost every time I've encountered an ORM it's been an over-boiled carrot. I think I've just had bad luck, though.
You really learn something new every day if you're observant. Thanks!
There's nothing about plain SQL that means you "don't need" to worry about the n+1 queries, although I suppose the author of our authentication class apparently thought that way, because while we have a way of getting all objects from the DB, and we have a way of checking the DB to see if the user has permission to view an object, we don't have a way of getting all the objects a user can view.
But at least all our queries use delightfully artisanal hand-crafted SQL! :)
I think the better lesson is that if you're writing code that touches the DB, you need to be aware of what your code (and whatever libraries or frameworks you're using) are actually doing to the DB, with a vague idea of how DBs work. The real crime of ORMs is they hint (or sometimes outright promise) that the DB is being abstracted away and you no longer need to worry about indexes or joins or constraints, or even whether you're data is being persisted to a RDBMS or a NoSQL datastore. Literally: I ran across one ORM recently that used as a major selling point that you could use MySQL or MongoDB interchangeably with no changes in your app code and I just shuddered.
So yeah, I'd say that the issue with ORMs isn't that they force you to worry about the n+1 query problem; it's that they encourage you not to worry about it. Everyone should be worried about it! :)
Ok... what if I was to tell you that you can get SQL injection with stored procedures? I worked for a company where we had that problem. I hadn't written them, but I knew that was impossible so I bet my boss that's not the reason.
And then I looked at them and discovered they were doing something like
s = "SELECT * FROM table WHERE id =" + @id
and then
exec_sql s
(I don't remember the exact syntax, but it was something like that.)
Misuse of what a stored procedure is supposed to do? Yeah, so's the "n+1 query problem" with ORMs... Though I will grant you that the second one is much easier to get wrong, the guy who wrote the SP had to work to make it vulnerable to injection...
Of course I realise lots of people will happily continue to use horrible ORMs and not learn how they work, but that's not a ORM specific problem.
Not just your bad luck. Personally, at this point, I consider ORMs an anti-pattern.
i s'p it's properly a database toolkit w/ an ORM component, written in such a way that the ORM can be refactored out once the database structure begins to stabilize.
... also (and this is _totally_ a crucial point), as someone else commented, squirrels are not terrible. probably better than McDonald's, depending on how they're cooked.
[0] http://www.introducingcarrot.com/
What exactly is the problem? :)
Stored procedures are often created and modified manually, directly in place, maybe by some external team/consultant, with total opacity about what SPs exist on which databases and with what code, when they changed etc.
There is of course no need for things to be this way.
This puts them on more-or-less the same level as shell scripts to me. They're fine if they're tiny, in source control, and unsurprising.
http://sqitch.org/
In both situations, the stored procedures were written by the DBA, and the DBA only. And they simply added them to the databases you needed, and then granted access. In one case, you did have 'permission' to write ad-hoc SQL, but it all had to be reviewed by the DBA.
And that was the big crux of the problem - the DBA. In both cases, they simply wrote sprocs and applied them to the databases. That was it. No version control, no testing, no application review, no sitting in on regular project meetings to understand the full problem. You'd have to describe to them what needed to be done, and they'd draft up something, put it on the server, and give you access.
So yes, these were not problems inherent to stored procedures. They were definitely a cultural problem. But those have been my exposures to "sprocs first" thinking (or "sprocs only" thinking). On most of my other projects, I use an ORM with schema definitions that are versioned, that are used to generate tables/indexes/migrations/etc, and then some queries are done by hand (usually complex reporting queries), and in some cases, stored procedures are used - maybe 90-95% queries run are ORM-generated, 3-5% are by-hand, and occasionally sprocs on an as-needed basis (slight perf boost, or logic reusable by multiple external applications are the two most common reasons).
And when I write the sprocs, they get version controlled and added to a migration process so they can be recreated in the build process as needed. By far the biggest issue I see with sprocs not being in version control is that it's DBAs writing/forcing the sprocs, and they're "too busy" for version control (or don't have the same tools, or don't understand it, or whatever other excuse is given on any day).
Seriously, why?
In this case, steam carrots.
(Apologies for not adding anything to the actual discussion!)
(might as well keep the off topic in the same thread)
In the past, regional dishes were a result of not just culture but the taste of foods grown locally. Things following "old" recipes with today's food will never capture the flavor. The foods are just that different.
I woulder why people were content with foods that are not tasty for prolonged period of time.
Disclosure: I have a back yard garden and fruit trees.
1) People had terrible teeth
2) Cooking devices were unreliable
3) Houses were cold so it was difficult to keep things warm without them continuing to cook & soften
4) People were accustomed to institutionalised cooking (boarding school, army, servants)
5) Bad practice was passed on through "Cultural acquisition"
The problem here, as always, is people. Pointing this phenomenon out to someone causes cognitive dissonance (e.g. "Rails doesn't suck, so I must suck?") which is often harder to deal with than eating boiled carrots.
There are pre-made tools to do this, and its relatively easy to author your own extracting of the DDL from all DB objects into a tree and commit them into revision control.
There is little difference between SQL and other languages in this context.
[1] http://ronjeffries.com/xprog/articles/jatbaseball/