Record Set Size
The selection speed of different record sets is not linear to the number of rows. Because many steps have to be taken for any selections, getting extra records out of the database often hardly takes any more time. In a typical database, I have about 17 million records in a table. By making a selection of 20,000, 50,000, 100,000, and 150,000 records, I calculated the execution time per record. These are some of the results:
|
Rows |
Rows / Second |
|---|---|
|
20,000 |
476 |
|
51,987 |
456 |
|
20,000 |
377 |
|
51,987 |
702 |
|
50,000 |
704 |
|
133,276 |
1,293 |
|
50,000 |
694 |
|
133,276 |
1,211 |
|
100,000 |
1,369 |
|
282,818 |
2,643 |
|
100,000 |
1,388 |
|
282,818 |
2,525 |
|
150,000 |
2,027 |
|
421,581 |
3,798 |
|
150,000 |
2,027 |
|
421,581 |
3,603 |
|
20,000 |
408 |
|
51,987 |
577 |
|
20,000 |
400 |
|
51,987 |
742 |
|
50,000 |
735 |
|
133,276 |
1,402 |
|
50,000 |
735 |
|
133,276 |
1,373 |
|
100,000 |
1,449 |
|
282,818 |
2,525 |
|
100,000 |
1,470 |
|
282,818 |
2,459 |
|
150,000 |
2173 |
|
421,581 |
4,093 |
|
150,000 |
2,142 |
|
421,581 |
4,053 |
This test indicates that one selection of 100,000 records is about three times as fast as four selections of 20,000 records each. So if possible, get all the information you need in one selection instead of going back to the database many times.