7 ms·

Remember 10 years ago when this was a solved problem? There were only two rules: "always use stored procedures" and "never build dynamic SQL". Doing stuff on

Remember 10 years ago when this was a solved problem? There were only two rules: "always use stored procedures" and "never build dynamic SQL". Doing stuff on relational databases was fast. 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.

billybob 16y ago
I haven't used LINQ, but I do use Rails and its ORM. Using Rails' ORM to generate SQL is a case of optimizing for development time rather than execution time; assuming I would write faster SQL by hand, which is questionable, it would still be premature optimization and would be harder to maintain. 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.
MichaelGG 16y ago
They chose to write their query in C# instead of SQL. They could have kept LINQ-to-SQL and manually written the query (even made it a SP, for the slight gain that'd provide) and then used LINQ's mapping features to get objects out of it. (I believe the DataContext has a Translate feature that'll take any data reader and pop out objects.) I do this from time to time when the LINQ-to-SQL query has a bug or isn't doing exactly what I want, or when there's a bulk operation I can do much more efficiently. It's not exactly advanced LINQ-to-SQL...
- brunomlopes 16y ago
  Actually, if you look at other posts from Sam mentioning dapper you'll find that they profiled the site and found some bottlenecks also on the translator from the datareader to objects on linq2sql. Since they're going to hand-write the sql then they're going to take advantage of that really fast microorm instead of sticking with l2q.
HelloBeautiful 16y ago
Remember 200 years ago when industrial pollution was a solved problem?
- gjm11 16y ago
  It wasn't. It's just that it consisted of wood and coal smoke and horseshit rather than CO2, weird organic chemicals, and so forth.
pfedor 16y ago
Actually, I do remember 10 years ago, that was when I got my first job. I remember that already then I was rather surprised to learn that what they said in the books about database applications was exactly backwards: the often repeated wisdom was that the database engines were fast and highly optimized, and you should do as much processing as possible on the database engine and as little as possible in your PHP script which was bound to be less efficient. The reality was that the database engine was always the bottleneck. If the PHP script was slow, you could replicate your Apache to as many machines as needed. If the MySQL was crapping out, you could buy a bigger machine or optimize your queries but generally that was a bigger pain.
- silverbax88 16y ago
  If your database engine is the bottleneck, you don't have an enterprise architecture. 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.
  - btilly 16y ago
    If your database engine is the bottleneck, you don't have an enterprise architecture. 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.
    - jacques_chester 16y ago
      I think the fundamental mismatch is not between OO and relational, or the application layer and the relational layer, but between reads and writes. 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.
drtse4 16y ago
Checking the SQL that your ORM engine produce if you deploy your app in an environment where performance matters has always been a good practice. It's true now as it was 6-7 year ago when modern ORM solution like hibernate (or similar things on .Net) started to appear.
MartinCron 16y ago
I remember 10 years ago, and I'm sure as hell not going back. 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.
- eftpotrm 16y ago
  Yes, bad table designs and poor practice will make your code slow regardless. 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....
- jasonkester 16y ago
  Why is any of that hard? Stored procedures and database schema changes are all written in SQL, which goes into source control nicely. Schema changes are easy in an environment like that. Parallel development on multiple dev databases rolling changes into a single staging server is easy in an environment like that. Just check in your code and let the automated build do it's thing. 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.
  - MartinCron 16y ago
    I'm not laboring under a misconception. I know how to do all of those things, I was doing them, it's just easier not to. 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.
rayvega 16y ago
>> "...Had they not bothered introducing LINQ in the first place, they'd have had their 100x performance boost from the get go..." 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.
- eftpotrm 16y ago
  But.... every single CRUD is normally not a huge amount of code and relatively simple to generate from the information schema if you know a bit of SQL Server. Do it that way and you've got your sprocs just sitting there for later use, primarily OK to be used straight out of the box but available for modification as soon as you need something different.
  - MartinCron 16y ago
    You say that like having CRUD sprocs sitting around is a good thing.
    - noblethrasher 16y ago
      Just curious, why isn't it?
      - MartinCron 16y ago
        All code, auto-generated or not, is a sort of liability in that you have to maintain it. You have to re-generate it each time you make changes, and there's a possibility that someone can mess with it and cause it to break. 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.
ssmoot 16y ago
This is the old Microsoft Misinformation. Stored Procedures are not faster. 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.
- deleted 16y ago
  [deleted]
  - ssmoot 16y ago
    That's a non-sensical statement. Stored Procedures are always parameterized. It's the only way to pass in arguments. 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-comparison-sp_executesql-vs-executeexec/ 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.
