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.

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.

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.

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

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

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.

Answer / bata

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

What does nvl stand for?

0 Answers  

How do you fine the performance tunning?

2 Answers   Accenture,

What is a trigger and its types?

0 Answers  

What is spid in sql server profiler?

0 Answers  

Which is better in performance - CONSTRAINT or TRIGGER over a column which restricts say an input of particular value in a column of a table?

3 Answers   Accenture,

One table Test with single column. These are the values in the table a b c d e f g h I need a query (without using any variable) with output as - a b c d e f g h

1 Answers  

How do I schedule a sql server profiler trace?

0 Answers  

What is the meaning of resultset type_scroll_insensitive?

0 Answers  

Can you tell me some of the common replication dmv's and their use? : sql server replication

0 Answers  

What area unit the various kinds of info compression introduced in sql server 2008?

0 Answers  

Where does the copy job runs in the log shipping primary or secondary? : sql server database administration

0 Answers  

What is a transaction and what are ACID properties?

14 Answers   Config Systems, IBM, TCS, Virtusa,