. Rules Followed for Query Tuning 1

 

==================================================================

1. 'or' should be written with a union

for e.g. instead of writing where a=1 or b=3

write

where a=1 union b=3

==================================================================

2. relational operator replace with between

for e.g. instead of writing where a>2 and b<5

write

where a between 2 and 5

==================================================================

3. There shouldn't be any formulae along with the attributes in where clause

for e.g. where 2*a=40 this should be removed wherever found. 

==================================================================

4. Avoid using Join if it is not necessary

for e.g. suppose SID attribute in table Sailors is a primary

key that refers to another attribute SID in table Reserved

so the query is

–

select S.SID from Sailors S, Reserved R where S.SID=R.SID

instead of writing this the query

should be changed to

select R.SID from Reserve R; 

==================================================================

5. Avoid the keyword DISTINCT if it is not necessary for

e.g.

select distinct ssnum from Employee where dept = 'Information Technology'

here there is no need of writing

DISTINCT keyword because ssnum is itself a primary key.

 

==================================================================

6.  Avoid using same nested query 

for e.g. there are 2 tables Employee and Techdept

and the query is

Select ssnum from Employee where dept in (Select dept from Techdept);

instead of writing this we can write

select ssnum from Employee, Techdept where Employee.dept = Techdept.dept;

==================================================================

7. Avoid using temporary relations if not necessary but where there is necessity there it should be used like-

a query for finding all information department employees

who earn more than $40000 is written as

Select * into Temp from Employee where Salary > 40000;

then the next query is

Select ssnum from Temp where Temp.dept = 'Information Systems';

so there is no need for using aTemp

here and it can be changed to

Select ssnum from Employee where Employee.dept = 'Information System' and Salary>40000;

==================================================================

8. If a query is long then break it into parts for e.g.

select * from T1 T2 where <condition1> group by <attribute> having <condition2>....

this should be written as

 where select * from T1 T2 where <condition2> 11

it will automatically group and show no need of writing group by.