Job Performance

[Juls]

Hi,

I have a pretty straight forward job that reads data out of source DRS (SQL server) based on some criteria and inserts records into two target DRS (SQL server) at the same time. As an error handler, I have a reject sequential files one with each target DRS. If for example there are duplicate records that can't be rejected, then they will be written to this reject file.

I have been looking for a way to make this process run faster. I increased the array and transaction sizes on target DRS stages. This dramatically decreased my processing time (which is what I was looking for) but it stopped the rejected records from being written into reject files. I can see that records were rejected in DS Director but they do not get written anywhere.
I called tech support and they said that I need to keep array and transaction sizes at 1 if I want reject file stage to work.

Is there any way to decrease my processing time and still have an exception handling?

Thanks,
Juls.

[roy]

Hi and welcome aboard ,
the reason you need array size set to 1 is that any other value loads bulks of rows each time.
the only thing I can think of right now would be to do the following:
after you complete the load to the taget table with array size to increase your perfomrance unload the key columns t a hash file, then run your source load file with a lookup to the hash file and take only records which you didn't find a coresponding entry in your hash file.
bare in mind that when you load more then 1 row, depending on your array size, some rows were not loaded due to any reason you might be actually losing this entire load and not just the bad rows.
so it would be better to prepare/cleans your data so no errors will occur during the load

IHTH,

[kcbland]

All databases have some ability to high-performance load data, some databases have some ability to high-performance extract data.

I recommend you explore using the appropriate utilities for high-performance loading in SQL-Server. The design you have described is the simplest one possible, but is often not the fastest. Using BCPload is one method for faster loads.