4 ms·

This is the old Microsoft Misinformation. Stored Procedures are not faster. SQLBooksOnline actually has this information clear as day, but MS Evangelists like

by ssmoot 15y 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 15y ago
[deleted]
- ssmoot 15y 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 15y ago
    that would be: exec sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id=1
    - ssmoot 15y ago
      Thanks for the syntax help. It's been awhile. ;-) For anyone curious, here's an old article on the interface I'm talking about: http://www.techrepublic.com/article/shorten-development-time-by-using-parameterized-queries-in-adonet/6093390 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?
deleted 15y ago
[deleted]
nl 15y ago
Ah.. but stored procs are faster if you are executing business logic in them, because you can avoid round trips. 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

by ssmoot 15y ago

deleted 15y ago

[deleted]

ssmoot 15y 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 15y ago
  that would be: exec sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id=1
  - ssmoot 15y ago
    Thanks for the syntax help. It's been awhile. ;-) For anyone curious, here's an old article on the interface I'm talking about: http://www.techrepublic.com/article/shorten-development-time-by-using-parameterized-queries-in-adonet/6093390 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?

deleted 15y ago

[deleted]

nl 15y ago

Ah.. but stored procs are faster if you are executing business logic in them, because you can avoid round trips. 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