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
How to improove performance through odbc stage
Moderators: chulett, rschirm, roy
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.