The tests were performed using a local SQL 7.0 database. The database used had one user table created. This table was populated with 10,000 rows using the following SQL query:

DECLARE @LOOP int
SELECT @LOOP = 0

SET NOCOUNT ON

WHILE @LOOP < 10000
BEGIN
	insert into tblStresstest(Name,Age,SSN,IsMale)
	SELECT 'Test Name',@LOOP/1000,@LOOP,1

	SELECT @LOOP = @LOOP + 1
END

SET NOCOUNT OFF

There was an unclusted primary key constraint on the ID field; the SSN field had a UNIQUE constraint on it. The datatypes/columns for this table were chosen to obtain a number of different datatypes (int, varchar, char, tinyint, datetime, bit...). A System DSN was created to connect to the database, and a small VisualBASIC 6.0 app was created to test the times for the various queries. The version of ADO used was 2.1.

The query used on the database was:

SELECT * FROM tblStressTest

The following commands were issued in the VB app:

For iCount = 1 To 5
    objRS.Open strSQL, objConn, adOpenForwardOnly, adLockPessimistic
    
    Do While Not objRS.EOF
        objRS.MoveNext
    Loop
    
    objRS.Close
Next iCount

(The cursor type and locktype were, of course, changed for each run.) A total of ten runs were made for each cursor type against the following locktypes: adLockReadOnly, adLockOptimistic, and adLockPessimistic. These ten run times were averaged, and the resulting total run times, in milliseconds, are given below:

Happy Programming!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article