How to improove performance through odbc stage

venkat n · Post by **venkat n** » Mon Sep 05, 2005 7:43 am

When we are trying loading a sql server table by using ODBC with two reference lookups( which contains 100 rows and 5 coloumns) that job performance is very low nearly 2 rows per sec.We are using datastage version 7.1
How to improove the performance and is there any otherway to connect to SQL server database instead of ODBC but we are supposed to connect with ODBC. Pleae help me how to improvve performance

thanks in advance
N.V.krishna

chulett · Post by **chulett** » Mon Sep 05, 2005 8:08 am

Unfortunately, 'performance' is a relative term and you haven't given us alot of information about what you are doing to go on.

First question would be related to the reference lookups - are they using ODBC as well? If so, first order of business would be to convert them into hashed file lookups.

It also depends on what action you are taking in your target - inserts, updates, a mixture? What is your Array Size and Commit level? With 'fat' rows or tables with an inordinate number of indexes, making a load job perform 'better' can be a little more difficult.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 05, 2005 4:11 pm

Do there really need to be five columns in the lookup?

If you are simply determining existence, then a key-only lookup would suffice. If you are using only one returned column, then just the key and that column are all you need.

venkat n · Post by **venkat n** » Tue Sep 06, 2005 2:46 am

Hi actually we tried with only one key coloumn there is no diff in the perfprmance
we are using hashed file reference lookups only.
In odbc stage we have parameter array size check box in that we gave 1 when we are trying to give more than 10 that will giving an error.

Thanks in advance
Venkat