asked on

ADO records delete much slower than DAO records

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?

Dave_Greene

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

garrenb

.. listening

garyw1

ASKER

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?

MelissaEvans

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

garyw1

ASKER

garyw1

ASKER

wsh2

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

wsh2

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.

Anthony Perkins

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

garyw1

ASKER

Anthony Perkins

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

garyw1

ASKER

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.

KingSun

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

Valliappan AN

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.

garyw1

ASKER

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.

Anthony Perkins

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

garyw1

ASKER

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?

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KingSun

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.

garrenb

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

garrenb

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

garyw1

ASKER

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.