Add New and Update Old Record SQL

I have a real simple Transformation. ODBC reader to SQL write. Only 4,420 records. Now I will also add this note: The SQL db for the writer is on a web host.

I emptied the SQL table and with Advanced ETL just Add all records. This was all processed in about 20 seconds (215 Rec/Sec).


I then had it just add New Records. The performance dropped!! It took nearly 7 minutes to perform at 11 Rec/Sec


What I really want to do is Add New and Update Old Records. The performance dropped even more. It took nearly 16 minutes to perform. That's 5 Rec/Sec. :evil: I really love this concept!! If I had 50,000 records to process, this would take nearly 3 hours. That is just unusable!! :sick:


Is there something I can do to help the performance of the SQL update? I normally set the update keys to the same as the primary keys.

Just as a thought, I suppose I could update a SQL table on the local server then transfer that to the web server. I have to ponder that a bit.

I ran it against a local SQL db as Add New and Update Old Record. The time improved to 87 Records per Sec. If I were working 50,000 records, it would take 9.5 minutes. :woohoo:


So bottom line, it's the connection to the web db.

1) "This was all processed in about 20 seconds (215 Rec/Sec)."

For loading data it is actually very slow.
It does 50k+ records per second on my laptop.
What connection do you use? Is it SQL Server Connection or OleDB?.

2) Add New and Update Old Records is expected to be slow.
For every record it has to run 2 SQL statements.
One to check if record exists (using count)
One to actually update/insert record.

Quite often the fastest option is to load data into temporary table and use
SQL After to update existing records and insert new ones

admin wrote: 1) "This was all processed in about 20 seconds (215 Rec/Sec)."

For loading data it is actually very slow.
It does 50k+ records per second on my laptop.
What connection do you use? Is it SQL Server Connection or OleDB?.

:ohmy:
I have not tried or considered OLE. Can I expect an improvement.?

One other bottleneck of this ETL is the ODBC source. No matter what tool or report writer we use, we always notice a performance issue on this particular ODBC source.

I was talking about target connection.
OleDB is the slowest way of loading the data.

Please post a screen-shot of your target(writer) connection