Interested to Buy Any Domain ? << Click Here >> for more details...
What is one of the first things you would do to increase
performance of a query? For example, a boss tells you that
?a query that ran yesterday took 30 seconds, but today it
takes 6 minutes?
- 2 Answers
- 16132 Views
- Accenture, Merrill Lynch, Wipro, I also Faced
- E-Mail Answers
Answer Posted / bhaskar
Can u check the folliwing points are used in the procedure
or a Query.
Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table
based on query which is running
Following priority order should be followed when any index
is created a) WHERE clause, b) JOIN clause, c) ORDER BY
clause, d) SELECT clause
Do not to use Views or replace views with original source
table
Triggers should not be used if possible, incorporate the
logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty - it improves
the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only
necessary in code
Remove any unnecessary joins from table
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)
There are few hardware upgrades can be considered as well
like separating index on different disk drive or moving
tempdb to another drive. However, I am not suggesting them
here as they are not quick way to improve the performance
of query.
| Is This Answer Correct ? | 16 Yes | 2 No |
Post New Answer View All Answers
Can I save my report as html, excel or word? : sql server management studio
what is database replicaion? What are the different types of replication you can set up in sql server? : Sql server database administration
Why is there a performance difference between two similar queries where one uses union and the other uses union all?
What are three ways you can use an identity value inside a trigger? Why would you prefer one way over another?
What are constraints?
What are the operating modes in which database mirroring runs?
What are the 7 disadvantages to a manual system?
What is de-normalization and what are some of the examples of it?
How do you migrate data from mssql server to azure?
What is sql or structured query language?
Explain the use of keyword with encryption. Create a store procedure with encryption?
How much is a sql server license?
Explain external key management in sql server 2008
What is the difference between a unique key and primary key?
How to create an multi-statement table-valued function?
