Stored procedures DONOT increase performance(.NET and SQL training video)

Best explanation sir

In general you’ll end up having a much easier time hypertuing a database that’s all stored procedure, especially on complex stuff, there’s no other option that to perform the complex work in the memory of the the actual machine instead of from a remote webserver. As well as having a machine with tools and a language fine tuned to dealing with relational datamodels.

Very use full video sir..

But when parameters changed then......

I given interview and got question on below please explain

Asynchronous programming how achieve in #
Reflection
Extension Method
We pass data from one app to another how we secure the data
How secure Channel in network
Collection can we use in multi thread env
Concrete Dictionary
mediatype
app/json and text/json
what are Azure Services you used
SOLID
Benefits of View
How delete Views work

Video is not clear about the cache for plane sql statement . Lets check by changing the parameters

Nice. I am using linq2sql

nice vidoe

your thought is wrong. if you pass parameter value both sp and normal query it will show different
change parameter and pass parameter:
SP--> cach insert ,catch hit,catch hit,catch hit,catch hit,catch ..........
nomal query --> cach insert,catch hit,cach insert,catch hit,cach insert,catch hit,cach insert,catch hit..

if you keep changing the username and password (text inputs) then there will be multiple plans created for each dynamic sql (since the hash will be different each time...not ideal..will result in plan cache pollution)...better use parameters and sp_executesql or stored procedures...also this way of string concatenation poses a sql injection threat...ORMs typically send out parameterized sp_executesql calls (can be verified by the Profiler) and therefore can re-use plans from the cache (that's why they are faster)...but with anything else it can be misused too :-)

Your title is a bit misleading because, basically the ORM frameworks kill the performance.

Nice video - good effort to describe the things

You just tested, that sp query and inline query are being cached. but you didn't measure all the pipeline performance for each one starting from parsing, compiling, analysing, optimising, etc. also simple query not enough to be a fear measurement, you should figure out many inputs and cases. finally to mention this is just query operation but what about a whole picture of crud processing in a large set of transactions scope. I disagree. sp increase performance.

I know your intention was to explain that there is no difference in performance if same query or proc is shot next time, it will always find plan in plan cache. But while doing so you used wrong examples and explanation. Inline query should be avoided at any point of time from app for many reasons and security being on top in it. In my view when we try to explain certain abstract of a technology in a Demo, we should be very specific about the explanation and examples to support that. Otherwise you would spend another two classes to explain that it was wrong practice OR example.

Sps have a huge performance improvement compared to queries. Any dev can make basic valid test(this video doesn't actually test anything) and calculate the execution time and see a massive difference between them. This video should be deleted for giving junior devs the wrong idea.

The one more advantage I can see in using Stored Procedure is, sql injection will be avoided.

This video helped me a lot, thank you very very much :)

Your activity is definitely worth .. :)

Only simple query plan will be cache, when a complex query, cache most likely be clear within minutes.

Nice video...removing developers misconception on stored procedures...

Interesting video.  I don't agree that inline SQL is just as efficient.  If you are changing the SQL (say from method parameters), SQL Server is going to think it is a different query.  The parameterized SPROC will still read be a cache hit.  Also, the SPROC will not have the startup cost if it has been accessed within a certain amount of time.  Your test app will always have the startup penalty when using inline SQL.

good effort to describe the things

good video to get basics straight !! thanks :)

While you neatly explained that stored procedures aren't more efficient than inline functions (in situations where what you intend to do is even possible from an inline statement... I always thought stored procedures were for things that you can't do inline)... there is one glaring problem with this video, in that the example inline SQL that you wrote is totally vulnerable to SQL Insertion, and should never be used in examples of valid code. Going by the video, the procedure route appears to still be light-years better, not because of performance, but because it uses parameters instead of allowing user-created inputs to go directly into the SQL, leaving the database wide open to hackers.

Then What is the difference b/w stored procedures and inline query ???
G+

Not agreed .. why only cached? Stored Procedure are compiled where as inline queries get compiled every time. So ofcourse performance basic SP's are better 

then what is the difference. why we need to create store procedure????

hello Sir,
the way you have taught us about Stored procedure was great.......and you are absolutely right that inline queries in sql 2005 servers and onwards do the same thing what Stored procedure do(About performance).....but if we do some little bit changes  in the same query that we had previously executed like  we can give some extra space in the same previous query
 for Example "select * from Table_name" this is our previous query and the modified query is "select * from (some extra space i have given their)   Table_name"

and when we execute the modified query so the new  "Execution plan will be generate"  but if we do the same thing in the query stored in stored procedure and then execute the stored procedure then it will not create a  new "Execution plan"....it will take from cached....and then execute the query....i hope you will not mind,i just wanted to clarify the things...& if i am wrong then you are welcome to clear my myth....Thank you :)

nice and cool video.. realy good and helpfull to interview purpose also thank u lot

hey thanks, its helpful 

i have a doubt that does inline query gonna make difference to the network traffic as compared to the stored procedures. If we are using inline query we need pass whole query through the network whereas in stored procedure we just need to pass parameters and procedure name if am not wrong. And disadvantage to using inline query is that whenever we need to make certain changes to query we will have to stop the application and in case of stored procedure we just have to make changes to the database which is quite efficient as per the developer point of view.

Instead of getting into debate how parameters work for inline query try executing an inline query with different parameters and see what profiler shows. Then try executing a SP with different parameters and then see what profiler shows.
I am sure your results will enlighten us all.
According to my results on SQL Profiler "Cache Insert" is called for inline query each time parameters are changed. Whereas for SP "Cache Hit" is called each time parameters are changed except for first time.

nice work.....

Let's hope the user doesn't enter this in the user name field: '; delete from users; --

Tnx for these videos !

its Cool ...!

The same concept is work for Oracle also, or there may be same different mechanism in Oracle.

very cool, definitely didn't know that one.

It really clear my doubt. thanks

thanks for sharing

Good video.

Great video and explanation. Thanks.

very useful information... Thanks Shiv

very good explanation.

Well said, thanks

thanks for info...

all my thanks is all I have to give. thanks millions

I think scenario given by @paulorocha1975 is enough to decline the logic explained in this video. i.e. inline query will insert cache each time it's parameter value is modified whereas SP will always use existing cache regardless of parameter values.

Hmm, how will it behave if you send different combinations of usernames and passwords to the stored procedure and to the inline SQL? Won't you get a different hash value every time you run the inline sql? If one wants to use inline sql would't it be better to just use parametrized queries?