Click here to Skip to main content
15,071,581 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello Friends..

Please can any one tell me that how can i decrease the Execution Time for the BackEnd Procedure.

Its taking alot of time to display the result. As i used Cursors..
But not sure how to optimize the procedure..


Regards,
Priya
Posted
Comments
Sebastian T Xavier 24-Jul-12 7:42am
   
Do you use any functions in your stored procedure?
priya naidu1 24-Jul-12 8:46am
   
No i did not use any functions, but i used indexing technique. I am not finding it to reduce the execution time but.
Aarti Meswania 24-Jul-12 7:44am
   
you should post your Sql-Query,
if other queries are also slow in execution then you should apply indexing on database
priya naidu1 2-Aug-12 5:37am
   
My other queries are not slow executing. Only for this procedure i even used indexing in database. but somewhere the process is getting struck and the execution is slowed.
StianSandberg 24-Jul-12 7:48am
   
take a look at sql's execution plan. It'l give you some useful tips...

Hi,
To optimize your query, you should take care about the following:
Quote:
Table should have primary key
1. Table should have minimum of one clustered index
2. Table should have appropriate amount of non-clustered index
3. Non-clustered index should be created on columns of table based on query which is running
4. Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
5. Do not to use Views or replace views with original source table
6. Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
7. Remove any adhoc queries and use Stored Procedure instead
8. Check if there is atleast 30% HHD is empty – it improves the performance a bit
9. If possible move the logic of UDF to SP as well
10. Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
11. If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)


For more info follow the links below:
http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[^]
http://msdn.microsoft.com/en-us/library/aa964133%28v=sql.90%29.aspx[^]


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

Views in DB:
MSDN: Create VIEW[^]
Overview of Views in SQL Server 2005[^]


You are using cursors which is not considered good practice, try TableValue functions instead and see.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900