I recently got asked do you know what a stored procedure is. Erm, it’s a SQL query that’s instantly executed and is therefore faster than adhoc queries. Yes, but why is it faster… Ooo, errr, most likely cached, coz someone once told me it was… not a very good answer at all.
Turns out they are fast for a number of reasons, the best one I found was validation. Other benefits include reduced network traffic and can help against SQL injection.
http://stackoverflow.com/questions/208976/are-stored-procedures-faster-for-simple-queries
Advertisement
March 29, 2010 at 1:57 pm
I thought the main reason was that stored-procedures were compiled (validated and planned). The network traffic reduction is that you only send a reference to the stored-procedure with the parameters instead of the individual SQL statements.
Interestingly, the two years I worked at Infospace/Motricity, I did not write even a single line of SQL. In the next job, I wrote all manners of T-SQL that I did not even know about. And, currently, everything’s done through NHibernate (not sure whether I like it, though).
Eddy.
March 29, 2010 at 7:02 pm
Yep, I guess it’s the actual meaning of “compiled” I’m looking for. I thought it was stored in memory, I couldn’t actually think of the what was really compiled like JAVA for example.
“Precompiled: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.” – http://www.codeproject.com/KB/database/hkstoredproc.aspx