Hi Shiv Sir,
This is one of the best videos from you. Super explanation.
I have one question:
Suppose I have to execute one select from my .Net application then my view is definitely I will not go ahead and wrap that statement around some Procedure. I will keep it inline only. Any views on this?
Long-long time ago they were invented to create some kind of ugly apps on server side (when "clients" were just a simple programs and there was only two layers). Novadays SP is not needed at all, since Application Server solves everything - from abstraction of tables from classes till security. In other words, everything you counted as "benefits of SP". Moreover: throwing away SP you REALLY can have everything in central place: changing in business logic will touch only your C#/Java/Ruby code, no necessary to login to database and jump on procedures/triggers. Moreover: knowing and using one general purpose language you don't need to study sh*t like PL/SQL, T-SQL, etc. - just SQL as an ugly brother to make queries.
Anyway my score is 3 since you gave good hint related perfomance: SP are the same fast as queries, who need all those SP?...
Dear You wrote a good article but in this way I made an experiment which increase performance and reduce load 80 percent mean which procedure was taking 10 sec that return record in 2 sec.
Example of my experiment
Create procedure sp_abc
@v1 varchar(50) ,
Decclare @vuse1 varchar(50)
Decalare @vuse2 varchar(50)
I've been a SQL Server DBA for over 10 years, and a .NET developer for nearly as long. Stored procs do have many performance improvements, especially as you start using newer technologies like LINQ and Entity Framework. Other commenters have already laid out very good reasons and proofs of why sprocs are "usually" faster, so I won't repeat them, here. Suffice it to say, in 90% of real world scenarios, sprocs are faster.
When I first read the title I thought it, surely this depends on the RDBMS (and it does). For example SQL Server does things completely different to the way Oracle does things. However since this only refers to SQL Server I can understand some of your reasoning.
However you haven't actually discussed the reason why I believe stored procedures are quicker.
You didn't look at the parse time taken, or the time it took to send that query to the server. Although the cache is hit, you still have the time it takes to parse the SQL query by the SQL server and sending it to the SQL server.
For example say you have a query that is 100 lines long (which is where your going to actually notice the difference), there is a considerable amount of time that SQL server has to take and verify that there is no syntax errors and compile it to send it to the query optimizer. In a Stored Proc this is only done once where as for inline SQL its done every time you run that statement. Now mr SQL Server may after looking at the SQL say this is the same as a previous SQL statement, and grab the query plan out of the cache. However it still needs to verify that the syntax of the statement is correct.
Some RDBMS systems, like MySQL do a simple text comparison to whats in the cache (for example "SELECT * FROM Customer" is a different query to "Select * From Customer"), so the parser can ignore this step. As this text comparison is generally O(n), there is some a performance effect however we are talking about cycles here not seconds.
Another problem with your logic, is the time it takes to send the SQL statement to the SQL server if it is physically hosted somewhere else. In fact as the Ping time increases you would see a noticeable difference in performance.
Please do this test again without using the * in your query. The * causes behavioral differences from properly written queries and is known to affect performance. Please give more emphasis to the fact that stored procedures have many other advantages and are still generally preferred over ad-hoc SQL statements. People will use this article to argue with managers, and they will be wrong. So, please make a bigger point out of the benefits of stored procedures (security, DRY, etc).
Oh also, another thing I'd like to see you do is to reinstall the database between tests. Since your SQL statements are identical, there's a possibility that one is faster because you ran the other one first. Over time, with repeated queries against the same table, SQL Server gets faster as it generates statistics and creates indexes to improve query plans. If you're hammering on the Users table looking up users all day, SQL Server will be able to optimize ANY attempt to do that, regardless of the source of the query. So, if you ran your inline SQL after running the proc 1000 times, I would expect it to be faster because SQL Server has been able to optimize the table for that specific type of lookup.
These are my suggestions for a more valid test:
1. Remove the * from your queries
2. Get a different row each time (you're always grabbing the same row)
3. Run the tests against different physical databases (so that performing one test doesn't affect the outcome of the other)
4. Return some useful data (such as User ID that was found) and consume it on the client side.
5. Try the other two kinds of operations, updating and deleting.
6. Run two load tests at the same time - one using procs and one using SQL - see which users "get through" more quickly when the server is stressed.
(Your conclusion is probably correct, but I don't think the test actually proves it).