DSXchange

Hi,

I am using an ODBC Enterprise in one of my job. I was wondering what might be the most effective value of "Fetch Array Size" so as to increase my job performance?
Or does this effective value is dependent on some factors?

It depends on a number of factors. As a starting value use then experiment.

Thanx Ray,

Can you explain what is an "network packet size"?
and how to calculate it?

You can't calculate it. It's a given. Your network transmits data in "packets". Talk to your network administrator about the concept. Typically packet size is 1KiB, 2KiB or 4KiB.

Don't you also want to factor in the buffer size of your stage?

I was lead to believe that you wanted your buff_size / row_length = how many rows you can fit in a stage buffer. Round down.

@Ray
The avg. row length of my table is 985KB (kilobytes). So according to your formula it comes out to be less than 1

@PaulVL
No, my question was for ODBC Enterprise only.
On second thoughts what if I keep more rows in the buffer than the default that is defined in a stage ? Will my job abort or it'll hamper my performance??

To my knowledge, you swap out to disk, thus a performance hit.

Check with your network administrator what the packet size is. Obviously such hugely wide rows will need multi-packet transmission. Don't worry about trying to tune Array Size yet - leave it at 1 (that's still 958KB of network traffic per row!). Work with your network administrator to achieve a better packet size.

1 MEG per row? interesting...

@Ray,
Will check and see how my performance is.

@PaulVL
We also have some tables which have avg. row length > 2.5 MB

Not really - more