Stored Procedure vs Adhoc Query Performance

Will you get faster performance by putting your SQL queries in a stored procedure or executing them directly? In this week's video we look at the (mostly) straightforward answer.

Check out the related blog post for all code samples:

Follow me on Twitter:

[bhaskarpati]

Using stored procedures instead of direct DML statements will reduce burden on the application team in following cases.
1) Adding extra filter conditions
.
2) Adding or changing order by clause
.
3) Simplifying existing SQL query or modifying SQL query for better performance.
4) Adding extra conditions before inserting or updating records.

Above are few activities that can be done by a DB resource without application code changes.

[danielheydt]

Bert, thanks for all of the great information about SQL. Your videos are awesome and your ability to describe complex information in understandable terms is brilliant. Please keep it coming. What I have found is that when moving SQL from C# code to stored procedures it often forces or encourages the developer to follow better practices and that is why there is often a performance boost. For example, I recently came across some code that queried a large amount of data and read it line by line only to perform some fairly basic aggregation. When I moved the code to an SP I, of course, used the built-in SQL SUM() and so the query was much more efficient. Over time I think this just created the perception that stored procs are faster.

[markcain]

Would you do a segment on SQL functions? When to used them vs Stored Procedure. I read lots of articles about never using SQL functions--they're evil. Thanks!

[zanonilabuschagne]

Well played, thanks Bert. Next one can be "which is slower - Distinct or Group by" :-)... Keep it up *thumbs up*

[email]

This is my dilemma. As a developer and DBA I am back and forth with whether to write queries in my app vs. creating stored procs. From a DBA standpoint stored procs are the way to go because it allows me to alter tables and know exactly what is affected and make the necessary sp changes without breaking anything. With the queries in the apps I am flying blind as a DBA, but as a developer it’s really nice for the sake of source control and switching between branches. It’s good to know that performance doesn’t take a hit either way though. Great video!

[dovh]

Yes, it would be interesting to know the differences between a stored proc and inline table-valued functions. At work I was pushing for the using of inline TVFs because they are more flexible (reusable) and are immutable. On our personal machines the inline TVF was actually faster than a stored proc - not sure why - but in production the inline TVF was significantly slower. I'm not sure how they were being tested as someone else did the testing so maybe the SQL Servers were set up differently or maybe the tests were flawed, I don't know.

It is nice to know that sending a query over the wire and stored procs offer no performance difference.

[d-mon]

parameter sniffing, ansi_null its different

[kunjalchaudhari]

Hello Bert can you tell me alternative store procedure in mongodb