Solved

ADO records delete much slower than DAO records

Posted on 2001-08-15
22
540 Views
Last Modified: 2013-11-23
Question:
What can I do to speed the process of deleting records from an ADO recordset created in VB from an Access database source?

Background:
I have a project that I recently converted from DAO to ADO (VB6 using MDAC 2.1).  The purpose of the project is to cycle through multiple Access 97 databases for maintenance - such as archiving/deleting old records in tables and repairing/compacting the databases.  The parameters are all table-driven (database names and paths, tables to archive, days to retain/delete, etc).  I use SQL statements to create the recordsets to process.

The record delete process is taking a very long time to complete in ADO.  The logic for both versions of the project is the same - loop through all records in the recordset and delete them one by one.  In DAO this was the most efficient way, avoiding other problems like locking issues in Access with append/delete queries with several thousand records.  My logic using DAO would process even recordsets as large as 75,000 records in several seconds.  The ADO code takes nearly 2 seconds per record to delete, and on large recordsets it hangs in mid-process.

My ADO object statements:
     Set dbAge = New ADODB.Connection
     dbAge.CursorLocation = adUseClient
     dbAge.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strDBPath & strDBName
     dbAge.Open

     recAge.Open strSQL, dbAge, adOpenForwardOnly, adLockOptimistic, adCmdText

The loop to delete the records (with some record counting, messaging and error trapping removed for simplicity):
     Do While Not recAge.EOF
            recAge.Delete
            recAge.MoveNext
        Loop

I am using client-side cursors and low-overhead cursors.  The maintenance is done after business hours and requires users be disconnected from the Access databases.

I've unsuccessfully tried more updated versions of MDAC.  I've unsuccessfully tried using other types of cursors and cursors with adLockBatchOptimistic to flag a smaller group of records for delete and then UpdateBatch to process them.  I've unsuccessfully tried deleting the reference to the DAO Compatibility Library (which I had left in the project to do the repair/compact using DBEngine) thinking there may have been some type of conflict.

What else can I try to get the performance of ADO to an acceptable, comparable level?
0
Comment
Question by:garyw1
  • 8
  • 4
  • 3
  • +5
22 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Well I can tell you this... Server side cursors are much faster than client side cursors...  Let me try to find some stats on it...

Dave
0
 
LVL 1

Expert Comment

by:garrenb
Comment Utility
.. listening
0
 

Author Comment

by:garyw1
Comment Utility
Dave,
I didn't even consider server-side cursors, because on this and other DAO/ADO conversion projects having server-side cursors resulted in slowness in creating and navigating through recordsets - as much as 50% slower measured by doing some benchmarking.  I changed to server-side cursors for this delete recordset and now I see performance similar to what I had with DAO.  I had to change the cursor type to Static to be able to access my recordcount, but that's no problem.

The issue I see now is the following error after deleting several thousand records in the recordset:  -2147217887, " Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."  All records up to that point are still deleted, but the error caused the process to abort.  Do you know what causes this ADO error - whether it is related to the cursor location or type or some other factor?
0
 

Expert Comment

by:MelissaEvans
Comment Utility
I've noticed that server side cursors are incredibly faster at *getting* the data, but not necesarily processing it.  My coworker made an archiving tool and used a server side cursor (due to my advice) and it was slower than snot on ice.  =(  

Is there a way to run SQL statements that will do the same thing?  "delete from <table> where ..." type of idea so you don't have to delete each one individually.  

In your delete loop, are you doing some updates too?  Those can be dragging it down as well.  
~Melissa
0
 

Author Comment

by:garyw1
Comment Utility
Dave,
I didn't even consider server-side cursors, because on this and other DAO/ADO conversion projects having server-side cursors resulted in slowness in creating and navigating through recordsets - as much as 50% slower measured by doing some benchmarking.  I changed to server-side cursors for this delete recordset and now I see performance similar to what I had with DAO.  I had to change the cursor type to Static to be able to access my recordcount, but that's no problem.

The issue I see now is the following error after deleting several thousand records in the recordset:  -2147217887, " Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."  All records up to that point are still deleted, but the error caused the process to abort.  Do you know what causes this ADO error - whether it is related to the cursor location or type or some other factor?
0
 

Author Comment

