Performance Issue in ODBC Enterprise

[chandra.shekhar@tcs.com]

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?

[ray.wurlod]

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

Code: Select all

Int((network packet size) / (average record size))

then experiment.

[chandra.shekhar@tcs.com]

Thanx Ray,

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

[ray.wurlod]

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.

[PaulVL]

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.

[chandra.shekhar@tcs.com]

@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??

[PaulVL]

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

[ray.wurlod]

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.

[PaulVL]

1 MEG per row? interesting...

[chandra.shekhar@tcs.com]

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

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

[ray.wurlod]

Not really - more