BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Push Down SQL function


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
dammalapatisree
Forum Member
Forum Member



Joined: 03 Jul 2014

Posts: 29



PostPosted: Tue May 03, 2016 1:37 pm 
Post subject: Push Down SQL function

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
Back to top
aidelia_yahoo
Forum Member
Forum Member



Joined: 05 Jul 2013

Posts: 23


flag
PostPosted: Wed May 04, 2016 8:33 am 
Post subject: Re: Push Down SQL function

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
Back to top
dammalapatisree
Forum Member
Forum Member



Joined: 03 Jul 2014

Posts: 29



PostPosted: Wed May 04, 2016 8:43 am 
Post subject: Re: Push Down SQL function

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
Back to top
aidelia_yahoo
Forum Member
Forum Member



Joined: 05 Jul 2013

Posts: 23


flag
PostPosted: Wed May 04, 2016 8:59 am 
Post subject: Re: Push Down SQL function

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
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0282 seconds using 17 queries. (SQL 0.0024 Parse 0.0009 Other 0.0249)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo