Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Record Locking with VB6/VBA, ADO, and SqlServer

Posted on 2008-06-09
12
Medium Priority
?
1,083 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

 
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
 

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 1000 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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

660 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