Exporting entire database via API

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.