In this lesson, you will learn how the physical movement of your storage hardware dictates query speed and why organizing data to favor sequential reads is a performance superpower.
The Mechanics of Access Patterns
To understand performance, think of your database storage like a library.
- Sequential Access is like reading a book from start to finish. You open the cover and glide through the pages in order. Your eyes (or the disk head) don’t have to jump around; you just keep moving forward.
- Random Access is like looking for specific words in a book by jumping to a random page, checking for the word, then jumping to another completely different chapter.
In a database, "Random Access" involves a high cost: the physical disk head must physically move or the storage controller must seek different, non-contiguous blocks. This "seek time" is orders of magnitude slower than simply reading the next adjacent block of data already in the buffer pool.
Why Locality Matters
When you perform a range scan—such as selecting all orders for a specific customer—the database engine prefers to read data that is stored physically together. If your data is fragmented (as we discussed in our study of Page Fragmentation), the database is forced to perform random I/O, hopping back and forth across your storage media to collect the requested rows.
Consider this query:
SELECT * FROM Orders WHERE CustomerID = 500 ORDER BY OrderDate;
If your table is a Heap Table with no specific physical ordering, the database might have to jump to ten different disk locations to find these orders. However, if you use a Clustered Structure keyed by CustomerID, all of Customer 500's orders are stored in contiguous pages. The database reads the first page, realizes the next page is right there, and performs a lightning-fast sequential stream of reads. By keeping related data together, you turn a series of expensive "seeks" into a single, high-speed "scan."
Key Takeaway: Database performance thrives on sequential I/O; by designing your storage structures to keep related data physically adjacent, you minimize costly seek operations and maximize throughput.