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

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

How to retrieve limited data on a multi join?


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
koolhyd
Forum Member
Forum Member



Joined: 23 Oct 2019

Posts: 1



PostPosted: Wed Oct 23, 2019 9:10 pm 
Post subject: How to retrieve limited data on a multi join?

Hello ,
I am basically trying to retrieve data from salesforce and build a csv based on certain criteria. But then way i designed it takes a ridiculously long time as all the tables are 1M+ rows. But the query that i can run directly in sql server (which has a copy of the SF database) takes less than 5 secs.

the query i intend to design in DS is..
select A.id, A.name, b.lift, c.qty, c.name from table1 A, table2 B left outer join table3 on b.id = c.name
where a.type = 'Helsinki'
and a.createddate > getdate()-5

in my DS, the end result is correct, but every query transform is retrieving the entire table and its taking alteast an hour to run the job.

Please advise on how to retireve data where i can get the DS to query the database directly, rather than retrieve the entire table and then perform the filters ?
Back to top
Staineran
Forum Member
Forum Member



Joined: 05 Aug 2020

Posts: 1



PostPosted: Wed Aug 05, 2020 4:37 am 
Post subject: Re: How to retrieve limited data on a multi join?

Hello!
Please tell me, did you use standard extraction methods or did you compose it yourself? Perhaps you have forgotten a point, check yourself using the salesforce implementation checklist. Possibly a problem with early setup.
Back to top
AlanDolan
Forum Member
Forum Member



Joined: 07 Aug 2020

Posts: 2



PostPosted: Fri Aug 07, 2020 4:39 am 
Post subject: Re: How to retrieve limited data on a multi join?

koolhyd wrote:
Hello ,
I am basically trying to retrieve data from salesforce and build a csv based on certain criteria. But then way i designed it takes a ridiculously long time as all the tables are 1M+ rows. But the query that i can run directly in sql server (which has a copy of the SF database) takes less than 5 secs.

the query i intend to design in DS is..
select A.id, A.name, b.lift, c.qty, c.name from table1 A, table2 B left outer join table3 on b.id = c.name
where a.type = 'Helsinki'
and a.createddate > getdate()-5

in my DS, the end result is correct, but every query transform is retrieving the entire table and its taking alteast an hour to run the job.

Please advise on how to retireve data where i can get the DS to query the database directly, rather than retrieve the entire table and then perform the filters ?


I had very similar issues, and my best decision was to find a salesforce consultant. And he helped me a lot.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

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

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0279 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0242)
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