Speed Up OleDbDataAdapter.Update()
-
Hello, first time asking a question here, so please be gentle :) I'm filling a dataset and using OleDbDataAdapter.Update() to insert it into a DBF table using Jet. The update is running painfully slow and I've read up a bit on how to speed up and saw something about starting the transaction before running the update. Doesn't really seem to do much for me. I have a table write that goes on for 24 minutes. Any help would be appreciated. Code snippit: If Not cnDBConn.State = ConnectionState.Open Then cnDBConn.Open() End If Dim transID As System.Data.OleDb.OleDbTransaction = cnDBConn.BeginTransaction() cmInsert.Transaction = transID daAdapter.InsertCommand = cmInsert daAdapter.DeleteCommand = Nothing daAdapter.UpdateCommand = Nothing daAdapter.ContinueUpdateOnError = False daAdapter.Update(dsOut, sCurrentTable)
-
Hello, first time asking a question here, so please be gentle :) I'm filling a dataset and using OleDbDataAdapter.Update() to insert it into a DBF table using Jet. The update is running painfully slow and I've read up a bit on how to speed up and saw something about starting the transaction before running the update. Doesn't really seem to do much for me. I have a table write that goes on for 24 minutes. Any help would be appreciated. Code snippit: If Not cnDBConn.State = ConnectionState.Open Then cnDBConn.Open() End If Dim transID As System.Data.OleDb.OleDbTransaction = cnDBConn.BeginTransaction() cmInsert.Transaction = transID daAdapter.InsertCommand = cmInsert daAdapter.DeleteCommand = Nothing daAdapter.UpdateCommand = Nothing daAdapter.ContinueUpdateOnError = False daAdapter.Update(dsOut, sCurrentTable)
Transactions don't speed anything up, so don't bother. A transaction just encapsulates a collection of operations so the collection is treated as a single unit. If one part of the transaction fails, all of the operations are rolled back. Access isn't really fast to begin with, so I'd start by changing the database engine you're using. Perhaps SQL Express would work better?? You're code is running about as fast as it's going to. How many records are in this adapters DataSet??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Transactions don't speed anything up, so don't bother. A transaction just encapsulates a collection of operations so the collection is treated as a single unit. If one part of the transaction fails, all of the operations are rolled back. Access isn't really fast to begin with, so I'd start by changing the database engine you're using. Perhaps SQL Express would work better?? You're code is running about as fast as it's going to. How many records are in this adapters DataSet??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
Access isn't really fast to begin with, so I'd start by changing the database engine you're using. Perhaps SQL Express would work better??
How do I do that? I'm writing out to .dbf files (DBase IV). Previously, it was using DTS in SQL2000, but now we've migrated to SQL2005 and DTS is no longer an option.
Dave Kreskowiak wrote:
How many records are in this adapters DataSet??
It's a data conversion project, so it could see anywhere from 0 - million + records...
Dave Kreskowiak wrote:
You're code is running about as fast as it's going to.
Always good to hear that :)
-
Dave Kreskowiak wrote:
Access isn't really fast to begin with, so I'd start by changing the database engine you're using. Perhaps SQL Express would work better??
How do I do that? I'm writing out to .dbf files (DBase IV). Previously, it was using DTS in SQL2000, but now we've migrated to SQL2005 and DTS is no longer an option.
Dave Kreskowiak wrote:
How many records are in this adapters DataSet??
It's a data conversion project, so it could see anywhere from 0 - million + records...
Dave Kreskowiak wrote:
You're code is running about as fast as it's going to.
Always good to hear that :)
A Wong wrote:
How do I do that? I'm writing out to .dbf files (DBase IV). Previously, it was using DTS in SQL2000, but now we've migrated to SQL2005
OK. You initally said you're using an Access database, now you say you're using SQL2005? Which is it? If you're using Access (Jet), then you'd be using the OleDb classes. If SQL2005 (any edition), you'd be better served using the Sql specific classes (SqlCommand, SqlConnection, ...).
A Wong wrote:
It's a data conversion project, so it could see anywhere from 0 - million + records...
Since a DataSet goes through each record in it's tables and updates each one individually, this is going to take a long time. Each updated is being performed on the database, one record at a time. So, if a single record takes half a second to update and you've got 1,000,000 records, guess what... That's going to be 138 minutes to go all the updates. You need to come up with a scheme that handles all the updates at once and can use Sql Server's Bulk insert facilities. You can find an example of this here[^]. Another option, building on the previous bulk insert method, would be to compile a table with all of the udpates required, and uploading the entire table into a temporary table in the database. This upload would go very fast using Bulk Insert. Next, you need an stored proecure in the database to iteract over that table and process each update individually. Each record in the table would probably have a dedicated field with the type of update that needs to be performed.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
A Wong wrote:
How do I do that? I'm writing out to .dbf files (DBase IV). Previously, it was using DTS in SQL2000, but now we've migrated to SQL2005
OK. You initally said you're using an Access database, now you say you're using SQL2005? Which is it? If you're using Access (Jet), then you'd be using the OleDb classes. If SQL2005 (any edition), you'd be better served using the Sql specific classes (SqlCommand, SqlConnection, ...).
A Wong wrote:
It's a data conversion project, so it could see anywhere from 0 - million + records...
Since a DataSet goes through each record in it's tables and updates each one individually, this is going to take a long time. Each updated is being performed on the database, one record at a time. So, if a single record takes half a second to update and you've got 1,000,000 records, guess what... That's going to be 138 minutes to go all the updates. You need to come up with a scheme that handles all the updates at once and can use Sql Server's Bulk insert facilities. You can find an example of this here[^]. Another option, building on the previous bulk insert method, would be to compile a table with all of the udpates required, and uploading the entire table into a temporary table in the database. This upload would go very fast using Bulk Insert. Next, you need an stored proecure in the database to iteract over that table and process each update individually. Each record in the table would probably have a dedicated field with the type of update that needs to be performed.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
OK. You initally said you're using an Access database, now you say you're using SQL2005? Which is it? If you're using Access (Jet), then you'd be using the OleDb classes. If SQL2005 (any edition), you'd be better served using the Sql specific classes (SqlCommand, SqlConnection, ...).
I'm sorry for the confusion. I'm writing from SQL2005 -> DBase IV and I'm using Jet to do so currently. I've looked around, and it just doesn't seem to be any other way to do this. If you know another way, I'd like to know for sure. In the mean time, I'll go read the Bulk features.
-
Dave Kreskowiak wrote:
OK. You initally said you're using an Access database, now you say you're using SQL2005? Which is it? If you're using Access (Jet), then you'd be using the OleDb classes. If SQL2005 (any edition), you'd be better served using the Sql specific classes (SqlCommand, SqlConnection, ...).
I'm sorry for the confusion. I'm writing from SQL2005 -> DBase IV and I'm using Jet to do so currently. I've looked around, and it just doesn't seem to be any other way to do this. If you know another way, I'd like to know for sure. In the mean time, I'll go read the Bulk features.
A Wong wrote:
I'm writing from SQL2005 -> DBase IV
So you're moving data from an SQL2005 database to a DBase IV database?? AFAIK, DBase IV doesn't have any bulk load features. I haven't touched a DBase product in about 20 years, so my experience is quite limited. OleDb doesn't support any bulk load features so I think you're pretty much screwed there. I think it's going to have to be one record at a time and however long it takes it going to be how long it takes. I don't see a way around this if you're moving data to a DBase IV database.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
A Wong wrote:
I'm writing from SQL2005 -> DBase IV
So you're moving data from an SQL2005 database to a DBase IV database?? AFAIK, DBase IV doesn't have any bulk load features. I haven't touched a DBase product in about 20 years, so my experience is quite limited. OleDb doesn't support any bulk load features so I think you're pretty much screwed there. I think it's going to have to be one record at a time and however long it takes it going to be how long it takes. I don't see a way around this if you're moving data to a DBase IV database.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007