INF: Representative Sorting/Scanning Rates for SQL Server

ID Number: Q77101

1.10

OS/2

Summary:

Various SQL queries result in nonindexed retrieval and/or a temporary

sort table being built. It is advantageous to know the approximate

additional time required for these operations.

More Information:

The following tests were run with a variety of row lengths and the

results averaged. The results can be considered representative for

typical row lengths.

SORTING RATE

============

386-16-8MB 486-33-16MB

---------- -----------

Time Time

# Rows (sec) # Rows (sec)

------ ----- ------ -----

1000 4.0 1000 2.4

2000 7.5 2000 2.7

5000 25.0 5000 5.0

7000 34.0 7000 7.0

10000 50.0 10000 10.0

15000 75.0 15000 13.0

20000 107.0 20000 17.5

SCANNING RATE

=============

386-16-8MB 486-33-16MB

---------- -----------

800 row/sec. 3100 row/sec.

Notes:

1. Only a single value for scanning rates is given because this figure

is essentially linear over a wide range of row numbers. The

additional tests for sorting rates were run to check for any

tendency toward nonlinear behavior in the sorting algorithm.

2. A difference between the initial and subsequent run times of a

query were seen because of SQL Server caching. This difference is

not extreme, with observed variances of less than or equal to 20

percent. The low value does not mean SQL Server cache is of little

benefit; rather, because of the locality of reference involved in

the queries, a cached set of pages rapidly accumulate even on the

initial execution of a query.

3. For a 16 MB machine, a table on the order of 70,000 rows can fit

entirely within cache.

4. The SQL Server sorting times show a linear curve, which indicates a

good sorting algorithm.

5. SQL Server was essentially CPU-bound for many of the operations in

these tests. This is in accordance with the Microsoft/Sybase

position that most SQL Server operations are CPU bound, and will

therefore benefit linearly from a CPU upgrade. Proof of this can be

seen by comparing the test data for the two hardware platforms in

this study.

6. As a rule of thumb, about 700-1200 rows-per-second sorting speed

can be expected on a 486-33-16MB machine.

Platform Details

----------------

386-16-8MB

Mylex MBE386-16-8

Log & tempdb: CDC 150 MB SCSI drive

Data: CDC 150 MB SCSI drive

Controller: Adaptec AHA1542A

OS: Microsoft OS/2 version 1.21, FAT partition

SQL Server: version 1.1

486-33-16MB

Mylex MAE486-33-16

Log & tempdb: CDC 320 MB SCSI drive

Data: CDC 1.2GB SCSI drive

Controller: Mylex DCE376-8

OS: IBM OS/2 version 1.3, FAT partition

SQL Server: version 1.1

Additional reference words: 1.10 1.30