Datastage Sparse look up on varchar fields.

[anjan.k]

Hi ,
We have one job ,it is designed such away that to fed in coming data volume is less.But all of sudden due to new requirements,data volume is coming very high twice in week and job is running very long .

design

dataset-> sparse look up-> transformer-> table

here the sparse look is with the table and the query is to pull the two fields that is

select A,B from C where A=Orchestrate.D

here A and D are varchar fields

and want to pass the D also to the next stage transformer.Since they want both fields ,not able to use join and both are VARCHAR fields.

In normal scenario data will be less than 1000 records and completes fine with in 10 min,but when we get 150k to 300k it is taking 7 to 15 hours to complete.Can you please help me out changing the job design

[ray.wurlod]

Sparse lookups are notoriously slow (compared to other methods) because of the overheads involved (separate single-row queries against the database server, possibly exacerbated by network latency issues).

Have you considered using a Join stage?

Another possiblity is loading all the keys into a temporary table in the reference data source and joining that to the reference table.

[anjan.k]

Thanks Ray for the reply..As per my design that i mentioned above ,can be changed to any other design that can run faster?

[ray.wurlod]

Possibly yes, as mentioned in my post.

Premium membership is not expensive, at less than 30c (Rs12) per day. It is one of the ways that the hosting and bandwidth costs incurred by DSXchange are defrayed.