by:garyw1
Comment Utility
Dave,
I didn't even consider server-side cursors, because on this and other DAO/ADO conversion projects having server-side cursors resulted in slowness in creating and navigating through recordsets - as much as 50% slower measured by doing some benchmarking.  I changed to server-side cursors for this delete recordset and now I see performance similar to what I had with DAO.  I had to change the cursor type to Static to be able to access my recordcount, but that's no problem.

The issue I see now is the following error after deleting several thousand records in the recordset:  -2147217887, " Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."  All records up to that point are still deleted, but the error caused the process to abort.  Do you know what causes this ADO error - whether it is related to the cursor location or type or some other factor?
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Does recAge have a Primary key? If it does NOT try adding an AutoNumber column as Primary Key.. (this saves ADO the overhead of building a temporary one).

Also.. adOpenForwardOnly? I would definitely try adOpenKeySet with a ServerSide cursor.

=========================================================
By the way.. a MSDN Fix report (the text of which follows the hyperlink).

FIX: Delete/MoveNext Moves to Wrong Record with Jet 4.0 Provider
http://support.microsoft.com/support/kb/articles/Q253/5/61.ASP
-----------------------------------------------------------The information in this article applies to:

Microsoft OLE DB Provider for Jet, version 4.0

-----------------------------------------------------------SYMPTOMS
When deleting records in a loop using the Microsoft Jet 4.0 OLE DB provider, not all records get deleted from the table.



CAUSE
When using forward-only, server-side cursors, the Microsoft Jet 4.0 OLE DB provider incorrectly positions the cursor after executing the Delete method.



RESOLUTION
To resolve this problem, do one of the following:

Use a different cursor type, such as Keyset.
Use a SQL statement, such as: DELETE * FROM MyTable.



STATUS
This has been fixed in Microsoft Jet 4.0 SP4.



MORE INFORMATION
The problem manifests itself only when using forward-only, server-side cursors and deleting records in a loop similar to this:

Do While Not rs.EOF
   rs.Delete
   rs.MoveNext
Loop
With a 10-record table:
With the Microsoft Jet 3.51 OLE DB provider (MSJTOR35.DLL 3.52.1527.4), the loop gets executed 10 times.
With the Microsoft Jet 4.0 OLE DB provider from MDAC 2.1 GA, the loop gets executed five times.
With the Microsoft Jet 4.0 OLE DB provider from MDAC 2.1 SP2 (JETOLEDB40.DLL 4.00.2927.2), the loop gets executed twice.
With the Microsoft Jet 4.0 OLE DB provider (JETOLEDB40.DLL 4.00.3714.0), the loop gets executed 10 times, as expected.
Steps to Reproduce Behavior
In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default.


On the Project menu, select References, and then add the following type library:


Microsoft ActiveX Data Objects 2.1 Library
Add a Command button (Command1) and the following code to the default form:


Option Explicit

Private Sub Command1_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, I As Long
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
  cn.Execute "CREATE TABLE DB12374 (ID Int)"
  For I = 1 To 10
    cn.Execute "INSERT INTO DB12374 VALUES (" & I & ")"
  Next I
  Set rs = New ADODB.Recordset
  rs.Open "SELECT * FROM DB12374", cn, adOpenForwardOnly, adLockOptimistic, adCmdText
  I = 0
  Do While Not rs.EOF
    I = I + 1
    Debug.Print I, "Deleting"; rs(0)
    rs.Delete
    rs.MoveNext
  Loop
  rs.Close
  cn.Execute "DROP TABLE DB12374"
  cn.Close
End Sub
NOTE: You might need to adjust the connect string to point to a valid Jet database.


Execute the project. If you have an older version of the provider, you should get output similar to the following:


 1            Deleting 1
 2            Deleting 2  
If you have the latest version of the provider, or change the cursor type from adOpenForwardOnly to adOpenKeyset, the output should be:
 1            Deleting 1
 2            Deleting 2
 3            Deleting 3
 4            Deleting 4
 5            Deleting 5
 6            Deleting 6
 7            Deleting 7
 8            Deleting 8
 9            Deleting 9
 10           Deleting 10  
? Microsoft Corporation 2000, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation

=========================================================
Latest version of MDAC is v2.6 - but I recommend v2.5.1 -  you can get it here:

http://www.microsoft.com/data/download.htm



0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Ooops.. MDAC v2.5.2 - If you go v2.6.x then make sure you download the Jet 4.0 SP3 drivers (which are separate in the 2.6 version of MDAC).

