Below is a description on how I ran my speed test.
I created a small DB called TESTMU containing 1 table Invt containing 2
integer columns Invt1 & Invt2. I populated the table with 1000 rows
containing integer values for both Invt1 & Invt2 ranging from 1 to 1000. I
created a (single-column) index on Invt1.
I then ran the following CMD file, with several variations, and recorded the
resulting time.
DISCO
CLS
DIALOG 'Set MULTI ON?' vr vk YES AT 15
IF vr = 'YES' THEN
SET MULTI ON
ELSE
SET MULTI OFF
ENDI
CONN TESTMU
CLS
SET V vTR = (ISTAT('TotalReads'))
SET TIME FOR 'HH:MM:SS.SS'
SET V VSTIME TIME = .#TIME
SET V vNum INT = 1
LABEL AGAIN
*( Statements below referred to as 1st, 2nd, 3rd & 4th respectively)
SEL Invt1 INTO vInvt1 FROM Invt WHERE Invt1 = .vNum
-- SET V vInvt1 = Invt1 IN Invt WHERE Invt1 = .vNum
-- SEL Invt1 INTO vInvt1 FROM Invt WHERE Invt2 = .vNum
-- SET V vInvt1 = Invt1 IN Invt WHERE Invt2 = .vNum
WRI .vInvt1=5 AT 20 05 WHITE ON RED
SET V vNum = (.vNum+1)
IF vNum <= 1000 THEN
GOTO AGAIN
ENDI
SET V VETIME TIME = .#TIME
SET V vTimeDiff REAL = ((.VETIME-.VSTIME)/1000)
WRI 'Elapsed time =',.vTimeDiff,'seconds' AT 10 10 WHITE ON RED
SET V vTR = (ISTAT('TotalReads'))
WRI 'Total Reads =',.vTR AT 15 10
SET TIME FOR 'HH:MM AP'
--------------------------------------------------------------------------
I ran this leaving only one of the four statements after "LABEL AGAIN" not
remed out.
I used this to test for any difference between reading a value using
"SET..." vs "SEL..." as well as using an indexed column vs a non-indexed
column in the WHERE clause.
You can also rem out the "WRI .vInvt1=5 AT 20 05 WHITE ON RED", if desired,
or make any other changes you want to make/test. I also ran this using a
WHILE loop instead of the "IF...THEN...GOTO..." loop.
This was run on a 100mb network with the server running WIN2000, 512MB
memory 533MH pentium. One workstation was running WIN98 with 196MB memory &
Celeron 500 processor and the other was running WIN2000 with 196MB memory &
400MH Pentium2. The TESTMU database is about 128K.
I ran this both with the database on a workstation and with the database on
the server.
I also ran it with 1 and 2 users on the network when multi was set on.
============================================================================
=============
RESULTS
=======
-1st statement- -2nd statement- -3rd statement- -4th
statement-
WHERE MULTI Users Reads Time,sec Reads Time,sec Reads Time,sec Reads
Time,sec
===== ===== ===== ===== ======== ===== ======== ===== ======== =====
========
Local ON 1 4071 1.98 4035 2.41 6999 26.37 3634
8.85
Local OFF 1 2037 1.27 2037 1.26 2004 24.89 2002
7.69
Network ON 1 4071 6.92 4035 7.99 6999 35.82 3634
15.43
Network ON 2 4071 6.97 4035 8.73 6999 35.81 3634
16.25
Network OFF 1 2037 2.14 2037 2.14 2004 25.54 2002
8.41
----------------------------------------------------------------------------
--------------
The time taken to run this test on the server, with MULTI ON was
significantly longer than with MULTI OFF and/or running on a local
workstation (with either multi setting). Also, note the results, under
different conditions, for SEL...(#1 & #3) vs SET...(#2 & #4)
The time for 1 or 2 users on the network are essentially the same. The
following changes were made to the Win2000 server prior to running these
tests. Refer to Microsoft articles Q296264 & Q126026 and ACSL article
dealing with RFCB caching & Opportunistic locking.
Registry changes (only qualified personnel should do this):
HKey_Local_Machine
System
Current Control Set
Services
LanmanServer
Parameters
EnableOpLockForceClose REG_DWORD set to 1
EnableOpLocks REG_DWORD set to 0
CachedOpenLimit REG_DWORD set to 1
(You may need to Add these parameters, if they do not currently exist)
Prior to making the above changes, I observed a substantial time difference
between 1 and 2 users on the network, but I do not have any timed results
for comparison purposes. The above changes seem to have fixed other
problems relating to I/O problems, hesitation/lock-up and a problem with the
server failing to release files used by Win98 workstations, but "speed" is
still an issue.
----------------------------------------------------------------------------
--------------
Frank Radice
[EMAIL PROTECTED]
