Speed issue while fetch data with fetch expression

[amanjlucid]

when i am using fetch expression to get data, I am getting very slow response. It take around 12-13 sec and in some cases it goes up to 20 sec to get response. Can you please suggest me what can i do to improve speed while getting data from crm with fetch expression.

[georged]

Without looking at the fetch you're using I can't really tell. One thing for sure - the toolkit is very lean.
Have you tried running your fetchxml using https://www.fetchxmlbuilder.com? What's the timing on that one?

[amanjlucid]

I using below fetch expression -

  one more thing i am not using cachePool property in onlineSettings, does using cachePool will help to improve speed.

[georged]

Cache only matters for subsequent requests.
You're not paging so if you have a lot of records that might take a while.
Did you measure your fetch performance in the tool I pointed?

[amanjlucid]

Yes I have check that and i am getting response in 0.34 sec

[georged]

I really doubt it's the toolkit. I bet it's something to do with the network.
Add a logger to your connection as described in https://github.com/AlexaCRM/dynamics-webapi-toolkit/wiki/Tutorial#settings to get the timing around requests. Logging would allow you to pinpoint where the time is spent.

[xaviervillafuerte]

I have exactly the same problem described by amanjlucid. I enabled the logger and I can see the second operation (retrieve metadata) is taking almost 1.5 minutes (GUID and server name has been removed per security):

2021-10-11 03:48:50.958062 [debug] [events] [pid:17060] Retrieved a new access token via https://login.microsoftonline.com/xxxxxxxxxxxxxxxxxxx/oauth2/token {} {}
2021-10-11 03:50:07.417655 [debug] [events] [pid:17060] Retrieved OData metadata via https://xxxxxxx.crm2.dynamics.com/api/data/v9.1/$metadata {} {}

I am initializing the client this way:

#initialize settings
echo PHP_EOL . 'Establishing connection and log settings' . PHP_EOL;
$settings = new \AlexaCRM\WebAPI\OData\OnlineSettings();
$settings->instanceURI = $configs->instanceURI;
$settings->applicationID = $configs->applicationID;
$settings->applicationSecret = $configs->applicationSecret;
$settings->apiVersion = '9.1';
$settings->setLogger( $logger );

#Initialize WebAPI Objects
$middleware = new \AlexaCRM\WebAPI\OData\OnlineAuthMiddleware( $settings );
$odataClient = new \AlexaCRM\WebAPI\OData\Client( $settings, $middleware );
$client = new \AlexaCRM\WebAPI\Client( $odataClient );

The fetch xml query is this:

The same query using Fetch XML Builder from Xrm Toolbox took 2 seconds. Based on the logger detail, the problem is not the query but the fact there is some kind of metadata download initiated by the client that it is taking a lot of time. Access to Dynamics 365 is working fine and forms load fast.

Any guidance on how to avoid this delay will be much appreciated.

Xavier

[georged]

Hi Xavier,

can you time the metadata download directly from your browser? Open Dynamics 365 url first to get authenticated then get /api/data/v9.1/$metadata

Thanks
George

[xaviervillafuerte]

@georged thanks for your quick response. It took 3.4s to download 7.70 MB.

[georged]

@xaviervillafuerte

in our request we're not doing anything different. Metadata request is a one-off and is cached if you provide the cache implementation. If connection from your hosting server is as good, the (one-off) overhead is simply 3.5s during the start up. I'm not sure why it's taking that long, if you can run and debug code locally, I suggest you step through and identify the bottleneck.

[xaviervillafuerte]

@georged
I included a couple of logs in my code (yellow text in the image below) and as you can see the metadata delay occurs when I call your RetrieveMultiple method:

My code is pretty straight forward:
$fetchExpression = new \AlexaCRM\Xrm\Query\FetchExpression( $fetchXML );
$logger->info('GetCaseList',['Starting Retrieve Multiple']);
$collection = $client->RetrieveMultiple( $fetchExpression );
$logger->info('GetCaseList',['Finishing Retrieve Multiple']);

The fetch expression is the same I posted yesterday.

Is there any recommended configuration in php.ini that I must take into consideration so requesting the metadata could run with a good performance? Any other idea?

[georged]

@xaviervillafuerte

there is obviously a performance penalty associated with the full metadata retrieval. In your case the direct request takes 3.5s but request from the web server is over a minute. I'd say it's the issue "between your hosting server and crm2 instance". To get more details you'd need to add some logging for Guzzler that we use (see https://stackoverflow.com/questions/32681165/how-do-you-log-all-api-calls-using-guzzle-6) but honestly I don't expect any additional information besides "the connection is not very fast and the latency is high".

Generally speaking, we retrieve metadata so that we can suss out some details like collection name, for example. If you are after a thin client, you don't have to do that. Use OData client directly that is a very thin wrapper around web api. You can use getList method, for example. Obviously you'd need to a) know the details like collection name, b) would have to parse the results yourself and c) know how to deal with pagination.

If your cache provider is set up correctly, metadata is a one-off hit and that's why we accept it "as is". If hosting server is close to the instance region, typical full request is 3-5 seconds which is a startup only penalty. Technically, we can fine-tune the metadata requests so that they are granular but it's not very high on the list of the priorities, to be honest.

HTH
George