[D365 F&O – Cdata provider performance issue.]

  • 19 July 2023
  • 3 replies

We are facing a performance issue while fetching the data from D365 F&O using CData provider. While fetching a data entity with around 700K records, It was taking around 1:20 mins, now when we execute it, its running for 10+ hours with no response.


While executing we noticed a SQL session with bulk insert command from TimeXtender with suspended state. and on the D365 database there are multiple session of batch process to fetch the data based on partition number. Since we are witnessed multiple sessions keeps executing, we would like to understand how the batch size is defined. And how we can customize the batch. Or any other way to set the connection properties which pull’s the data faster.

Query for Ref :

WHERE ((((((T2.PARTITION=5637144576) AND ((T2.PARTITION#2=5637144576) OR (T2.PARTITION#2 IS NULL))) AND (T2.PARTITION#3=5637144576)) 
AND (T2.PARTITION#4=5637144576)) AND (T2.PARTITION#5=5637144576)) AND (T2.PARTITION#6=5637144576)) )T1 WHERE ((T1.rowNumber>=@P1) AND (T1.rowNumber<@P2))

And we observed that every batch pulls top 1000 records





Best answer by rory.smith 21 July 2023, 16:51

View original

3 replies

Userlevel 5
Badge +7

Hi @Surendra ,


as far as I know there is a Batch Size parameter that defines the number of rows pulled across in one go. If you set that to 0 all data will be pulled in one, but may run into limits on the source. It may be useful to experiment with the batch size and set up a retry mechanism so that long runs (connection may be lost between TX and source) will be aborted and retried.


Userlevel 4
Badge +5

Hello @Surendra ,

I agree with Rory. Once I set the batch size to 0 and my VM was working like crazy. Trying to get all the data from 8 tables (because I set it to get 8 tables parallel) and then I wnet down due to RAM issues.

By default I set my batch size to 300.000. I've you got time to experiment see what happend when you take more or less, like @rory.smith suggested. 


Hope this helps

= Daniel

Userlevel 5
Badge +5

Hi @Surendra were you able to resolve the issue? If so can you please help us by marking the best answer above? If you have follow up questions please let us know