Perfomance handling
Moderators: chulett, rschirm, roy
Perfomance handling
Hi All,
I need to start coding for a job where it has 1 source and the source data needs to be looked up against 4 tables (say A,B,C,D).
The source record count is 10 million+ records
Lookup 1 (A) - 13000+ records
Lookup 2 (B) - 15 million+ records
Lookup 3 (C) - 500000+ records
Lookup 4 (D) - 36 million+ records.
I'm not allowed to create any indexes on tables.
Please help me on how to handle such a huge data with out affecting the perfomance.
I need to start coding for a job where it has 1 source and the source data needs to be looked up against 4 tables (say A,B,C,D).
The source record count is 10 million+ records
Lookup 1 (A) - 13000+ records
Lookup 2 (B) - 15 million+ records
Lookup 3 (C) - 500000+ records
Lookup 4 (D) - 36 million+ records.
I'm not allowed to create any indexes on tables.
Please help me on how to handle such a huge data with out affecting the perfomance.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Most database tables have an index on the primary key. If this is the case then sparse lookups will perform reasonably well if the database server is on the same machine as the DataStage server.
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.
Hi Ray,ray.wurlod wrote:Most database tables have an index on the primary key. If this is the case then sparse lookups will perform reasonably well if the database server is on the same machine as the DataStage server. ...
Please correct if I am wrong...
Is sparse lookup applicable in this case ,theritically a sparse lookup is applicable if source : lookup is 1:100 typically ,ie if source is reasonably low,in this example two look up contains few thousands records while source is 10 m.
is not join stage a option here ?
Rumu
IT Consultant
IT Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Everything is an option. Don't be tied down by what are only guidelines. Be prepared to think about the actual mechanism to make these decisions. For example, if the OP has vast amounts of memory available, then a normal lookup may even be possible. 15 million short rows is not such a great burden.
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.
Still,Sparse lookup is expensive since it executes the SQL qry for each incoming row....ray.wurlod wrote:Everything is an option. Don't be tied down by what are only guidelines. Be prepared to think about the actual mechanism to make these decisions. For example, if the OP has vast amounts of memory av ...
Rumu
IT Consultant
IT Consultant
Still,Sparse lookup is expensive since it executes the SQL qry for each incoming row....ray.wurlod wrote:Everything is an option. Don't be tied down by what are only guidelines. Be prepared to think about the actual mechanism to make these decisions. For example, if the OP has vast amounts of memory av ...
Last edited by rumu on Wed Jul 21, 2010 6:21 am, edited 2 times in total.
Rumu
IT Consultant
IT Consultant
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: