+ Start a Discussion
Stelian EpureStelian Epure 

Salesforce Slowdown during API pull

Hi,

When we pull a few months of data over the API, the older the data the slower the pull. We imagine there is some sort of caching on the database. Looks like the threshold is 3 months. If this is true, what can we do to make the pull faster? We usualy dowload 4-6 months worth  of data.

Thanks,
Stelian
buyan thyagarajanbuyan thyagarajan
Hi Stelian,
 I am not sure on how you are pulling the data with api like a real time call from an external application using webservice or a data loader pull. But here are some options for the data pull.
1. Certainly pulling a large amount of data in one shot is going to take too much time and resources. So you can create a daily apex batch or a call from your application which can pull data daily from salesforce and populate your database which would cut down the records load.
2. If the daily load does not work for you, create multiple batches of your application which can pull data from salesforce using date ranges like current month, last month etc and have the data loaded in your application.
Can you explain to me why you are querying a huge amount of data one time and how is the data pulled from salesforce currently?
Buyan
Stelian EpureStelian Epure

Hi Buyan,

We query Salesforce for 4 months worth of invoice line items.   Currently the request is broken into batches, by month Recently the later months are taking longer.  Since the beginning of October we have been recording the length of time it takes the request to complete and below are some metrics.  

                              Avg Invoice Line Items    Min Invoice Line Items    Max Invoice Line Item
Sept. Invoice Line Items        4590.25                922                    8230
August Invoice Line Items    11152.5                10233                12010
July Invoice Line Items        12916                12585                13183
June Invoice Line Items        14676                14535                14809

                                        Avg Time        Min Time        Max Time
Sept. Invoice Line Items        0:00:10        0:00:03        0:00:14
August Invoice Line Items    0:00:22        0:00:20        0:00:23
July Invoice Line Items        0:01:07        0:00:24        0:02:31
June Invoice Line Items        0:03:28        0:00:18        0:09:05

On average this year, we request 11k records every request and no more than 16k. 

These requests are made through SOAP web service calls.

buyan thyagarajanbuyan thyagarajan
Hi Stelian,
 Thanks for your reply. I see the response times and here are some possible solutions which you might want to try out.
1. I am not sure whether the invoice line item is a custom object and if so, you would want to contact salesforce and add a custom index to your date field which would help in speeding up the performance. The below link talks about this on how to do it.
https://developer.salesforce.com/blogs/engineering/2011/03/bulk-api-queries.html
2. I am also not sure whether  your external application uses any bulk query to do the query instead of soql. The below link talks about ways you can refine your query to have the right filter conditions which would impact it.
https://developer.salesforce.com/page/Webinar:_Inside_the_Force.com_Query_Optimizer_(2013-Apr)
3. The timing of the batch also plays a role. If your batch jobs run between 11 to 2 am Eastern time, they would have slowness considerably. 
Could you explain in what language the external application is written and how does it make calls? Is it a straight date call or does it have multiple joins with other objects? Let me know and i can have a conversation as well.. Please click like if you feel that i answered your question ..
Thanks
Buyan