One quick note.. if indeed you do update or have updated your MDAC.. make sure you copy the mdac_typ.exe file into your

x:\Visual Basic\Wizards\PDWizard\ReDist

folder so that any future setup packages you may create will contain the version of MDAC you are developing with.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>> I have a project that I recently converted from DAO to ADO (VB6 using MDAC 2.1).

The record delete process is taking a very long time to complete in ADO.  The logic for both versions
of the project is the same - loop through all records in the recordset and delete them one by one.  
<<

Unfortunately, I have seen this too often.  Conversions from DAO to ADO using exactly the same logic, which produces frustration and disappointement when performance becomes an issue.

My advice, if you are not prepared to re-write (at least in a non-trivial app) taking advantage of ADO's set functionality you are doomed to exactly the sentiments you are expressing.  The worst thing about it is that ADO unjustifiably gets a bad rap in the process.  The best thing you can do if you are not going to rewrite is to leave well alone.

ADO was never designed to loop through records, the way DAO was.  It is not intended to be used for this purposes, at least not for large quantities of records.

The only way you are going to see any improvement is to restructure your code to using SQL statements:
Delete From Table Where ...
as someone else has already suggested.

My 2 cents worth,
Anthony
0
 

Author Comment

by:garyw1
Comment Utility
Dave,
I didn't even consider server-side cursors, because on this and other DAO/ADO conversion projects having server-side cursors resulted in slowness in creating and navigating through recordsets - as much as 50% slower measured by doing some benchmarking.  I changed to server-side cursors for this delete recordset and now I see performance similar to what I had with DAO.  I had to change the cursor type to Static to be able to access my recordcount, but that's no problem.

The issue I see now is the following error after deleting several thousand records in the recordset:  -2147217887, " Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."  All records up to that point are still deleted, but the error caused the process to abort.  Do you know what causes this ADO error - whether it is related to the cursor location or type or some other factor?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
garyw1

>>I had to change the cursor type to Static
to be able to access my recordcount, but that's no problem.<<

Sorry if you think I am picking on everything you say, but this is another example of where DAO does not translate well to ADO.  Rather than using Static cursors (or doing a MoveLast as I have also seen done)to get a RecordCount it is far better to do a:

Select Count(*) From Table1 Where ...

to get the total number of records.

Of course you should also analyze, if you really need the record count in the first place...

Again, try not to think at a record level and you will not be disappointed.

Anthony
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:garyw1
Comment Utility
Thanks to all for your comments.  I'm trying some things considering all of this input.  It looks like a combination of things will help - server-side cursors, non-forward only recordsets, thinking in sets instead of records.  Let me know if you think of anything else that might help and I'll complete the question tomorrow.
0
 
LVL 1

Expert Comment

by:KingSun
Comment Utility
Hi grayw1,

to delete records from table, loop the recordset and delete one by one is a solution. but from my experience, if you want delete a quite number of records in one time, using sql "delete from ...." will be much much faster than using recordset.
for example, you want delete all records from a table called 'mytable' with 10000 records, then I tried in 2 ways.
1). using ado recordset, loop and delete one by one,
    takes 9:30
2). using 'delete' sql statement
    takes less than 1 second

  sample code like this:
    Dim conn As ADODB.Connection, sqlstr As String
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Support\ITS\supp.mdb;Persist Security Info=False"
   
    Dim d1 As Date, d2 As Date
    sqlstr = "delete * from mytable"
    d1 = Now
    conn.Execute sqlstr
    d2 = Now
   
    Debug.Print d1, d2
   
    conn.Close
    Set conn = Nothing

So, what do you think about the result? Of cause my suggestion is to take the 2nd way.

Hope this can help you!

B.rgds, King
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
I agree with KingSun. Use SQL statements, which is obviously faster. I tried doing some processing, using ADO recordsets and looping through, it takes lot of time. But the same done in SQL statements, is quite faster.

If you want to check with progress of delete, use WithEvents when declaring the recordset, and hope, you can code in the FetchProgress Event.

Hope this helps.
0
 

Author Comment

by:garyw1
Comment Utility
I have tried some of your suggestions this morning with mixed results.  While I agree that using sql statements to perform the deletes is a more efficient and proper way, I keep getting errors when deleting large sets with one statement.  The recurring error is -2147467259 "Could not update; currently locked."  The database is not opened anywhere and there are no security permissions; it is only connected to by this project.

