To read the article online, visit http://www.4GuysFromRolla.com/webtech/062799-3.report.shtml

Cursor & LockType Performance Report


Computer Specs:

  • Pentium II - 450 MHz
  • Windows NT Server 4, SP3
  • 256 MB RAM
  • 8 GB SCSI HD

    ER Diagram. 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:

    Performance testing results.

    Happy Programming!


  • Article Information
    Article Title: Cursor & LockType Performance Report
    Article Author: Scott Mitchell
    Published Date: Sunday, June 27, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/062799-3.report.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers