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