peformance issues

dataservices performance
1

Can you please suggest me the simple way to solve the below issue

We load the stagging data in db1 and db2 .Using this data we ill update the dimesnion data in db3 .

The targets are residing in database db4. I join db1 and db2 and get the dimension keys using the look-ups and populated the data.
it took 5 hrs to populate for 1 mil records

then I have tried using another option that is I pulled all the stage data to db3(since sql function reside in db3) and get the dimension keys using sql function in sql transform , which took 30 min to load for the 1 mil records

the issue team is insisting to use the SQL function so my questions is other than this way is there any faster approach we have to get the best response i.e avoiding the sql functions ?

Thanks for the help

Thanks

ora.question (BOB member since 2009-06-20)

2

What is this sql function doing? I assume you mean a Oracle stored function by that.
How was that function used in DS?

What I would do is setting up database links, import the stored function, use it as mapping and then try to pushdown as much of the processing as possible. Or do not use the stored function but a lookup_ext instead as a new function call.

If you post some more information and screenshots we can try to help you there.

Werner Daehn :de: (BOB member since 2004-12-17)