Cursor performance and memory usage 
Author Message
 Cursor performance and memory usage

We're evaluating using ASP pages that cache ADO recordsets in the
Session object.  (This means, the lifetime of a cursor will be the
lifetime of the users session, unless explicitly deallocated).

So we're concerned with the resource usage of cursors and their
performance.

I did some benchmarks on SQL 6.5.  I created the cursors inside of
stored procedures.

(1) FORWARD ONLY CURSOR
Example:   DECLARE cursor1 CURSOR
           FOR SELECT id, field1 FROM table1 WHERE id <= 10000
NOTE:   This type of cursor doesnt support FETCH LAST, so the last
record is retrieved by using a loop to traverse entire recordset.

  #Rows         Table
  Selected      Size    t-first (s)    t-last (s)   t-total (s)
  100           1,000     0.000        0.120          0.120    
  100          10,000     0.000        0.120          0.120
  100         100,000     0.000        0.130          0.130
  100       1,000,000     0.000        0.130          0.130
  1,000         1,000     0.000        1.333          1.333
  1,000        10,000     0.000        1.380          1.380
  1,000       100,000     0.000        1.360          1.360
  1,000     1,000,000     0.000        1.380          1.380
  10,000       10,000     0.030        14.000        14.030
  10,000      100,000     0.000        14.432        14.320
  10,000    1,000,000     0.000        13.830        13.830
  100,000     100,000     0.040        191.023      191.063
  100,000   1,000,000     0.020        192.426      192.446

EXPLANATION:
  t-first is the time to open the cursor and retrieve the first row.
  t-last is the time to then go retrieve the last row.
  t-total = t-first + t-last

  The 4 tables used have identical schema.  The 'id' field is an 'int'
and has a unique index (non-clustered).

COMMENTS:
This cursor opens very fast.  NOTE:  Since this is a forward-only
cursor, the last record was retrieved by using a loop to sequentially
traverse the entire recordset.  This was not necessary in the following
two scrollable cursors.

(2) KEYSET DRIVEN CURSOR
Example:   DECLARE cursor1 SCROLL CURSOR
           FOR SELECT id, field1 FROM table1 WHERE id <= 10000

  #Rows        Table
  Selected      Size      t-first (s)  t-last (s)   t-total (s)
  100          1,000        0.040        0.000       0.040      
  100         10,000        0.090        0.000       0.090
  100        100,000       11.226        0.030       11.256
  100      1,000,000      111.200        0.020       111.220
  1,000        1,000        0.290        0.000       0.290
  1,000       10,000        0.034        0.000       0.340
  1,000      100,000       11.566        0.030       11.596
  1,000    1,000,000      110.920        0.020       110.940
  10,000      10,000        2.833        0.000       2.833
  10,000     100,000       13.196        0.040       13.236
  10,000   1,000,000      112.660        0.050       112.710
  100,000    100,000       32.950        0.020       32.970
  100,000  1,000,000      131.350        0.070       131.420

(3) STATIC CURSOR
Example:   DECLARE cursor1 INSENSTIVE SCROLL CURSOR
           FOR SELECT id, field1 FROM table1 WHERE id <= 10000

           (results were same as KEYSET DRIVEN CURSOR)

COMMENTS:
The time it took to create these scrollable STATIC and KEYSET cursors
was quite large for the large tables.  Oddly, it seems the time required
was more dependent on the size of the table than on the size of the
result set.

QUESTIONS:
Is there a way to improve performance of KEYSET DRIVEN or STATIC cursors
on large tables?
Would making the index clustered help?

Also are there any guidelines on how much memory cursors use?  And where
is that memory allocated?  I suspect in tempdb because I got the
following error when I tried creating a cursor containing 1,000,000
rows:

 37000[Microsoft][ODBC SQL Server Driver][SQL Server]
 Cant allocate space for object -1333580333 in database tempdb
 because the default segment is full.  If you ran out of space in
syslogs, dump the transaction log.

Any comments would be appreciated.  Thank you.

REFERENCES:
http://www.***.com/
INF: Overview of SQL Server, ODBC, and DB-Library Cursors



Fri, 05 Nov 1999 03:00:00 GMT
 Cursor performance and memory usage

Quote:

