Stored procedures DONOT increase performance(.NET and SQL training video)
HTML-код
- Опубликовано: 6 июл 2012
- For more such videos visit www.questpond.com
For more such videos subscribe ruclips.net/user/questpondvide...
See our other Step by Step video series below :-
Python Tutorial for Beginners:- • Python Tutorial for Be...
Learn Data Science in 1 hour :- tinyurl.com/y5o7qbau
Learn Power BI Step by Step:- tinyurl.com/y6thhkxw
Learn MSBI Step by Step in 32 hours:- goo.gl/TTpFZN
Learn Tableau step by step :- tinyurl.com/kh6ojyo
Learn SQL Server Step by Step tinyurl.com/ja4zmwu
Learn Angular tutorial step by step tinyurl.com/ycd9j895
Learn MVC Core step by step :- tinyurl.com/y9jt3wkv
Learn C# Step by Step goo.gl/FNlqn3
Learn Design Pattern Step by Step goo.gl/eJdn0m
Learn Azure Step by Step :- tinyurl.com/y6fmrech
Learn SharePoint Step by Step in 8 hours:- goo.gl/XQKHeP
When asked in .NET interviews compare Stored procedure VS Inline query the first thing that people answer is Stored procedures increase performance.In this video we will try to clear this misunderstanding that stored procedure increase performance. From SQL server 2005 onwards any SQL which comes to SQL server will be cached. So the performance using stored procedure or in-line queries are both same.
![Sean Rii, Karyon, Sharzkii - Taungule [My Love] (Official Music Video)](http://i.ytimg.com/vi/eJoMuypbSzQ/mqdefault.jpg)
Do not miss our Interview Question video series
30 Important C# Interview Questions : ruclips.net/video/BKynEBPqiIM/видео.html
25 Important ASP.NET Interview Questions : ruclips.net/video/pXmMdmJUC0g/видео.html
25 Angular Interview Questions : ruclips.net/video/-jeoyDJDsSM/видео.html
5 MSBI Interview Questions : ruclips.net/video/5E815aXAwYQ/видео.html
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.
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 :)
I also think, this video doesn't illustrate all the aspects. I agree with your comment. In msdn also, I saw this kind of idea. Actually SP is fast than in-line sql.
all my thanks is all I have to give.
thanks millions
This video helped me a lot, thank you very very much :)
Nice video - good effort to describe the things
It really clear my doubt.
thanks
good video to get basics straight !! thanks :)
nice and cool video.. realy good and helpfull to interview purpose also thank u lot
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.
Great video and explanation. Thanks.
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
good effort to describe the things
Very use full video sir..
very useful information... Thanks Shiv
Thanks to you, you are the best....
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.
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.
The one more advantage I can see in using Stored Procedure is, sql injection will be avoided.
Best explanation sir
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.
very good explanation.
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?
The same concept is work for Oracle also, or there may be same different mechanism in Oracle.
very nice and good explanation
Tnx for these videos !
excellent! you rock! do you have a tutorial about metro style app?
Great info, new subscriber; thanks!!!
Well said, thanks
very cool, definitely didn't know that one.
Your activity is definitely worth .. :)
nice work.....
thanks for info...
Good video.
thanks for sharing
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 :-)
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
its Cool ...!
Nice. I am using linq2sql
Nice video...removing developers misconception on stored procedures...
Only simple query plan will be cache, when a complex query, cache most likely be clear within minutes.
.
nice vidoe
how can I insert cache to stored procedure?
Then What is the difference b/w stored procedures and inline query ???
G+
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.
hey thanks, its helpful
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.
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.
Video is not clear about the cache for plane sql statement . Lets check by changing the parameters
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..
then what is the difference. why we need to create store procedure????
jaimin patel Thanks, clear now.
But when parameters changed then......
it happens automatically.
Your title is a bit misleading because, basically the ORM frameworks kill the 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.
Let's hope the user doesn't enter this in the user name field: '; delete from users; --