Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


What are the factors you will check for the performane
optimization for a database query?

Answers were Sorted based on User's Feedback



What are the factors you will check for the performane optimization for a database query?..

Answer / sravan kumar vemuri

The most important option to increase the query performance
is to create index on the table.
First one should concerntrate on the columns used in the
where cluase.

But indexes can hit the performance for insert queries. So
before giving an insert query, better to remove the index
and then to recreate the index.

Is This Answer Correct ?    6 Yes 0 No

What are the factors you will check for the performane optimization for a database query?..

Answer / ponguru

It is very big thing...Let me explain in short..Run the
profiler and catch the Logical reads/Physical reads/CUP
values.

find out where values are more(huge) and take Excution plan
and study it ...here you can find out where the problem.

In general scenarios Data also matters that means If your
Dev/Test enviroment not having that much Data which has
Prod.

Get the large Data and do above scenarios and you came know
what is cause.

Is This Answer Correct ?    1 Yes 0 No

What are the factors you will check for the performane optimization for a database query?..

Answer / samba shiva reddy . m

1.In the select statement give which ever the columns you need
don't give select * from
2.Do not use count()function
3.use indexes and drop indexes regularly it B tree structure we are not going to drop means it will take time for creating indexes it self.
4.when joining the tables use the id's(integer type)not string
5.Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
6.Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
7.Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
8.Try to use UNION ALL in place of UNION.
9.Avoid the 'like' and notlike in where clause.
10.Use non-column expression on one side of the query because it will be processed earlier.
11.To store large binary objects, first place them in the file system and add the file path in the database.

Is This Answer Correct ?    1 Yes 0 No

What are the factors you will check for the performane optimization for a database query?..

Answer / khalid moin

try to use function based indexes and use it when needed..
it will not be used unnecessorily.

use clustering concept while creating table it will make you
join operation fast if more join operation u r going to
perform on the tables

Is This Answer Correct ?    1 Yes 0 No

What are the factors you will check for the performane optimization for a database query?..

Answer / murtaza

first we will check the columns which r there in table.
then the frequency of usage of each columns.Index should be
created on those columns which has maximum usage or columns
which can be use frequently.
so to check which columns should have the index and how we
can optimize the query can be done by
1.SQL Profiler
2.By Normalization

Is This Answer Correct ?    1 Yes 1 No

What are the factors you will check for the performane optimization for a database query?..

Answer / pushpendra kumar

The important factors for performance optimization are as
follows:
1. Proper data type assignment;
2. Selection of proper data reader;
3. Proper command to retrieve the data from the database.

Is This Answer Correct ?    2 Yes 3 No

What are the factors you will check for the performane optimization for a database query?..

Answer / bata

i think above 2 answers arent much betr.wil try to give
perfect answer soon

Is This Answer Correct ?    0 Yes 1 No

Post New Answer



More SQL Server Interview Questions

What is the difference between DTS and SSIS?

3 Answers   Allianz,


What are the benefits of using stored procedures over sql statements?

3 Answers   Ness Technologies,


How to transfer an existing table from one schema to another schema in ms sql server?

0 Answers  


What is the difference between seek predicate and predicate?

0 Answers  


What is the impact on other user sessions when creating indexes?

0 Answers  






how do we find every fifth record in a table

14 Answers   Aspire, HCL,


Tell me what do you mean by an execution plan? Why is it used? How would you view it?

0 Answers  


Can a table be moved to different filegroup?

0 Answers  


How to update existing rows in a table?

1 Answers  


Explain about builtinadministrator?

0 Answers  


What are the differences between decimal and float in ms sql server?

0 Answers  


What is pessimistic concurrency?

0 Answers  






Categories