HI,
How do i push the sql function on to data base for optimization. I have simple data flow where with a sql function being used with 1 million rows from source. currently job is taking 2 hrs to finish. how can i force the job to push function on to data base ?
Thanks in advance
dammalapatisree (BOB member since 2014-07-03)
Hi, I think your question is too broad to be tackle correctly.
Depending of your problem you can use a SQL transform, a pushdown() call, or even play with Join rank and cache in the sources tables. It could even be related to incorrect use of cache in lookups, if it used.
Every alternative has its pros and cons.
Could you be more specific in order to help? I.e. number of spurce tables involve, Join cluse used, if you use lookup call in the mappings, tec.
Regards,
Andrés
aidelia_yahoo 
(BOB member since 2013-07-05)
i am having 1 source table and a one query transform and target table.
I imported a function from data base(oracle 12c) into data store. In query transform i am calling that imported function. source has around 2 million records and job is taking 5 hrs to complete.
when i execute the same function in sql developer for 2 million records it is taking less than a minute to execute.
let me know if you need more information, thanks for the help
dammalapatisree (BOB member since 2014-07-03)
OK, in your place I would try to localize the problem.
First, yuo need to be sure that what is the sql that DS generate to extract the data, that can spot in Validation\Display Optimized SQL. You can also enable some trace flags (i’m not sure which one is related to SQL, but you an do trail an error till find the righjt one) when running the job in order to see the “real” SQL pushed to the DB.
If the query looks similar to the one you expect, the problem is not the pushdown and you have to check other thinks like extra steps in the DF, where is your DS engine and your targets, etc.
If the SQL generated is not correct, then you can replace the source table with a SQL transform and write the optimal query that you need, but the downside is the manteneability of the DF and the lost of traceability of the data.
Hope this helps,
Regards,
Andrés
aidelia_yahoo (BOB member since 2013-07-05)