Problem with Transaction when updating Access DB

[Fred48]

Hi,

I am having a problem trying to use transaction for updating my Access databases. I used both transaction and no transaction and I get the same run time.

Below is a sample of my code with transaction processing:

Dim Transaction As OleDbTransaction = cnnNew.BeginTransaction

strSQL = "INSERT INTO FI(" & _
"[TGSN], " & _
"[MEAS_PRD], " & _
"[BASE_YEAR], " & _
"[REQ_MO1], " & _
"[REQ_TRKS1], " & _
"[REQ_MO2], " & _
"[REQ_TRKS2], " & _
"[REQ_MO3], " & _
"[REQ_TRKS3], " & _
"[REQ_MO4], " & _
"[REQ_TRKS4], " & _
"[REQ_MO5], " & _
"[REQ_TRKS5], " & _
"[REQ_MO6], " & _
"[REQ_TRKS6]) " & _
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

Using cmd As New OleDbCommand(strSQL, cnnNew)
cmd.Transaction = Transaction
cmd.Parameters.AddWithValue("TGSN", Downloaded_Rec_FI.tgsn)
cmd.Parameters.AddWithValue("MEAS_PRD", Downloaded_Rec_FI.meas_prd)
cmd.Parameters.AddWithValue("BASE_YEAR", Downloaded_Rec_FI.Base_Year)
cmd.Parameters.AddWithValue("REQ_MO1", arrForecastYears(0).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS1", arrForecastYears(0).Req_Trks)
cmd.Parameters.AddWithValue("REQ_MO2", arrForecastYears(1).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS2", arrForecastYears(1).Req_Trks)
cmd.Parameters.AddWithValue("REQ_MO3", arrForecastYears(2).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS3", arrForecastYears(2).Req_Trks)
cmd.Parameters.AddWithValue("REQ_MO4", arrForecastYears(3).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS4", arrForecastYears(3).Req_Trks)
cmd.Parameters.AddWithValue("REQ_MO5", arrForecastYears(4).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS5", arrForecastYears(4).Req_Trks)
cmd.Parameters.AddWithValue("REQ_MO6", arrForecastYears(5).Req_Mo)
cmd.Parameters.AddWithValue("REQ_TRKS6", arrForecastYears(5).Req_Trks)

cmd.ExecuteNonQuery()

End Using


[other processing]

Transaction.Commit()


We changed our code from ADODB to OLEDB and we have notice an increase in processing time therefore we have tried to using Transaction.

Any help would be greatly appreciative.

 

[PRPhx]

What error are you getting? Which version of vb are you using???? We need more info.

 

[Fred48]

Hi,

I am sorry, I am not getting an error but the processing time for using the Transaction is the same without using Transaction. It is my understanding if you have a lot of data you can save time using Transaction. In the previous code we were using ADODB and there was BeginTrans now we are converting to OLEDB we would like to use the BeginTransaction and etc. we are using VB.NET 2005; the data bases are being stored in Access on a local PC.

If you need additional information please let me know.

Thanks

 

[RiverGuy]

I've never used transactions with Access. I was not even aware Access was capable. However, I can't see how using a transaction would cause a command to complete more quickly. In databases, transactions are typically used to accomplish the "A (Atomic)" in "ACID." This means that an entire operation will complete or not. If you had a bank account database, and you needed to debit one account and credit another, you would use a transaction so that if the debit succeeded and the credit failed, the entire operation would be rolled back as if nothing happened.

 

[Fred48]

The transaction processing for us stared in VB 3.0 when we were loading large files to a Microsoft Access DB. The original code was to update the data base after each record was populated. Using this process it took a lot of time to insert records to the data base. A developer at the time find by using Begin/Committ Transaction it would speed up the processing by Committing the Transactions after a number of updates (by a variable).

The assumption is the BeginTransaction and CommittTransaction works the same as BeginTrans/CommitTrans used to work. Are you suggesting that is no longer true?

 

[RiverGuy]

Is this what you are saying?

Original Code:
--------------
1. Insert Record
2. Update table
3. Insert Record
4. Update table


New Code:
--------------
1. Insert Record
2. Insert Record
3. Insert Record
4. etc.
100. Update table

Obviously, your new code would be faster because you're not doing as many update operations.

Also, without knowing anything about transactions against Access/Jet, it could also have something to do with a temporary transaction log in memory growing too large and needing to be written to the disk. Committing the transaction more frequently could then stay resident in memory. However that's just a wild guess. I don't have a clue how Access handles them.

But as far as overall architecture is concerned, it would be faster still to do all of the inserts and then do the update once, at the end. Possibly faster still would be to Bulk Insert all of the records into a Temporary/Staging table, and then merge them to your production tables. However, I'm not sure it's supported for access/System.Data.OLEDB.

Your main bottleneck is simply the fact that you are calling Insert after insert, individually.

 

[Fred48]

Hi,

Yes, that is what I am trying to accomplish (New code). You may be right about doing a committ more frequently than at the end of processing. I will change my code and let you know.

Thanks again!!!!!!!!!

 

[PRPhx]

River Guy:
Apparently transactions have been a part of Access for some time. I have a book on Access 2000 that has a whole chapter devoted to transactions.

Fred48:
WARNING: I take NO responsibility for you altering your system registry. DO so atyour own risk.

Take a look at \HK\Local_Machine\Software\Microsoft\Jet\4.0\Engines\Jet 4.0.

Look at: ImplicitCommitSync. The default value is No.

Also look at: ExclusiveAsyncDelay (default is 2000 milliseconds) - affects db's opend exclusively

SharedAsyncDelay (default is 50 milliseconds) - affects time for implicit transactions commiting transactions. Setting this value higher can increase performance of implicit transactions, however concurrency issues may crop up.

Lastly, look at UserCommitSync, which affects explicit transactions.

You might also do a google search for each setting. There is lots of info out there. I have Access 2003 installed and the defaults are the same as for Access 2000, so it seems these settings have been consistant for a while.

One place to start is with this link:

http://doc.ddart.net/mssql/sql2000/html/mdacxml/htm/mdrefjetprovspec.htm

Good luck! Altho, if your handling a lot of data, why not update to SQL Server or SQL Server Express (it's free)?

 

[Fred48]

Hi,

RiverGuy,

I tried to do a Committ and 100 records and it still takes the same amount of time.

PRPhx,

Thanks for the information. I do not think I will change settings of the PC since this application is delivered to our clients (100) in the field.

When our product was first developed we were using Access 97 and processing time was very slowed therefore we went to transaction processing whick increased the time. Since then we migrated to Access 2000 and apparently no one notice the time increase. It is also interesting to note when we changed processing from ADODB to OLEDB the time increased. with ADODB the time was 2 minutes and now with olEDB the time increased to almost 6 minutes with the same data.

I will check out the link to see if there is anything that can help me.

Thanks again!!