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.
Subscribe and turn on notifications to never miss a weekly video: ruclips.net/user/DataWithBer...
Check out the related blog post for all code samples:
bertwagner.com/2019/10/15/are...
Follow me on Twitter:
/ bertwagner

[@danielheydt9864]

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.

[@robertkorth605]

Of course, it is generally good to think about things when coding. Review techniques are particularly useful when code is migrated.
But your example is a bit lame, because in my opinion the problem is not that the data is processed line by line on the client. I think that the performance boost in your example is not generally due to the use of SUM(), which makes the data loop on the client unnecessary. The advantage is simply that much less data has to be fetched from the server (SQL) and transferred to the client (APP). If the summation is done on the client, then the server (depending on the index coverage) must first push the complete table over the network (protocol) to the client. There the data is received, stored and summed. If the SQL server sums, then only the aggregate is pushed as result over net(protocol) to the client. This is the reason for the efficiency.
There is also the opposite case: The data is already on the client. In this case, it usually (!) makes no sense to first push it to the SQL server and have it calculated there via an SP. But this is also to be weighed from case to case :-)

[@RussianVideoPodcast]

You make, like the best videos!!!

[@markcain2116]

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!

[@bhaskarpati1764]

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.

[@08kunjalchaudhari43]

Hello bhaskar sir can you tell me alternative of store procedure in mongodb

[@zanonilabuschagne7628]

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

[@danielheydt9864]

I like this suggestion. Perhaps you could include the best way to find either unique records or perhaps returning one record from groups having certain identical fields

[@email195]

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!

[@jerichaux9219]

Man you have perfectly encapsulated my precise issues and concerns.
I LOVE writing SP’s with a decent amount of preliminary validation, chocked with THROW statements (never use RAISEERROR because it’s deprecated and doesn’t play nicely with XACT_ABORT), and wrapping anything more complicated than a SELECT inside of a TRANSACTION (deferring to outer transactions because nested transactions are a myth).
Hell, I even have a nice little template that has all of the above, it’s handy and dandy.
I do NOT like that every SP I define feels like it floats away into the amorphous blob of Way-Too-Many™ objects in
[DB]>[Programmability]>[Stored Procedures] which feels far less organized than it should.
I do not like that if it changes you’d better have saved a copy, because Microsoft’s take on it if you haven’t is: “tough shit”.
The expressiveness afforded? Unparalleled vs. using something like the MSSQL npm package or something. The organization and, more importantly, version control?
Feelsbadman :(

[@08kunjalchaudhari43]

Hello Bert can you tell me alternative store procedure in mongodb

[@dovh49]

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.

[@dovh49]

Also, part of the reason I was pushing for in the inline TVFs was because I thought we could do additional filtering and sorting in SQL Server rather than doing a data dump. But since every query would be fairly unique that was probably not a good idea on my part as SQL Server would need to create new plans for each query which would suck.

[@d-mon666]

parameter sniffing, ansi_null its different

[@blender_wiki]

Misleading at best

[@hritikgupta7216]

Worst