VB.NET - OLEDB with Foxpro table
-
Hi! I'm trying to run a process that updates a zip/tax rate table. The new tax rates are in a flat file. the table I need to update is a FoxPro free table indexed in country and zipcode fields. Everything is working, but I have 45K+ records to update and after about 15% into the flat file the process it gets slooooow and gets slower and slower. If I comment out the code that does search and just leave the code that goes through the flat file it takes about 30 seconds with the looks up and update it takes 30+ minutes and I can see the progress bar miving for first 15% and than it dies.... I think I am missing something here... Here is what the code looks like: m_conn = New OleDbConnection m_conn.ConnectionString = "Provider=VFPOLEDB.1;" & _ "Data Source=" & g_app_options.processing_folder_path & "output\tax import;" & _ "Mode=ReadWrite|Share Deny None;" & _ "Collating Sequence=MACHINE;" m_conn.Open() Do While srTaxFile.EndOfStream = False ado_command = New OleDb.OleDbCommand("SELECT ZIPCODE FROM zip WHERE country='001' AND ZIPCODE='" & taxRecord.field(cTaxRecord.RecordFieldIDs.ZipCode).Trim & "'", m_conn) Dim sZipCode As String = ado_command.ExecuteScalar() ado_command = New OleDb.OleDbCommand("UPDATE zip SET itaxr = " & taxRecord.field(cTaxRecord.RecordFieldIDs.SalesTaxRate).Trim & " WHERE zipcode='" & taxRecord.field(cTaxRecord.RecordFieldIDs.ZipCode).Trim & "'", m_conn) ado_command.ExecuteNonQuery() set_progress(srTaxFile.BaseStream.Length, srTaxFile.BaseStream.Position) Loop Its pretty simple, and I don't understand why it takes that long. This is running on a local drive. If you can help or know what I'm missing, I would greatly appreciate it! Thank you ipokrov
-
Hi! I'm trying to run a process that updates a zip/tax rate table. The new tax rates are in a flat file. the table I need to update is a FoxPro free table indexed in country and zipcode fields. Everything is working, but I have 45K+ records to update and after about 15% into the flat file the process it gets slooooow and gets slower and slower. If I comment out the code that does search and just leave the code that goes through the flat file it takes about 30 seconds with the looks up and update it takes 30+ minutes and I can see the progress bar miving for first 15% and than it dies.... I think I am missing something here... Here is what the code looks like: m_conn = New OleDbConnection m_conn.ConnectionString = "Provider=VFPOLEDB.1;" & _ "Data Source=" & g_app_options.processing_folder_path & "output\tax import;" & _ "Mode=ReadWrite|Share Deny None;" & _ "Collating Sequence=MACHINE;" m_conn.Open() Do While srTaxFile.EndOfStream = False ado_command = New OleDb.OleDbCommand("SELECT ZIPCODE FROM zip WHERE country='001' AND ZIPCODE='" & taxRecord.field(cTaxRecord.RecordFieldIDs.ZipCode).Trim & "'", m_conn) Dim sZipCode As String = ado_command.ExecuteScalar() ado_command = New OleDb.OleDbCommand("UPDATE zip SET itaxr = " & taxRecord.field(cTaxRecord.RecordFieldIDs.SalesTaxRate).Trim & " WHERE zipcode='" & taxRecord.field(cTaxRecord.RecordFieldIDs.ZipCode).Trim & "'", m_conn) ado_command.ExecuteNonQuery() set_progress(srTaxFile.BaseStream.Length, srTaxFile.BaseStream.Position) Loop Its pretty simple, and I don't understand why it takes that long. This is running on a local drive. If you can help or know what I'm missing, I would greatly appreciate it! Thank you ipokrov
Sorry but are you trying to update a table based on values from a flat file? Also, why are you selecting first if you know what you want to update? You should also look at Transaction--this might make the process faster but I am sure there are better ways to do what you need to do. Can you please explain what you want to do, in plain English, without any code?
-
Sorry but are you trying to update a table based on values from a flat file? Also, why are you selecting first if you know what you want to update? You should also look at Transaction--this might make the process faster but I am sure there are better ways to do what you need to do. Can you please explain what you want to do, in plain English, without any code?
Hi, Sorry I was not clear on what I'm trying to do. In plain English, I have a comma delimited file with 45k+ records that contains zipcode and taxrate. I have a foxpro table that contains countrycode, zipcode and taxrate fields. Now I need to update each taxrate field in the table with value of taxrate field from the flat file. If the zipcode in taxrate table is not found I need to insert a new record. (that is why I have to lookup the zipcode first, I commented that code out and did not include it with my example.) Thank you ipokrov
-
Hi, Sorry I was not clear on what I'm trying to do. In plain English, I have a comma delimited file with 45k+ records that contains zipcode and taxrate. I have a foxpro table that contains countrycode, zipcode and taxrate fields. Now I need to update each taxrate field in the table with value of taxrate field from the flat file. If the zipcode in taxrate table is not found I need to insert a new record. (that is why I have to lookup the zipcode first, I commented that code out and did not include it with my example.) Thank you ipokrov
Create a datatable of all the zipcodes which are not found in the foxpro table. For update you can use something like http://sqlblogcasts.com/blogs/simons/archive/2006/10/04/SQL-Engine---BULK-UPDATE-command.aspx[^] For the insert from the datatable to database, use SqlBulkCopy class.
-
Hi, Sorry I was not clear on what I'm trying to do. In plain English, I have a comma delimited file with 45k+ records that contains zipcode and taxrate. I have a foxpro table that contains countrycode, zipcode and taxrate fields. Now I need to update each taxrate field in the table with value of taxrate field from the flat file. If the zipcode in taxrate table is not found I need to insert a new record. (that is why I have to lookup the zipcode first, I commented that code out and did not include it with my example.) Thank you ipokrov
FoxPro has indexing on it's tables using either IDX or CDX files, they'll have the same name as the .DBF file, but with a different extension; that is if there are any indexes on the table. Hopefully, there is an index on the zipcode field that can be used to speed up the look up queries. From my experience with OLEDB, it's generally faster to send as few commands as possible, so build a single query like:
select zipcode from zipcodes where zipcode in (... comma-separated list of zip codes ...)This query of course will perform much better if there is an index on the zipcode field. It will give you the list of zipcodes that exist and need to be UPDATEd and ones that aren't in the result set will be INSERTs.Keep It Simple Stupid! (KISS)