ETL and Data Integration tools - Modern ETL software for everyone

Good Afternoon,

We have some jobs that are exporting data from Fox Pro into MySQL and SQL Server 2K5. When we transfer data we are currently transferring everything
every time and doing an update if a record already exists. The problem with this is that out of 100 records, only 5 may have been updated, but the jobs updates all 100 even though not all records have been updated since last job run.

Is there a strategic way in Visual Importer to code it in such a way that it will only retrieve and update those records which have been updated since the job last ran? Can you provide some examples on how to do this in Visual Importer?

Some of our tables have over 100,000 rows. It is time consuming to run the job as is since is updating all records regardless whether it was updated or not. These jobs are taking an average of 4 minutes and we would like to change our logic to reduce execution time to seconds if possible.

Please advise.
Thank you.
Ian

Hi.

We execute separate update statement for every record.
If you got very large table it could be very slow.
There is no other way of doing it.

In your case it would be better create separate table, load data into it and write update statement using sql after.

If you have timestamp field in source and target table you may also update data changed since last load

Mike

Thank you for the quick reply. Can you elaborate a little more on how the SQL After works? I’m not sure I understand what you are suggesting.

Ian

Hi.

Here is the example

Say You have following table in your database

create table CUSTOMER
(CUSTOMER_ID INTEGER, - Primary Key
CUSTOMER_NAME,
VARIOUS FIElDS
LAST_MODIFICATION_DATE
)

1 Source file/table has same structure
2 Target table is very big and when you run updates it takes a lot of time to complete

My suggestion is to create another table

create table CUSTOMER_TMP
(CUSTOMER_ID INTEGER, - Primary Key
CUSTOMER_NAME,
VARIOUS FIElDS
LAST_MODIFICATION_DATE
)

and use following sequence

truncate table CUSTOMER_TMP <- Sql Before

load data into CUSTOMER

update CUSTOMER
set CUSTOMER.customer_name=CUSTOMER.customer_name
VARIOUS FIElDS
FROM CUSTOMER_TMP
WHERE CUSTOMER_TMP.customer_id=CUSTOMER.customer_id and
CUSTOMER_TMP.LAST_MODIFICATION_DATE>CUSTOMER.LAST_MODIFICATION_DATE

For SQL After

Update statement will update only old records

I can email you screenshots if it is still not very clear

Sorry for late reply,
Mike