The only way I have been able to perform the entire delete successfully is to loop through a series of smaller recordsets (1000 records each) one record at a time.  My test table has over 75,000 records, a typical sample size.  I can't limit a delete query to x records as with a select query, unless there is some undocumented syntax that will work (can't use TOP), or I would try doing smaller mass deletes to keep with set theory executing sql only.

Any other thoughts on why the recurring errors documented in my comments are happening?  Could it have anything to do with network issues - the Access 97 databases are located on a novell server?   Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Try isolating the problem as much as possible.  Here are a couple of suggestions:

1. Copy the database to your local drive.
2. Create a one function project with just one connection to execute the Delete query. Something like:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
    .ConnectionString = "your connection string goes here"
    .Open
    .Execute "Delete Query goes here with hard-coded condition"
    .Close
End With
Set cn = Nothing

I am not suggesting you leave the code this way, it should just serve to isolate the problem.

Anthony
0
 

Author Comment

by:garyw1
Comment Utility
Good call Anthony.  Local copy of the DB performs correctly - all 75,000+ records are deleted with one sql query statement.

Given my recurring errors while processing the DB on the network, any helps on why it happens - or else how to improve the sql code to force smaller blocks of deletes?  I can't really use dates, because a single day could be a large recordset in itself.  Am I stuck going record by record?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
Comment Utility
In Access the very best you can do is resort to sub-queries, such as:

Delete From Test_Table
Where ID In (Select TOP 1000 ID FROM Test_Table)

But this is ugly and you may lose the increased performance you were getting with the action query in the first place.

If you think it is a network issue, take a second look at that.

Another thing you can do is take a look at the CommandTimeout property.  It is possible that the query is timing out, but is giving you this bogus locking message.

Another thought,  why don't you post the relevant code, preferably condensed to less than 25 lines, so that we can pick it apart, tell you where you have gone grossly wrong  and make you feel small and insignificant.  Just kidding!

Anthony
0
 
LVL 1

Expert Comment

by:KingSun
Comment Utility
I don't think Access is the right database suitable for multiuser application, especially with tables exceed 50k records. From my experience, once a MDB file exceeds 30MB, it becomes unstable.

I have worked on sql for many years. 3 or 4 years ago, I used Access often, but now I concertrate on Oracle. and also I have exeperience on SQL server. for you case, I'll recommend you think about other database server, like SQL server(it's cheap!).

I think the topic we are discusing is about house-keeping function. so can you describe more detail about how often you run it, and why so many records should be deleted one time, and etc. believe we can find a final solution.
0
 
LVL 1

Expert Comment

by:garrenb
Comment Utility
Hi,
I would agree with KingSun. Access databases throw up very odd messages when it's size grows too big.
It's not a huge task to port the data over to an MSDE database. The beauty of this is that if in the future you went down the SQL Server route then your code & data access would remain unchanged.

The MSDE is a lot more stable than Access.

GarrenB
0
 
LVL 1

Expert Comment

by:garrenb
Comment Utility
Sorry, Also this is a bit unprofessional .. but if you were processing the database when everyone is offline you could use Microsoft's JRO to make a compacted *local* copy of the database, process it locally then dump the complete database back to the server. Thus bypassing the slow deletion process across the network and doing the job with very little additional code indeed!


GarrenB
0
 

Author Comment

by:garyw1
Comment Utility
Thank you all for your input.  Anthony (acperkins), you ultimately gave me the solution that worked the best with this quirky network - use subqueries to delete blocks of records.  Cursor location ultimately did not matter using SQL deletes and the CommandTimeout property did not work, probably due to some inherent network limitation, but the subqueries run actually faster than the record-by-record looping or anything else I tried (except local drive processing).  I can run the whole process on the 75,000+ record table with blocks of records up to 10,000 in less than 1 minute with no errors.  Given where I started and with the systems I have to work with, that's not an "ugly" solution at all!

As garrenb and KingSun have said Access is not the place for these types of databases.  I have known this for years but have only recently been able to get the corporate wheels turning toward SQL Server.  But that will take time.  And have many VB apps all feeding to and from these Access databases that need to be rewritten for SQL.  One step at a time. Hopefully this app will be obsoleted by SQL sooner than later.

BTW, Anthony, I like your style and sense on humor, and I appreciate the help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

17 Experts available now in Live!

Get 1:1 Help Now