Solved

Record Locking with VB6/VBA, ADO, and SqlServer

Posted on 2008-06-09
12
1,046 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:amp834
  • 4
  • 3
  • 3
12 Comments
 

Author Comment

by:amp834
ID: 21747478
Anyone have any suggestions?
0
 
LVL 58

Expert Comment

by:harfang
ID: 21747732
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
 

Author Comment

by:amp834
ID: 21751344
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
 
LVL 18

Expert Comment

by:chrismc
ID: 21751584
With out looking too closely, I might suggest that the record is being locked by another process.

0
 
LVL 58

Expert Comment

by:harfang
ID: 21756265
> 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:amp834
ID: 21760713
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
 
LVL 18

Expert Comment

by:chrismc
ID: 21762121
Possibly a stupid question but is the table indexed properly?
That's often a cause of slowdowns.
0
 

Author Comment

by:amp834
ID: 21765802
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
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
ID: 21766057
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
 
LVL 18

Assisted Solution

by:chrismc
chrismc earned 250 total points
ID: 21766464
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now