SQLBooksOnline actually has this information clear as day, but MS Evangelists like Rob Howard (went on to create Teligent) would go around spouting the "sprocs are faster!" line and no one would ever think to point out the discrepancy. It just became religion for the masses of MS platform Developers.
ADO.NET (and whatever it's called these days) executes all Parameterized queries through (see if I can get the casing right) the sp_execsql procedure. This means that the Query Plan for every query run on ADO.NET is cached. IIRC the only additional step Stored Procedures avoided was Parsing. Execution for either would come up with the same, cached plans, but sprocs wouldn't have to parse each time.
Who's willing to bet that the SQL Parser, with decades of optimization work, can parse 100% of your queries in a few hundred nano-seconds?
In practice then, that savings doesn't matter. In side-by-side tests I did a few years ago (that anyone with a few minutes can replicate), there was effectively no performance difference between the two. So the parsing savings were swallowed up as noise in the actual network-latency/query-execution formula.
At the end of the day then, sprocs are not the solution. The solution is to avoid dynamic SQL, and use Parameterized queries. Generated queries that aren't Parameterized do not cache their query plans (last I knew, but it's been years, so maybe that's changed) and won't perform as well as sprocs.
The problem isn't LINQ then. If you know how the gears underneath it all fit together, you can easily avoid sprocs with no negative performance repercussions.
I think it's a terrible idea, but I've seen multiple places where there is a very blurred boundary between stored-proc-for-data-access and stored-proc-for-data-access-after-a-bit-of-business-processing
What I'm talking about is a special stored-procedure called sp_executesql. It takes varargs for parameters and executes your query.
Open up Query Analyzer. Run SELECT * FROM users WHERE id = 1;
That's an ad-hoc, not parameterized query.
Now run: sp_executesql 'SELECT * FROM users WHERE id = @id', @id=1
(My MSSQL may be a bit rusty :p, so syntax issues aside)
That's a parameterized query. That's what runs when you use the classic ADO.NET .addParameter interface.
Queries executed that way have their query plans cached.
I spent a couple seconds on Google and came up with this. Looks like a decent overview: http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comp...
If you can actually measure a significant difference between sp_executesql and a stored-procedure call that's news to me. Definitely not my experience, and not what the actual documentation indicates. I'd go so far as to bet that unless you came up with some kind of pathological example you're mis-remembering or misunderstanding what I'm trying to say.
To keep it simple: If you use the .AddParameter interface you should see no appreciable performance advantage to Stored Procedures. If you simply interpolate your parameters (or your O/RM does it for you), then you will pay the price for generating the execution plan each time. Probably not a significant different for 100ms queries, but it could be quite a substantial cost for a <10ms query.
exec sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id=1
For anyone curious, here's an old article on the interface I'm talking about: http://www.techrepublic.com/article/shorten-development-time... Just so you don't think you have to memorize the above syntax.
I'm not sure what's used these days, but I assume it's fairly similar if not simplified a bit. If it's not broke right?
Versioning of SPs was hard. Code control for SPs was hard. Automated testing of SPs was hard. Incremental deployment (e.g. rolling out the new code to half of the servers in the web farm) was hard. Parallel development against a shared dev/staging server was hard. Trivial schema updates were hard.
And even then, it was never guaranteed to be fast. If the underlying tables weren't indexed, or your stored procedures did stupid shit like use cursors or temporary tables, it was still slow.
Nothing has changed in the last 10 years to make it harder. Granted, many shops weren't doing it well back then, thus the misconception that versioning SQL databases is hard, and that schema changes are hard.
That stuff was only ever hard because people declared it to be so, and built crazy bureaucratic organizational limitations to stop developers from changing the database (because that's hard and therefore not something that developers should be doing.) Once you start thinking of it as easy and build your tools around that fact, you'll notice that it actually is easy.
The example that comes to mind is making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. As a result, incremental rollouts become impossible, so you wind up doing high-risk huge-scale deployments or doing things like creating procedures named EntityInsertV2 and EntityInsertV3 or adding optional parameters with defaults or ignoring parameters that the procedure no longer needs but older clients still want to provide.
And then there's cleanup. As you really should remove EntityInsertV1 once all of the clients are using EntityInsertV2, but you can't have that in the same Update SQL, so there's really two deployments for every deployment.
If there's an easy fix for this problem (other than keeping procedural logic out of the persistence layer) I never found it.
Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue.
If you have thick clients accessing SQL functions directly, you have a bigger problem (client-server... ugh) that browsers have solved.
If you have multiple servers talking to each other, you still have a version of the "thick client" problem, namely enterprise integration... for which there are intermediary APIs.
Source control isn't the issue (of course everything is also in source control, this isn't the dark ages).
I'm talking about a pretty common scenario where you have one SQL server and 4 web servers acting as clients. Let's say you want to do a zero-downtime rolling deployment and upgrade the web servers one at a time while the other 3 continue to run. This is a real issue that I encountered on multiple projects that were adhering to the "everything must be in sprocs" dogma. Moving to ORMs and parameterized queries saves a lot of headaches.
s/stored procedure/function
... and notice that you're simply describing a common issue with building anything. If you change the interface to something, you need to change the stuff that calls it. That's just life, and it's the same story whether you have databases in the mix or not.By keeping the procedural stuff in the procedural layer and not in the persistence layer, it's easier (for me, anyway, your experience may be different) to keep things encapsulated and make contained changes in contained places.
Still though, while I don't dispute that SPs (frankly, databases in general) have issues with version control and deployment management, there are still options for managing that sort of issue that don't require the blunt instrument of the ORM tool generating its own set of dependencies and inappropriate SQL for the job, leading to the sort of performance issues that the article talks about....
Except that when StackOverflow started it was a small startup with just three developers and a small user base. They probably made the decision early on to tradeoff any premature performance optimization with the ability to quickly iterate and ship the product by using Linq-2-Sql instead of hand-writing every single CRUD operation.
Now that their business (i.e. web traffic) has grown exponentially and have a larger group of developers they can now go back and optimize and performance tune the code including doing complete re-writes as necessary.
This is an important takeaway for anyone building a startup.
I think there was an implicit assumption that everything Microsoft does is great, and Microsoft would never lie to you. Oh, and the next version will be even betterer! Honest!
Basically the fix boils down to replacing a slow ORM with a faster ORM, and using indexes. Well of course indexes are faster - Duh!
HOWEVER this is where if I was them, I would start to get worried, because every SQL Server horror story I've been told basically starts with "And then the indexes became randomly corrupted" and goes rapidly downhill from there.
That ORM can generate update statements that only update changed fields... can your proc do that or does it blindly update all fields? That ORM effortlessly maps data from the db into objects so you don't have to pass record-sets around; I don't want record-sets and data readers littered all over my programs, I want business objects.
Programs that keep all SQL in procs are horrible to work on and a bear to maintain, I'll take an ORM any day over a mess like that.
My take on auto-generated code is this: If code is so simple/deterministic/boilerplate that it can be auto-generated, then the generation of that code should be completely automated (at runtime, if possible) and the generated code should be invisible and untouchable.
I've never had a pleasant experience tweaking auto-generated code.
Depending on your application architecture, you're also increasing the chances of your needing multiple round-trips to the database to query various bits of information for building your query, increasing the load on the DBMS (and possibly the network, depending on your architecture) and increasing the query latency.
Properly auto-generated code should be the cleanest, best laid out, most consistent code you work on, quite possibly well commented as well. It's obviously not a panacaea that'll work for everything and I'm not saying ORM tools never have their benefits, but certainly I'm not convinced that third-party ORM tools are right for what I'm doing at present.
And, of course, I don't know anything about what you're doing at present, so I won't even try to speak to that. But I can't imagine a weaker argument against ORM than the first one you made, though. When you're doing data-intensive work, are you really worried about the CPU overhead of string concatenation on your application layer?
:)
I should stress in this though, I'm not a complete 'it must be a compiled sproc stored in the DBMS' guy; I have worked with systems before which effectively provided their own stored procedure implementation within the application source code because the underlying DMBS didn't support anything else; it's not ideal for a number of reasons but it does work, is an order of magnitude superior to ad-hoc SQL scattered throughout the app, still permits some of the benefits of genuine sprocs but also still provides the dev with proper control over what they're doing.
My take would be: use a great ORM to develop quickly and maintain easily, but be aware of common pitfalls, set up sensible indices, and have some performance monitoring in place. Tune by hand if absolutely necessary.
MySQL could barely do joins ten years ago. Today, it's still behind where Oracle was ten years ago. You can throw extremely complicated queries at Oracle and it generally optimizes them well. Don't think of MySQL as "fast and highly optimized". It's only fast for relatively simple queries.
When I say complicated queries, I'm talking about 50+ line queries with many joins, subqueries, aggregations, etc. You can run these against tables with millions of rows and still get an answer in seconds, often regardless of indexes.
Just yesterday I rewrote a view that used a database link to act as a materialized view (which makes me feel icky). It ran approximately 10,000 times faster. For a 100-row result that only joins 4 modest tables, there shouldn't be that kind of a difference IMO.
This will sound incredibly mean, but I built a very lucrative career coming in behind people who tried to fix everything in their apps and not the DB.
What's the busiest website that you have experience with? Do you have any experience with scaling to, say, serve more than a million dynamic pages per hour to consumers?
I ask because your opinion seems extremely uninformed when it comes to high performance websites. If you've designed your application right in a traditional CRUD application, everything except the database layer scales fairly easily. The game then is about finding ways to help that remaining bottleneck. Standard techniques include optimizing queries, caching data intelligently, directing queries to read-only slaves, and in extreme cases sharding data across databases. Several of these result in moving more logic into the app, not less.
In the database world this conflict has been studied for decades under the headings of OLAP and OLTP. All you get taught in a university course or a beginner's book is OLTP-focused. Great if you're storing thousands of credit card transactions per second. Less useful if you need to read thousands of records per second.
Lots of NoSQL looks like it's actually dimensional modelling minus some of the really clever bits.
However, I have found it is sometimes necessary to denormalize my schema and store entities based on how they are presented, not necessarily the idealistic OO ORM'ed way.
At the end of the day, the user doesn't care if your code is abstracted properly (who's definition?) or enough. It just needs to work speedily and correctly and be easily maintainable.
I think alot of people know SQL, but may not understand how to model entities correctly, and if they can't model the entities correctly, what ever they denormalize is still coming from a 'less-good' model.
And that's exactly what I was talking about: a key purpose for DM is to make the database more accessible for fast bulk queries at the cost of duplicate data.
Whereas normalisation drives out duplication in order to maximise transactional performance and space efficiency.
In your case, you start with the OLTP/normalised model, then devolve to what is actually a kind of OLAP/denormalised model.
Write-oriented vs read-oriented.
Maybe the missing link for relational databases in a web world is actually some kind of real-time ETL?
In fact, many ORMs will happily use CRUD sprocs to save and retrieve complex entities, which compounds the N+1 select problem by passing all columns, every time. Compared to that, raw-SQL mappers like LINQ to SQL are a positive step forward, and leave the door open for more intelligent mappers like Dapper (as mentioned in the article: http://code.google.com/p/dapper-dot-net/)
Granted, you often had to actually write Stored Procedures by hand back then. But then if you look at the LINQ in the article, you'll notice that it's pretty much exactly the SQL you'd stick into your stored procedure, just backwards. Had they not bothered introducing LINQ in the first place, they'd have had their 100x performance boost from the get go.
Naturally, SP's are not some magic 100x'ing cure-all. They're just a non-generated version of your SQL, which means that you're guaranteed never to have your ORM go nuts and build some monstrosity like the one outlined in the article.
You still need to tune your SQL by hand, but at least you can tune the SQL rather than some not-particularly-helpful abstraction on top of it.