1

We use the salesforce dataloader to do occasional exports of our entire database so we can use our local database tools to run analysis and data cleaning that we can't manage via reporting and selective extracts (we also don't have any in-house apex devs with any more bandwidth).

It's painfully slow at the moment, does anyone have any experience of using the Bulk data API to try and achieve the same thing (or perhaps that's what most dataloaders use anyway?).

I'd like to try and get an idea of if that's likely to be any faster, if there are any other implications to using the bulk API (speed, running out of requests etc?).

In terms of what we're downloading it's around 29 objects, totaling to many hundreds of thousands of records, our most recent extract runs to about 1.7 gig of .csv files...

I guess the way I'd go about doing it is by building queries using Sobject.describe global and then describe on each object to build a list of all headers then do some kind of SQOL request for where not null?

[Update]

On reviewing with colleagues it is not the data loader that is causing the hold up in our current workflow - it is the Weekly Export Service, which we now have to schedule for mid Sat in order to get it for Monday, we believe it is because of queues on our instance (NA8). I am exploring the Bulk API to see if it would be a superior alternative.

  • It would help to judge where the bottleneck currently is if you could share ballpark numbers for number of objects/records being exported, volume of data that ends up being transferred and time it takes at the moment – IvanR Feb 23 '15 at 11:34
  • Hi Ivan, Thanks will add some clarifying edits above – Huw Feb 24 '15 at 10:42
5

Proceeding with apex, describe and SOQL will make it slower and in my experience it will rather add new bottlenecks and problems than remove existing.

On thing you try is the weely export feautre to export everything. But depending on the size of all your data, this can also be very slow. Also it splits up the export in many zip files, which are hard to download.

I usually utilize an ETL-process with tools like TalenD for task like that. Possibly it's also slow if you export everything. At least it will automatically use bulk api.

One method which will really boost up the speed is to export only a "delta" instead ALL data. E.g. you could export only records where LastmodifiedDate is newer than your last export date.

  • Humm, I've written a small app to pull a client's record and associated objects using the Restforce gem, Ruby on Rails 4 and the Rest API. I guess what I was wondering is if I could write something myself to pull everything through the bulk API. As it is we schedule the dataloader over the weekend to be ready for Monday morning but it keeps taking longer and longer. Currently we trigger it on Saturdays and sometimes it's still not with us when we hit our desks first thing Monday... – Huw Feb 24 '15 at 10:49
  • Total copies will be slow. Try to figure out what is really changed (filter last modified) and reduce the transfer volume to that. My assumption is: 90% of your data is unchanged between your exports. Exporting it again and again and again is wasting your time. – Uwe Heim Feb 24 '15 at 11:49
  • I agree Uwe, but is there a fast way of running diff on data via API? Our records can be changed retrospectively over vast periods. I suppose we could just address modified dates after last export... – Huw Feb 24 '15 at 12:10
1

One option is the Data Extract Service you can run weekly, this is for periodic backups, but sounds a good fit. You still get cvs files, so you will need to process them into you local database. Good for Reporting, and a Backup!

You have not stated the local database used, but there are also lots of data replication tools/products that do this. Look on AppExchange.

  • I believe that is exactly that we're using... perhaps I've confused this with the dataloader... the problem being it's 24hours+ too slow. – Huw Feb 24 '15 at 11:09
0

I'm assuming you are talking about Salesforce.com Data Loader (standalone desktop app) - if so, here are instructions on how to enable Bulk API. Doing so will automatically tell Data Loader to split data in smaller batches and process them asynchronously.

Some other things to consider when working with large data volumes:

  • Internet connection - are you running the Data Loader on your local machine? You'd probably want to make sure it is running on a server with decent connection bandwidth
  • Do you really need to export "entire database"? It is likely that you don't need to export all records, but rather only the ones that were created/updated since the time of the last export/clean up

[UPDATE] If Data Loader is being scheduled from command line, this document lists parameters that can be set in properties file to configure Data Loader behaviour. In particular, you might be interested in setting sfdc.useBulkApi=true and sfdc.bulkApiSerialMode=false.

  • I IvanR, unfortunately we do need to download everything. Our team has lots of expertise in VBA and access and we need to perform some complex calculations and analysis from across nearly all the records along with data cleaning etc. As for where it runs from... perhaps i've misunderstood here, I believe the data extract is something we schedule from a local machine but is compiled and send from salesforce's servers... perhaps I've described it wrong here. – Huw Feb 24 '15 at 10:51
  • No, you got it right. I was just trying to point out that network connection can be a significant limiting factor in case of large volumes(and improving it can be a quick win) but knowing what your volumes are now I don't think it should take anywhere near 48h to fetch 2Gb of data. Also now that you mentioned you are scheduling data loader I have updated my answer with details on how to enable Bulk API in properties file. Let me know if turning it on will make any affect on export times. – IvanR Feb 24 '15 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.