Thursday, January 20, 2011

When to use Stored Procedures?

Hello,

Before some days, I talked about system calls. While including a file to the script is a system call, connecting to database server is also a system call. So obviously it will make the site slow.

There is a popular belief that SPs are helpful in getting faster database performance. But very few know why. Most people believe that it is because the code is pre-compiled. This is not true. The fact that you can pass variables to a SP, it has to be dynamic and cannot be pre-compiled. So. is SP really faster as they say? The answer is Yes and No. A SP is fast is it is used for appropriate reason. Otherwise there is no difference. So what is the right use of SP?

As I said in the beginning, all the database calls are system calls. So the site becomes slow when you fire more and more queries on the database. In most occasions this is inevitable. But in some cases there is an alternative. Sometimes when you are doing database operations, you fire a query to get some result. Now based on this result you have to fire another query to get some more result. Also, this process may not be possible joins. So in this case, you would be firing two system calls. So instead of firing two queries, just create a stored procedure that would fetch the first value from database, do the necessarily operation, fire the next query and give you the result as the output. Thus you make only one system call. The fact that the stored procedure is the part of the database engine, it would not make extra system call when it is firing a query. So here you go, you get two query worth output with only one system call. so it is now faster!

Hope I made sense here.

No comments:

Post a Comment