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 ?


koolhyd (BOB member since 2019-10-24)

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.


Staineran (BOB member since 2020-08-05)

I had very similar issues, and my best decision was to find a salesforce consultant. And he helped me a lot.


AlanDolan (BOB member since 2020-08-07)

In these cases you can use the SQL Transform, using the SQL sentence and is not necessary to retrieve the source table.