Stored Procedure Increase Performance

Posted by Murugavelmsc under Sql Server on 2/27/2013 | Points: 10 | Views : 1319 | Status : [Member] | Replies : 8
How store procedure increase performance.

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Kundan64 on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,
When we create SP, server Compile it and store it on server, But when we use Query then its compiled and execute. If we run same query again and again then it will compile every time, But in the Case of SP, it will Compile once (when its created), SP runs every time without Completion. So its Increase the performance of DB Server.
SP Also increase the performance of Network, as its reside on the server, so no need travel the query over Network.

Thanks
Kundan

Mark as answer if it help you.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Kundan,

It valid for older versions of SQL Server, but from 2005 onwards, all SQL statements, irrespective of it's a SQL coming from inline code or stored procedure or from anywhere else, they are compiled and cached.

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Kundan,

I am agree with Network Traffic.

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/27/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
@Murugavelmsc

Are you saying all the codes are Compiled & Cached everytime ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
http://www.codeproject.com/Articles/414272/Stored-Procedures-DO-NOT-increase-performance

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Arul44ece on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
Hi ,

Please refer the below link.

It may be help about your query.
http://www.sqlservercentral.com/articles/Performance+Tuning/performancetuningstoredprocedures/2505/

Regards,

Arul R
arul44.ece@gmail.com

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kundan64 on: 2/27/2013 [Member] Starter | Points: 25

Up
0
Down
hi,Murugavel,
You are right - "It valid for older versions of SQL Server, but from 2005 onwards, all SQL statements, irrespective of it's a SQL coming from inline code or stored procedure or from anywhere else, they are compiled and cached."

Thanks
Kundan

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

Up
0
Down


STORED PROCEDURE IS COLLECTION OF SQL SERVER STATEMENTS.TO INCREASE THE PERFORMANCE FOR TABLE LEVEL ONLY.

SOME STEPS TO INCREASE THE PERFORMANCE BOOSTING:

USER JOINS
AVOID CURSOR
AVOID SELECT * FROM TO USE APPROPRIATE COLUMNS
USE VIEW
FOLLOW NORMALIZATION PROCESS.
USE PARTITIONING TABLE


HOPE THIS WILL HELP....

sriram

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response