DSXchange

there is some 290 million records in temp table. There is no primary key and index on this table.Right now i am using this table as look up.

I used this table as sparse look-up in ETL job,but the datastage job is still running(More than 15 hours)..

One of my friend is telling this is because of the temp table space,that is the reason the job is still showing running..

Please help me on this issue..

few issues here - a sparse lookup in PX means that each lookup will go to the database (a non-sparse lookup unloads the data into a dataset - which makes the lookups a lot quicker).

So sparse lookups are generally slower

also if you havent got a primary key or index on the table then the whole 290Million records will have to be scanned each time

How do you specify the lookup if your table doesn't have a key? As fridge says, you need to have an index of some kind. Without an index on the lookup key (whether or not it's a primary key) you need to perform a table scan for every row processed in your job. There is no way this can be efficient. Create an index on the search key column(s).

ask your dBA what query is running for this sparse lookup, and try to analyse that query, without index it must be doing full table scan for each row.

rather then asking your friend, you should have asked your DBA why is this query taking so long time.

the issues are resolved..

first we asked DBA to increase the size of the table..
then we loaded the records into the table..
we created the index on the table.. This took less amount of time (App 2 hours)..

Before that we created index on the table and tried inserting the records (290 million) into the table.. it took 3 days..

Now the Sparse lookup is working fine..
Thanks for the information...