When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Sunday, June 27, 1999

Cursor & LockType Performance Report


Computer Specs:

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

    - continued -

    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!


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