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


Can any one tell me how to increase the performance of a
sql query ie what are the performance tips in creating or
writing a sql query !!?

Answers were Sorted based on User's Feedback



Can any one tell me how to increase the performance of a sql query ie what are the performance tip..

Answer / manoj

1.
Use proper sequence of tables in from clause
i.e.
the table have more common column must in last
in form clause bcoz on firing of query engine link
last table in form clause
2.
Use "Exist" clause instead of "Not in"
Bcoz it return only true/false(boolean)

3.
Use "Hint" if Required

Is This Answer Correct ?    6 Yes 0 No

Can any one tell me how to increase the performance of a sql query ie what are the performance tip..

Answer / sathishrpillai

Hii..
for optimization of sql query
1.don't use select * from,choose particular fields itself
2.don't use both OR ,AND in same query
3.Minimise the use of aggreagate functions such sum,count
4.don't link unneccessary tables

Is This Answer Correct ?    5 Yes 2 No

Can any one tell me how to increase the performance of a sql query ie what are the performance tip..

Answer / hutashan

use Inline view to eleminate large number of rows
Avoid implicit datatype conversion
Avoid using function on an indexed column
Avoid comparing column value with NULL
Outer Join
Use OR carefully

Do not use outer join unless absolutely necessary. Degree
of optimizing outer join permutation is VERY LIMITED
Instead: Consider using default values in the base table to
avoid outer join
Do not outer join to a view. This typically results in a
non-mergable view execution plan
Always use NOT EXISTS instead of NOT IN


Remove DISTINCT keyword from SELECT if UNION is used

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL PLSQL Interview Questions

What are predefined functions in sql?

0 Answers  


What are schema-level triggers?

0 Answers  


1. what is the exact use of hint in sql. 2. How we can avoid index scan in sql even though index is there in the table.

1 Answers   Polaris,


Why do we use sql constraints? Which constraints we can use while creating database in sql?

0 Answers  


How do you use join?

0 Answers  






What is bulk collections?

2 Answers  


How do I save a sql query?

0 Answers  


Q1.all the depts which has more then 10 empls? Q2.all the dept which does not have any emply? Q3 all the emp which does not have any dept? Q4 get all the emply detais with the dept details it dept is exit otherwise any emp details? Q5 how to debugg the dynamic sql and packages?

2 Answers  


What is the best strategy to use COMMIT in a pl/sql loop?

3 Answers  


Are sql database names case sensitive?

0 Answers  


What is the maximum number of rows in sql table?

0 Answers  


i have a table eno dno sal 1 10 200 2 10 150 3 10 100 4 20 75 5 20 100 i want to get sal which is less than the avg sal of thri dept. eno dno sal 2 10 150 3 10 100 4 20 75

11 Answers   IBM,


Categories