Optimizing the Query

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

, +

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 24-Jul-12 1:38am

priya naidu1

Add a Solution

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

2 solutions

Solution 1

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.

Posted 24-Jul-12 2:03am

Sandeep Mewara

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)

_Amy · Accepted Answer · 2012-07-24T02:11:00

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