Question

A table has 1,000,000 (million) rows. Each row is 500 bytes.

- The data transfer speed of the disk is 400 MB/SEC

- The seek time is on average 0.001 seconds

- The index fan out is 200

- The secondary index is not a clustering index.

How many seconds would it take to read a range of records using the index if 100,000 (one-hundred thousand, or 10% of all records) records qualify?

Answer 1

Data in one row/record = 500 bytes.

Therefore, total data in 100,000 rows/records = (500 x 100,000) = 50,000,000 bytes.

Lets convert these bytes to KB = (50,000,000 / 1024) = 48,828.125 KB

Now, lets convert these KBs to MB = (48828.125 / 1024) = 47.68371582 MB

Now, data transfer speed is 400 MB/SEC hence time required for transfer of 47.68371582 MB will be = (47.68371582 / 400) = 0.11920929 seconds

1.) Amount of time to transfer data = 0.11920929 seconds

Now, lets calculate index fan out time for 100,000 records/rows.

The Index fans out every 200 records/rows. Therefore the number of times index will fan out to read 100,000 records/rows will be = (100,000 / 200) =  500 fan outs.

After every fan out the new index seek time is 0.001 seconds.

Hence, for 500 fan outs time taken will be = (500 x 0.001) = 0.5 seconds.

2.) Amount of time due to index Fan out will be = 0.5 seconds.

3.) Total time taken to read a range of 100,000 records/rows under given conditions will be = (Amount of time to transfer data + Amount of time due to index Fan) = (0.11920929 + 0.5) = 0.61920929 seconds