Stored Procedure Increase Performance

Posted by Murugavelmsc under Sql Server on 2/27/2013 | Points: 10 | Views : 2315 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How store procedure increase performance.

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

0	@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

0	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

0	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

0	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

0	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

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

Latest Posts