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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

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

Increase performance of oracle function from Data Services.


 
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: Mon Jul 13, 2015 12:51 pm 
Post subject: Increase performance of oracle function from Data Services.

I am using Data Services version 14.1

how can I increase performance of a oracle function call.

we are having 10 million records in source. which i try to call oracle function in data services it is taking around 2 hours to complete the job. oracle function is taking around 7 Millie seconds for each record in SQL developer.

I tried enabling parallel option on function in data store and increased degree of parallelism in data flow as 16 but it is still the same.

please let me know if there is any article pointing to increase performance.

Thanks
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Mon Jul 13, 2015 2:45 pm 
Post subject: Re: Increase performance of oracle function from Data Servi

It sounds like the Oracle function isn't getting pushed down to the database with the rest of the query. That's where the problem is.

If the function call is not part of the main query then what's happening is that for each row the Dataflow is connecting to Oracle, executing the function and then disconnecting. The connect/disconnect is very costly.

You may need to recode the Dataflow to use a SQL transform. That will ensure that the Oracle function call is always pushed down.

Is the Oracle function call imported into the Datastore? I think it would have to be for you to use it in the Dataflow.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
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.0270 seconds using 17 queries. (SQL 0.0026 Parse 0.0008 Other 0.0236)
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