Click here to Skip to main content
11,432,241 members (57,598 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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 3:10am
Comments
Bhushan Shah1988 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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..Smile | :)

regards
anurag
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.150428.2 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100