Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to speed up SQL stored-procedures performance?

user-image
Question added by Jehangir Wahid , Lead Software Developer , Inaequo Solutions
Date Posted: 2016/02/27
Ehab Hassan
by Ehab Hassan , IT Manager , Pharmazone Co

you can use (set nocount on ) in the store procedures .. it will help a lot to increase your speed

and also ... put the min. memory per query in the sitting.

 

 

swarajpal saini
by swarajpal saini , Subject Matter Expert (SME SQL) IT Architect , IBM

Try below steps

 

Check the execution plan , validate the cost of Execution plan , if any of the Process costing more , try to re-right the stored procedure , or wright simple code with same business logic and try to reduce the cost of that certain process.

1) Use SET NOCOUNT ON inside ALL your Stored Procedures

2)Don’t Name any of Your Stored Procedures with ‘sp_’ at the start

3)Adjust the Index Fillfactor setting

4) Adjust the Min Memory Per Query setting

Jinoj John Mathew
by Jinoj John Mathew , IT Director , The Bridge Company

Configure Index Fillfactor setting

Configure Memory Per Query setting

Usage of SET NOCOUNT ON on your Stored Procedures

 

Exclude the naming of stored procedures with ‘sp_’ at the beginning

Jawad Siddiqui
by Jawad Siddiqui , Principal Software Engineer , Nisum Private Limited

1. Lower your number of joins.

2. Only use most relevant joins 

3. Check Execution Plan it will describe everything to you. 

 

Still getting issue please share your query i will let you know.

 

Thanks and regards

Deleted user
by Deleted user

Use SET NOCOUNT ON inside ALL your Stored Procedures

Don’t Name any of Your Stored Procedures with ‘sp_’ at the start

When joining tables using inner joins, you always want to join the smallest table first.

With a large procedure, it may be likely you also have the same query being executed multiple times. If this is the case, then instead of performing the same query multiple times, dump the results into a temp table and reference the temp table in the rest of your queries.

Limit the Select list

Returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.

Use the Least Amount of Tables to Compile Your Select List

 

An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the select list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.

You must optimize the stored procedure by reducing the individual query execution time. Try to replace all in, not in clauses by joins. Remove loops from the Stored procedure and replace them with functions and use goto statements to call those functions. Try to eliminate all udf's from the stored procedure and instead do manual query writing as possible.

eldhose john
by eldhose john , .net developer , Quest Innovative Solutions Pvt. Ltd

creating a stored procedure to run in a database other than the Master database, don’t use the prefix “sp_” in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly

If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is “dbo”. Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don’t name any of your stored procedures with the prefix “sp_”

Venkatesh V
by Venkatesh V , Associate Software Developer , Wondersoft Pte Ltd.

You can start by adding SET NOCOUNT ON; This should speed it up quite a bit.

imran Kasuri
by imran Kasuri , Teacher , jps

Use SET NOCOUNT ON inside ALL your Stored Procedures

Deleted user
by Deleted user

  • Use SET NOCOUNT ON inside ALL your Stored Procedures
  • Adjust the Min Memory Per Query setting
  • Adjust the Index Fillfactor setting

mohamed eltigani
by mohamed eltigani , Senior Software Developer/Developemnet Lead , Kenana Sugar Company

First we need to check the estimated exedcution plan.

More Questions Like This