2

I have two objects Object1 and Object2 in Salesforce. These two objects doesn't have Master-Detail relationship. But they are related through a Text column X. I want to create many to many junction object Object3, which will link Master-Detail Object1 and Object2. I had two approaches in mind.

Approach 1:

  1. Create an Apex Job
  2. Loop through Object1
  3. Find all Object2 which matches with the field Object1.X = Object2.X
  4. Create a Junction Object Object3 with Object1,Object2 as Master-Detail relationship.

Limitations: I am working in Millions of records and there are many limitation in number of records retrieved, number of SOQL triggered and number of Batches that can be created per day.

Approach 2:

  1. Pull all the records from Object1 to our System using Bulk API query
  2. Pull all the records from Object2 to our System using Bulk API query
  3. Do a join in Mysql with field X as Join condition
  4. Create Junction objects with join on Object1.X = Object2.X using Bulk API

Limitations: Bulk API cannot retrieve CSV output greater than 1 GB.

I tried using Limit Offset to retrieve records in batches but Bulk API doesn't support offset queries.

Please help me understand which one is better and if there is any simpler approach to do the same.

Improve this question
5
  • how many gigs do you expect the csv will be (quering only the 2 relevant columns)? In order to get smaller csv files, you could create an arbitrary grouping of the data, e.g. if there's an auto-number field you could filter on the last digit(s) and do multiple queries. Or if there's a date field, you could group into year/months etc..
    – Guy Clairbois
    Oct 20, 2014 at 14:00
  • 2
    In approach 1 you call it an Apex job, but have you considered doing this as a Batch Job? It would be very similar to approach 1, just done in a batch class.
    – Chris Duncombe
    Oct 20, 2014 at 14:00
  • Is this just a one time thing or something you need to continually maintain as data comes into Object 1 and Object 2?
    – cricketlang
    Oct 22, 2014 at 23:36
  • @cricketlang FYI, I've converted your answer to a comment, these are the preferred way of asking clarifications. As soon as you reach 50 reputation you should be able to comment anywhere.
    – Samuel De Rycke
    Oct 23, 2014 at 7:58
  • Have you made those text fields unique and/or external ID's? If so, then you would have an index on them, and using batch should be okay and your queries will likely not be table scans. However the developer console will help you tune your queries.
    – Andrew Monshizadeh
    Oct 23, 2014 at 12:14

1 Answer 1

Reset to default
0

I have went ahead with Approach 2 and till now I have never hit X > 1GB limit in BULK API output CSV.

Its been almost 2 years since I asked this question, and here is my learning through the course of time.

  1. Try to enforce constraints as much as possible (Master-Detail,Lookup) during the design phase itself, which will in-turn simplify and provide clean data out of the box
  2. If the constraints are just free floating text (as in my case where I can't go back and redesign this legacy stuff), its always good to pull the data out, perform required mashup and use BULK API to reflect the data back in Salesforce
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .