how to minimize the process time of stored procedure

Question

0.00/5 (No votes)

See more:

SQL

server

Pls some one tell that how to minimize the process time or execution time of stored procedure.
pls help me

Posted 14-Feb-13 2:10am

chander_rani

Add a Solution

Comments

Bhushan Shah1988 14-Feb-13 8:23am

it depends on your sql query which is used in stored procedure.
you need to optimize query to minimize process time.

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)

Anurag Sinha V · Answer 1 · 2013-02-14T02:25:00

Hi,

There must be some queries in your stored procedure performing some inserts,selects,updates etc.
Check whether the strategic columns(columns upon which the above operations are taking place) are involved in some indexes or not.
If not, then create indexes involving these columns, it will help in minimizing the execution time of ur sp.
Next you can view the actual execution plan of ur queries in ur sp by clicking 'Ctrl+M', and then u can analyse where you need attention.
Google for some SQL tuning stuffs, and you can get help from them too.
Also, if ur sp has manier temporary tables, try to eliminate them by some combination and stuffs.
It too will help.
have a look at the below link too.

SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]

Happy coding..:)

regards
anurag

Sandeep Mewara · Answer 2 · 2013-02-14T02:29:00

Use Query analyzer & SQL Profiler of SQL Server to find the query cost and performance. See where the bottle neck is and try to fine tune it.

References:
Query Analyzer, find at location:
- Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio for Query Analyzer.
- Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler for profiler.

If needed, define Views in DB and use it:
MSDN: Create VIEW[^]
Overview of Views in SQL Server 2005[^]

Mr. Mahesh Patel · Answer 3 · 2013-02-14T02:46:00

Hi Dear,

I am listing some points to be keep in mind when generate Stored Procedure.

1. Use schema name with object name (Sql can search your object easily and fast and directly)
2. Include SET NOCOUNT ON statement (avoid additional messages like "2 rows effected")
3. Use If exists (SELECT top 1) instead of (SELECT *) (short of length of data)
4. Try to avoid using cursors if possible
5. Do not use the prefix of “sp_” in the stored procedure name(Because of System SP Pattern)
6. Use the sp_executesql stored procedure instead of the EXECUTE statement
7. Use Try-Catch for handling errors
8. Transaction should be as small as possible.

how to minimize the process time of stored procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0