I have a Sql Server table which I am updating from  VFP Tables. It curently has c 200,000 records and is taking about 2 hours to update.

The Sql table consists of about 20 fields from a VFP table, and an additional 10 fields, whcih are in effect lookup fiuelds to give descriptions from a series of VFP lookup tables.If I strip out the 'Lookup Code' bit, the update takes about 5 minutes, so I am fairly sure the bottleneck is in the way I am updating the descriptions, and womder if there is a quicker way to do this update.

I have the following code replicated 10 times for each of my lookup fields, within my main loop to update the main data

1. I set up an OleDbCommand with the connection string and a parameter for the lookup table

2. I then have a While Rdr.read loop to update a field with the description

Is there a quicker/better way to update these descripotions ?

Can you just do the join to a normalized table on the VFP side then import that table?



I didn't quite get how you are doing the "update" but to transfer data I'd suggest SSIS.



Still, if you have the option of doing your joins first and then just bringing it in as one table, no lookups you'll probably get that 5 minute speed.

Hi Charles

Initially I had considered using Joins, but its not a staight join in the sense that there is some additional code, once the join is done, like inserting extra text under certain conditions. One alternative I had considered was to use a ListArray. i.e. Initially store the lookup tables into a ListArray (there are only a few thousand Lookup records) and then do a ListArray.BinarySearch to insert the descriptions. I would have thought thios would be faster ,but I have abosolutely no experience of using ListArrays. (I used Arrays quite a lot in VFP and know they are extremely fast for lookups etc). I dont suppose there'a anything like a IIF(Seek) in .net ?

Hi Gerard,



I am sorry, but I don't quite understand what is it that you are trying to accomplish here, would you mind trying to be more explicit?



Are you using VFP & SQL tables all the time in this application, or is this one time import?



You said you are updating an SQL table from a VFP table which has 200,000 records, right?



Also it looks like that the problem is in getting the lookup descriptions which are in the VFP tables and at the time of the import it is taking a lot of time, is this right?



Assuming what I understood is correct, you may change your approach to import the records. It will help if you can post the code you are using in a text file here so we can see clearly what you are doing. I have done some imports from CSV data, and it was really slow using the regular BO.Add or just ADO, so I changed my approach to use SqlBulkCopy which inserted the records into the SQL table really fast, in seconds, then after I had the records in SQL I did the rest of the process which resulted in a faster process. Take a look in the forums using "sqlbulkcopy" and you will find my posts and others.



You could also do the following:

1.- Use the sqlbulkcopy approach to create a temporary SQL table with all the lookup data, also import your 200K records.

2.- Create a stored procedure to do the lookup assignments and any other updates needed.



Keep in mind that using VFP/ADO you will be processing one record at a time with a loop/scan, etc. while in MS-SQL with a stored procedure you can process a whole bunch of record in a single pass. In my case my process done in SF with BOs took more than an hour to import and do some calculations and updates, with the help of a MS-SQL MVP consultant he helped me create a stored procedure that now process 500k records in 1-10 minutes, of course I had to pay him but his fees are very competitive he being a MS-SQL MVP.

Edhy Rijo

Hi Efhi.

To give meore background.

This is an ongoing requirement..i.e a process that will be run every night, to get data from VFP Tables into a Sql Server Database, which then goes into a BI application.

Most of the data is in a few VFP Tables and these all go into one SQL Table. On this SQL Table, there are say 10 fields, which also need a description. e.g. Rep No (Which needs a description)  Stock Group(Which needs a description)  Area (Which needs a description) Account Category(Which needs a description) etc.. The Sql Table is currenlty at 200,00 records for a 3 month period but this will grow to 1.5 million for say a 2 year period.

I am happy with the main population of the SQl table (Which currently takes about 5 minutes) if I leave out the Descriptions.

I then have to go back over the file (or some other method) and populate each of the six description fields from 6 different VFP tables (Lookup Tables)

(The description population require manily a lookup from the VFP Lookup Tables but some additional processing also,  so I cannot easily use an outer join to these Description Tables)

As I will be doing a lot of this sort of stuff over the coming months, I wanted to get the most efficient way of doing this

I believe that using a stored procedure will be the way to go specialy if this is a process that need to be run more than once. Importing the lookup tables should take no time and will allow the SP to do it's magic.

Edhy Rijo

My vote is for SSIS. This is exactly the kind of stuff it was designed for and it is very configurable to find the best performance. Especially if you are using SQL2008 I think you will be very pleased to find how flexible your options are using SSIS. Very easy to debug as well.

Should also mention this is not "either-or" with a solution using SPs. SSIS lets you run any SQL queries or SPs as part of any of your transformation processes.

Charles and Edhy..thanks for your very informative replies.

(By the way, what is SSIS ?)

I have done a bit of experimenting and got the update,( inclusive of descriptions) down to about 10 mins for 200,000 records.

I had a look at Arrays, ArrayList but neither seemd to fit the bill. Then I came across  a thing called Dictionary, which allows you to store in a type of array,  Key and Value pairs, and then to do a lookup on these with one command. This seems to have worked extremely well in the limited testing I have done so far.

So what I have done is as follows:

1. Initailly, store all my Keys and Descriptions into  Dictionaries . This is an initial hit but only takes seconds as I am just storing 2 fields for each lookup

2. When populating my main Table, in order to get the descriptions, I just do a lookup on the in Memory dictionary. This means I do not have any overhead of going to disk  out and doing any selects etc...all thats needed is a lookup which seems to be very fast

Sounds like you have it working. Great.



SSIS is SQL Server Integration Services - the successor to DTS (Data Transformation Services) It is a whole thing built into the Business Intelligence Development Studio ( BIDS ) that is designed specifically for importing/exporting and transforming data. Very useful for importing text files, munging data of all kinds.

I have a pretty large package I've created that migrates and transforms a whole lot of SQL data from one database to another which we can run on client data when we migrate them to our .NET version from the VFP app (which used SQL data but used GUID keys etc)



Very powerful and worth looking into whether or not you use it on this project. Much more user friendly than DTS was and plays well with .NET.