[thelist] SQL Update Query - Easy

[Ken Schaefer]

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On
: Behalf Of Scott Dexter
: Subject: Re: [thelist] SQL Update Query - Easy
: 
: 
: > set RS = Server.CreateObject("ADODB.Recordset")
: > RS.ActiveConnection = MM_web_request_STRING
: > RS.CursorType = 0
: > RS.CursorLocation = 2
: > RS.LockType = 3
: >
: > while  counter < uBound(Video)+1
: > 	RS.Source = "Update tblVideos SET CheckedIn = 1 WHERE Video = '" &
: > Video(counter) & "'"
: > 	RS.Open()
: > 	counter = counter + 1
: > wend
: 
: You have to do an RS.Update() to get it to commit, IIRC.
: 
: BUT Eeek! Use an Update statement, dear boy. The recordset route is
: seriously performance challenged. Change it up to

Absolutely. Here are some performance numbers from stress testing I did a
while back:

<quote>
Our second test involved a combination of inserts and reads. Three insert
methods were tested (inline SQL, and two recordset based types). Three
recordset types were also tested. For the Jet/Access test, the only usable
updateable recordset cursortype is Keyset with either an Optimistic or
Pessimistic locktype (Jet does not support batch updates).

Access/Jet Test Results

Reads/Inserts          Read Method
Insert Method	      Forward/ReadOnly	Static/ReadOnly	Keyset/Pessimistic
SQL			1132/1192	855/860	855/939
RS(Keyset/Optimistic)	1022/1062	889/885	759/830
RS(Keyset/Pessimistic)	 979/956	751/810	689/803

>From the results we can see that the ForwardOnly/ReadOnly cursor for data
retrieval is clearly the fastest, in combination with SQL insert statements.
Moving to using a Recordset to insert data resulted in a minimum 10% drop in
requests served. At the far end of the scale, the use of the heavy Keyset
cursor (with pessimistic locking) for both data retrieval and insertion
resulted in a 35% drop in requests served.

SQL Server Test Results

Reads/Inserts	Read Method
Insert Method	      Forward/ReadOnly	Static/ReadOnly   Keyset/Pessimistic
SQL			1698/1737	1090/1116	  957/1033
RS(Keyset/Optimistic)	1600/1675	1073/1108	  927/992
RS(Keyset/Pessimistic)	1603/1657	1078/1164	  901/925

The use of Optimistic or Pessimistic locktypes didn't have much effect in the
SQL Server tests. Moving from SQL to Recordsets for inserts caused an
approximate 5% decline in requests served across the board. Moving from the
lightest combination (ForwardOnly/ReadOnly + SQL Inserts) to the heaviest
combination (Keyset/Pessimistic for both inserts and reads) resulted in a 46%
drop in requests served.
 </quote>


Cheers
Ken