pradocchia 16y ago
This is simply not true. Stored procedures and dynamic SQL are completely orthogonal to the problems of N+1 select and index seek vs table scan. 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/ http://code.google.com/p/dapper-dot-net/)

billybob 16y ago

I haven't used LINQ, but I do use Rails and its ORM. Using Rails' ORM to generate SQL is a case of optimizing for development time rather than execution time; assuming I would write faster SQL by hand, which is questionable, it would still be premature optimization and would be harder to maintain. 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.

MichaelGG 16y ago

They chose to write their query in C# instead of SQL. They could have kept LINQ-to-SQL and manually written the query (even made it a SP, for the slight gain that'd provide) and then used LINQ's mapping features to get objects out of it. (I believe the DataContext has a Translate feature that'll take any data reader and pop out objects.) I do this from time to time when the LINQ-to-SQL query has a bug or isn't doing exactly what I want, or when there's a bulk operation I can do much more efficiently. It's not exactly advanced LINQ-to-SQL...

brunomlopes 16y ago
Actually, if you look at other posts from Sam mentioning dapper you'll find that they profiled the site and found some bottlenecks also on the translator from the datareader to objects on linq2sql. Since they're going to hand-write the sql then they're going to take advantage of that really fast microorm instead of sticking with l2q.

HelloBeautiful 16y ago

Remember 200 years ago when industrial pollution was a solved problem?

gjm11 16y ago
It wasn't. It's just that it consisted of wood and coal smoke and horseshit rather than CO2, weird organic chemicals, and so forth.

pfedor 16y ago

Actually, I do remember 10 years ago, that was when I got my first job. I remember that already then I was rather surprised to learn that what they said in the books about database applications was exactly backwards: the often repeated wisdom was that the database engines were fast and highly optimized, and you should do as much processing as possible on the database engine and as little as possible in your PHP script which was bound to be less efficient. The reality was that the database engine was always the bottleneck. If the PHP script was slow, you could replicate your Apache to as many machines as needed. If the MySQL was crapping out, you could buy a bigger machine or optimize your queries but generally that was a bigger pain.

silverbax88 16y ago
If your database engine is the bottleneck, you don't have an enterprise architecture. 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.
- btilly 16y ago
  If your database engine is the bottleneck, you don't have an enterprise architecture. 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.
  - jacques_chester 16y ago
    I think the fundamental mismatch is not between OO and relational, or the application layer and the relational layer, but between reads and writes. 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.

drtse4 16y ago

Checking the SQL that your ORM engine produce if you deploy your app in an environment where performance matters has always been a good practice. It's true now as it was 6-7 year ago when modern ORM solution like hibernate (or similar things on .Net) started to appear.

MartinCron 16y ago

I remember 10 years ago, and I'm sure as hell not going back. 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.

eftpotrm 16y ago
Yes, bad table designs and poor practice will make your code slow regardless. 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....
jasonkester 16y ago
Why is any of that hard? Stored procedures and database schema changes are all written in SQL, which goes into source control nicely. Schema changes are easy in an environment like that. Parallel development on multiple dev databases rolling changes into a single staging server is easy in an environment like that. Just check in your code and let the automated build do it's thing. 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.
- MartinCron 16y ago
  I'm not laboring under a misconception. I know how to do all of those things, I was doing them, it's just easier not to. 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.

rayvega 16y ago

>> "...Had they not bothered introducing LINQ in the first place, they'd have had their 100x performance boost from the get go..." 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.

eftpotrm 16y ago
But.... every single CRUD is normally not a huge amount of code and relatively simple to generate from the information schema if you know a bit of SQL Server. Do it that way and you've got your sprocs just sitting there for later use, primarily OK to be used straight out of the box but available for modification as soon as you need something different.
- MartinCron 16y ago
  You say that like having CRUD sprocs sitting around is a good thing.
  - noblethrasher 16y ago
    Just curious, why isn't it?
    - MartinCron 16y ago
      All code, auto-generated or not, is a sort of liability in that you have to maintain it. You have to re-generate it each time you make changes, and there's a possibility that someone can mess with it and cause it to break. 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.

ssmoot 16y ago

This is the old Microsoft Misinformation. Stored Procedures are not faster. 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.

deleted 16y ago
[deleted]
- ssmoot 16y ago
  That's a non-sensical statement. Stored Procedures are always parameterized. It's the only way to pass in arguments. 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-comparison-sp_executesql-vs-executeexec/ 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.

pradocchia 16y ago

This is simply not true. Stored procedures and dynamic SQL are completely orthogonal to the problems of N+1 select and index seek vs table scan. 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/ http://code.google.com/p/dapper-dot-net/)