> The time it took to create these scrollable STATIC and KEYSET cursors
> was quite large for the large tables.  Oddly, it seems the time required
> was more dependent on the size of the table than on the size of the
> result set.

> QUESTIONS:
> Is there a way to improve performance of KEYSET DRIVEN or STATIC cursors
> on large tables?
> Would making the index clustered help?

I tried clustered index and it helped:

KEYSET CURSOR:
  ________   ________   ________________________
   # Rows     # Rows            t-first (s)      
  Selected   in Table   NON-CLUSTERED  CLUSTERED    
      100     100,000       11.226       0.040                  
    1,000     100,000       11.566       0.190            
   10,000     100,000       13.196       1.853        
  100,000     100,000       32.950      22.680            
      100   1,000,000      111.200       0.020        
    1,000   1,000,000      110.920       0.240
   10,000   1,000,000      112.660       1.873
  100,000   1,000,000      131.350      22.500

EXPLANATION:
  t-first is the time to open and fetch the first record.

COMMENTS:
  With the clustered index, the time to create the cursor is now more
proportional to the size of the result set. (Before it seemed to only
depend on the size of the underlying table).  The performance
improvement is dramatic.

REMAINING QUESTION:
How much space in tempdb is consumed by a scrollable cursor?

I tried using Performance Monitor to measure this.  I monitored SQL
Server's Max tempDB Used (MB) which is 'The maximum number of (MB) used
in tempdb during this monitoring'.  However, it always reads 0.000.  Why
would that be?

I'm trying to come up with a guideline for cursor memory usage.  I'm
guessing it might be something like this (for a simple single table
SELECT query):

  index_size_per_row
       = (unclustered primary key index size [bytes]) / (# rows in
table)
  cursor_memory_usage
       = index_size_per_row * (# rows in result set)

EXAMPLE
  index_size_per_row = 23,086 * 1,024 Bytes / 1,000,000 Rows
     = 23.64 bytes/row
  cursor_memory_usage = (23.64 bytes/row) * (333,000)
     =  7.51 MB for a 333,000 row cursor.
  333,000 is the size at which I run out of space in 'tempdb' which has
7.82 MB free space.



Sat, 06 Nov 1999 03:00:00 GMT
 Cursor performance and memory usage

Quote:

> I'm trying to come up with a guideline for cursor memory usage.  I'm
> guessing it might be something like this (for a simple single table
> SELECT query):

>   index_size_per_row
>        = (unclustered primary key index size [bytes]) / (# rows in
> table)
>   cursor_memory_usage
>        = index_size_per_row * (# rows in result set)

> EXAMPLE
>   index_size_per_row = 23,086 * 1,024 Bytes / 1,000,000 Rows
>      = 23.64 bytes/row
>   cursor_memory_usage = (23.64 bytes/row) * (333,000)
>      =  7.51 MB for a 333,000 row cursor.
>   333,000 is the size at which I run out of space in 'tempdb' which has
> 7.82 MB free space.

The following table was calculated in this manner:

        # Rows in Cursor     Tempdb Usage (MB)
               100             0.00225 (2.3 KB)        
             1,000             0.0225  (23 KB)
            10,000             0.225   (230 KB)
           100,000             2.25
         1,000,000            22.5

SCENARIO:  100 users each using cursors of 1,000,000 rows.  
This would require 100 * 22.5 MB = 2.2 GB of space in tempdb.  The
default size of tempdb, when installing SQL Server, is 2MB.  Is dont
know if a 2.2 GB tempdb is unrealistic or not.  Is it?



Sat, 06 Nov 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Memory usage during Cursor Processing using Pro-C

2. Monitor SQL Server Memory Usage Using NT Performance Monitor

3. ADO Causes 100% Memory Usage/Increases in VM Usage

4. WARNING: memory usage in procedure headers (1) does not match memory usage count in Pss (66) for server process id 63

5. Memory Usage by MSSQL server 2000

6. Abnormal memory usage of SQL-server

7. Memory usage and stored procedures

8. SQL SERVER MEMORY USAGE

9. SQL Server hogging up CPU usage / memory leaks

10. Memory usage per user

11. SQL Server Memory Usage


 
Powered by phpBB® Forum Software