Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server

Please anyone tell me how stored procedure improve the performance

Posted 12-Jan-11 0:42am

ravindrapatil1987

Updated 12-Jan-11 0:46am

Estys

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Estys · Answer 1 · 2011-01-12T00:46:00

Solution 1

It's compiled once. The query doesn't have to be sent to the server, only it's parameters

Cheers

Posted 12-Jan-11 0:46am

Estys

Vipin Kumar Mallaya G · Answer 2 · 2011-01-12T06:42:00

Dear Friend
Some reasons for preferring stored procedure are

Easy maintainability- Stored procedures are easy to maintain and can be directly deployed without recompilation of code
Increased Performance- When grouping a set of operations in to a single procedure will increase the performance by reducing the number of round trips to the database server
eg - One Insertion and modification (both are depended each other) using procedure will get completed in a single tick to server ; if not implemented the same using procedure, in that case for executing each process application have to go to server and execute the statement
Data Integrity Is Maintained - Consider the above situation if insertion is causing error then without moving to updation the operation will be terminated by raising an error if the operations are implemented using procedure else if insertion is causing error then it may try with updation which is meaning less as insertion caused error

Hope you got an idea and will continue to use stored procedure for further application development

Happy Programming

Vipin Kumar Mallaya

#realJSOP · Answer 3 · 2011-01-12T07:11:00

Solution 3

0) The stored procedure doesn't have to be re-written if you refactor the code to a different language, leading to ease of maintenance, and even a certain amount of portability.

1) If you need to, you can use the database from additional platforms by simply writing the front end.

2) You can add "SQL server" to your resume...

Posted 12-Jan-11 7:11am

#realJSOP

Comments

Sandeep Mewara 13-Jan-11 4:01am

I like the bullet point 2! Very few might had thought of it! :)

wizardzz 13-Jan-11 12:12pm

Would a possible 3) be:

Frontend developers don't need as much query syntax knowledge or knowledge regarding the actual column names and/or relationships between tables. It also prevents developers from using "Select *" statements, which are typically bad practice.