Record Locking with VB6/VBA, ADO, and SqlServer

Using VBA code, ADO, and Sql Server, I get "Timeout Expired" when trying to find a record in a table.  All other machines are executing the same code, updating different document records.  They do Optimistic locking, so they do not keep any locks except at the "rs1.Update" stage.

I understand Sql Server sometimes does page level locking when doing its updates, but 60 second command timeout should be more than enough for any updates to finish, and for a machine to be able to do the Find operation.

Does anyone see what I'm doing wrong, of what else I can try?


Also, here is a note from previous problems I've had, if anyone as any insight on why this happens.
On sql server, if we do Find_DocumentByDocId(rs, 1), then for DocId 2, 3, etc.,
  the first one is ok,  all subsequent ones return an empty dataset.
If we change to adUseClient (even just for the first item), it works ok for all items,
  even the subsequent ones that are not set as adUseClient
If we do adOpenDynamic, subsequent adOpenKeyset are ok; if we do adOpenStatic, the first adOpenKeyset is ok but subsequent ones are EOF
  so... for now, we're using adUseClient until weI find out what the problem is



'Globals
    dim SrcConnection as ADODB.Connection
    ... omitted some code to setup SrcConnection
    ... CommandTimeout = 60

    Dim cmd1 As ADODB.Command, _


' Setup
sub Setup()
    Set cmd1.ActiveConnection = SrcConnection
    cmd1.CommandText = "select * from tDocument where DocId = ?"
    cmd1.CommandType = adCmdText
    cmd1.CommandTimeout = SrcConnection.CommandTimeout
    cmd1.Prepared = True

    Dim prm1 As ADODB.Parameter
    Set prm1 = cmd1.CreateParameter("DocId", adInteger, adParamInput): cmd1.Parameters.Append prm1
end sub

sub Find_DocumentByDocId(byref rs1 as ADODB.Recordset, DocId as Long)
    cmd1(0) = DocId  ' can also do Cmd1("DocId") = DocId

    dim rs1 as ADODB.Recordset
    Set rs1 = New Recordset
    rs1.CursorLocation = adUseClient
   
    rs1.Open cmd1, , adOpenKeyset, adLockOptimistic, 0
end sub

'Repeated calls to find and process another DocId:
sub ProcessDocId(DocId as long)
    dim rs1 as ADODB.RecordSet
    Find_DocumentByDocId rs1,DocId
    If (rs1.EOF Or rs1.BOF) Then
       ... not found
    else
       do some process which may take a minute or two
          the process may change fields in rs1 while it's working, but doesn't do an Update yet
       rs1!DocStatus = "Complete"
       rs1.Update
    end if
    rs1.close
end sub
amp834Asked:
Who is Participating?
 
harfangCommented:
Have you tried a stored procedure? This would ensure that SQL Server is performing the search, not Access or the driver. You are right, finding a record using an index should be very fast. Sorry I don't have more experience with SQL Server...

(°v°)
0
 
amp834Author Commented:
Anyone have any suggestions?
0
 
harfangCommented:
You could try to simplify your code. I don't really see the need for the command object and its parameter. Since you are (potentially) editing records, stick with a client-side cursor and dynamic recordsets.

You could also open a static recordset for your one-minute processing, and update the field using an update query at the end:

    strSQL _
        = " UPDATE tDocument" _
        & " SET DocStatus = 'Complete'" _
        & " WHERE DocID = " & DocID
    SrcConnection.Execute strSQL

(°v°)
Sub ProcessDocId(DocId As Long)
 
    Dim rs1 As New ADODB.Recordset
    
    rs1.Open "select * from tDocument where DocId = " & DocId, _
        SrcConnection, _
        adOpenDynamic, _
        adLockOptimistic
 
    If (rs1.EOF Or rs1.BOF) Then
       ' ... not found
    Else
       ' do some process which may take a minute or two
       rs1!DocStatus = "Complete"
       rs1.Update
    End If
    rs1.Close
    
End Sub

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
amp834Author Commented:
After further reading, it seems Client side cursors are always Static for SqlServer.

The Command object is to avoid re-compiling the SQL query (which may be called, say, 80,000 times within a loop)

Anything with transaction isolation levels or other default parameters?
0
 
chrismcCommented:
With out looking too closely, I might suggest that the record is being locked by another process.

0
 
harfangCommented:
> it seems Client side cursors are always Static for SqlServer.

I had it the other way around: only client-side cursor allows a dynamic recordset. I never really used server-side, so I can't vouch for it, though.

> avoid re-compiling the SQL query

Recompiling this tiny SQL is not the bottleneck, I think. If you worry about performance, open your entire table and use .Find to locate the record. This should be faster than passing a parameter and re-generating your recordset.

Anyway, your main problem is the lock and the time-out. I'd say first make it work, and then worry about optimization...

(°v°)
0
 
amp834Author Commented:
Unless I'm doing something wrong, .Find is very very slow.  Maybe there's a combination of open modes (cursor type, etc.) that makes it fast, but I haven't discovered it yet!  For 100,000 records, it should find a record in less than a fraction of a second, but it seems to be several seconds.

For the time-out, I'm changing the default transaction isolation level and trying it out again.  I should be able to test it in a few days and will post the results.




0
 
chrismcCommented:
Possibly a stupid question but is the table indexed properly?
That's often a cause of slowdowns.
0
 
amp834Author Commented:
Hi chrismc.  The table is indexed by DocId, and the precompiled queries run quickly while the "Find" commands are very slow.  I suspect "Find" does a sequential search and not an indexed search, or is not smart about using the indexes.
0
 
chrismcCommented:
As you are using ADO rather than ADO.Net, I suspect it's doing the search with a cursor i.e.  it's reading it in row by row to the client.
Cursors are never a good thing and should be used as a last resort.
I vaguely remember something similar when I switched a VB6 app from using Access to SQL.

So harfang's suggestion of ensuring the search is done in SQL is valid. The issue is not SQL as such but the ADO driver. ADO.Net works completely differently and is geared more to SQL than Access.

Obviously this isn't helping much. Either create a stored procedure to do the find or look for alternative methods on the recordset like filter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.