How to Fetch large number of records using QueryExpression.

(0) Share

Report

Posted on by Sabin Sharma

Background:

I need to retrieve more than 800K record from an entity. After the record is fetched I need to send the record back as CSV file to caller for download. QueryExpression can have 20 or more attributes.

Web Application using c#. Its an API that fetch record and sends CSV file as response.

Action:

I connect to CRM and use QueryExpression to fetch the record. I use pagination to fetch more than 5K records.

Issue:

It takes around 30 sec to fetch 5K records, so for approx 800K records it takes around 1.5hour. I would like to accelerate the process and decrease the time., Is there a way to do so?
When 500K records is fetched, I add it to the List. After certain number of records ( number not know but it's more then 300K records), I get error that reads "Failed to allocate a managed memory buffer of 67108864 bytes. The amount of available memory may be low." How can i resolve this issue? When there is huge number of records being fetched at once, can anyone provide insights on it.

I wanted to use Parallel For loop but the problem is, for pagination for QueryExpression, it depends on pagingcookies and pagenumber, so It is not feasible.

I would be very thankful if someone could provide some insights to resolve 2 issues.

All responses (3)

Answers (0)

Haseeb Jamshed 360 on at

Like (0)

Report

RE: How to Fetch large number of records using QueryExpression.

Yeah, I was thinking with respect to SSIS Perspective, However You can still use Point #2 for your work.

For Point # 1 - Have you tried to use Threading to process records?
Sabin Sharma 10 on at

Like (0)

Report

RE: How to Fetch large number of records using QueryExpression.

Hello haseeb,

Are you talking about balance distributor in SSIS? I am using web application and Language is c#.

Thank you for your insights, but it seems like you are talking about SSIS. Can you please provide more detail on your point 1 and 2 if it's not related with SSIS.
Haseeb Jamshed 360 on at

Like (0)

Report

RE: How to Fetch large number of records using QueryExpression.

Issue #1 - Use balance distributer.

Issue #2 -

1. Reduce CRM connection Batch Size.

2. Check and change default buffer size and defaultbufferrowsize

example: Set Default Buffer Size = Default size/4

suppose row size = 10k then change to 2500