### The efficiency of a query command in Relational Algebra

• Query Efficiency

• Fact:

 The disk accessing time can be several milli-seconds long (i.e., to read data on disk takes several milli-seconds) The processing time of queries is mostly disk access times to read the information !!!

• Query optimization:

 Different relational algebra expressions can result in the same result set !!! Query optimization means finding the most efficient relational algebra expression that produces the desired result set

• Efficiency consideration of queries

• Consider the following Relational Algebra expression:

σ (dno = dnumber and dname = 'Research') ( employee × department )

The steps of execution of this relational algebra expression is: (using an example relation for Employee and Department)

Note:

• This Relational Algebra expression σ(dno = dnumber and dname = 'Research') ( Exmployee × Department ) will find:

 The employees in the Research department

(We will study this later - right now, we discuss efficiency of a query)

• Consider an alternative expression:

σ dno = dnumber ( employee × σ dname = 'Research' ( department ) )

The steps of execution of this relational algebra expression is: (using an example relation for Employee and Department)

 Employee: +--------+---------+----------+-----+ | fname | lname | salary | dno | +--------+---------+----------+-----+ | John | Smith | 30000.00 | 5 | | Frankl | Wong | 40000.00 | 5 | | Alicia | Zelaya | 25000.00 | 4 | | Jennif | Wallace | 43000.00 | 4 | | Ramesh | Narayan | 38000.00 | 5 | | Joyce | English | 25000.00 | 5 | | Ahmad | Jabbar | 25000.00 | 4 | | James | Borg | 55000.00 | 1 | +--------+---------+----------+-----+ Department: +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | | Administration | 4 | | Headquarters | 1 | +----------------+---------+ σdname = 'Research'( department ) +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | <----- 1 tuple !!! +----------------+---------+ Employee × σdname = 'Research'( department ) +--------+---------+----------+-----+----------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Research | 5 | | Jennif | Wallace | 43000.00 | 4 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Research | 5 | | James | Borg | 55000.00 | 1 | Research | 5 | +--------+---------+----------+-----+----------+---------+ σdno = dnumber( Exmployee × σdname = 'Research'( department ) ): +--------+---------+----------+-----+----------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | +--------+---------+----------+-----+----------+---------+

Observation:

• We got the same result !!!

• But:

 We "produced" a lot less tuples in the intermediate result !!!

• Without going into the processing details, I'm just telling you that the query (relational algebra expression):

 σ dno = dnumber ( employee × σ dname = 'Research' ( department ) )

is more efficient than:

 σ (dno = dnumber and dname = 'Research') ( employee × department )

• Final notes

• Query optimization is an important topic of Database Systems