Monday, 6 October 2014

Different measures for calculating query cost

Measures of Query Cost / Different measures used in calculating the query cost / What are the different measures that are to be considered which calculating the query cost? / Query cost evaluation measures

Measures of Query Cost

In DBMS, the cost involved in executing a query can be measured by considering the number of different resources that are listed below;
  • The number of disk accesses / the number of disk block transfers / the size of the table
  • Time taken by CPU for executing the query

The time taken by CPU is negligible in most systems when compared with the number of disk accesses.

If we consider the number of block transfers as the main component in calculating the cost of a query, it would include more sub-components. Those are;
Rotational latency – time taken to bring and spin the required data under the read-write head of the disk.
Seek time – time taken to position the read-write head over the required track or cylinder.
Sequential I/O – reading data that are stored in contiguous blocks of the disk
Random I/O – reading data that are stored in different blocks that are not contiguous.

That is, the blocks might be stored in different tracks, or different cylinders, etc.
Whether read/write? – read takes less time, write takes more.

From these sub-components, we would list the components of a more accurate measure as follows;
  • The number of seek operations performed
  • The number of block read
  • The number of blocks written
To get the final result, these numbers to be multiplied by the average time required to complete the task. Hence, it can be written as follows;

Query cost = (number of seek operations X average seek time) +

(number of blocks read X average transfer time for reading a block) +

(number of blocks written X average transfer time for writing a block)

Note: here, CPU cost and few other costs like cost of writing the final result are omitted.

No comments:

Post a Comment

ALTER TABLE statement in SQL

ALTER TABLE statement in SQL  ALTER TABLE statement ALTER TABLE is a Data Definition Language (DDL) statement that alters the ...