Execute multiple FetchXML and OData queries using Batch request

By | August 23, 2019

We have developed an application to fulfill one of our clients’ requirement. In that application we have perform multiple retrieval operation from CRM (around 10) either using FetchXML or OData of different entities. So, when we retrieve records from Dynamics 365 CRM of different entities for each retrieve operation it takes around 300 milliseconds and thus our application takes time to load and users have to wait for application to load. For this, our clients reported that the application is slow and taking time to load and operate, so ultimately it takes time in displaying result to the users. As it is, quick turnaround time is essential for a business. Therefore, to improve the performance, we worked on it and found that most of the time it was taking was to retrieve records from Dynamics CRM as we perform multiple retrieval operation. To reduce this retrieval time, we have used Batch request and retrieved multiple records using single request instead of multiple requests thereby reducing significant amount of time.

Below is the JavaScript code used for same.

We have created this function to execute four retrieval requests in single instance to retrieve records from Dynamics CRM instead of four different requests.

//This function is used to read batch records from CRM

var readRecordsUsingBatchRequest = function (entityType, webresourceName) {

var functionName = “readRecordsUsingBatchRequest “;

var batchReq = [];

var team = “Sales”;

var userId = “”;

var languagecode = “1033”;

try {

//get the userid

userId = getUserId().replace(“{“, “”).replace(“}”, “”);

//Read system user    

fetchXml = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>” +

“<entity name=’systemuser’>” +

“<attribute name=’fullname’ />” +

“<attribute name=’businessunitid’ />” +

“<attribute name=’systemuserid’ />” +

“<link-entity name=’teammembership’ from=’systemuserid’ to=’systemuserid’ visible=’false’ intersect=’true’>” +

“<link-entity name=’team’ from=’teamid’ to=’teamid’ alias=’team’>” +

“<attribute name=’name’ />” +

“<attribute name=’teamid’ />” +

“<filter type=’and’>” +

“<condition attribute=’name’ operator=’eq’ value='” + team.toLowerCase() + “‘ />” +

“</filter>” +

“</link-entity>” +

“</link-entity>” +

“</entity>” +

“</fetch>”;

var req = {};

req.Query = fetchXml;

req.IsFetchxml = true;

req.EntitysetName = “systemusers”;

batchReq.push(req);

//Read Language webresource

var req = {};

webresourceName = “new_LanguageLables_” + languagecode + “.xml”;

req.Query = “?$select=webresourceid,content&$filter=name eq ‘” + webresourceName + “‘ “;

req.EntitysetName = “webresourceset”;

batchReq.push(req);

//Read usage (custom entity) records

fetchxml = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>” +

“<entity name=’new_usage’>” +

“<attribute name=’new_usageid’ />” +

“<attribute name=’new_currentcount’ />” +

“<attribute name=’new_allowedlimit’ />” +

“<filter type=’and’>” +

“<condition attribute=’statecode’ operator=’eq’ value=’0′ />” +

“</filter>” +

“</entity>” +

“</fetch>”;

var req = {};

req.Query = fetchxml;

req.IsFetchxml = true;

req.EntitysetName = “new_usages”;

batchReq.push(req);

//Read System views

var req = {};

req.Query = “?$select=name,fetchxml,savedqueryid,layoutxml&$filter=querytype eq 0 and isquickfindquery eq false and statecode eq 0 and returnedtypecode eq  ‘” + entityType + “‘”;

req.EntitysetName = “savedqueries”;

batchReq.push(req);

//retrieve using batch request

WebApiLib.batchRequestRetrieve(batchReq, function (results) {

readRecordsUsingBatchRequestCallback(result)

}, readRecordsUsingBatchRequestErrorCallBack);

} catch (e) {

showError(functionName + ” ” + e.message);

}

}

var readRecordsUsingBatchRequestCallback = function (error) {

var functionName = “readRecordsUsingBatchRequestCallback “;

try {

//Perform the required logic

} catch (e) {

showError(functionName + ” ” + e.message);

}

}

var readRecordsUsingBatchRequestErrorCallBack = function (error) {

var functionName = “readRecordsUsingBatchRequestCallback “;

try {

showError(functionName + ” ” + e.message);

} catch (e) {

}

}

In WebApi library we have written below function to execute all the above queries to retrieve records using Ajax request. When we execute the request then it gives results in following format.

Execute multiple FetchXML and OData queries using Batch request

Now, our next challenge is to parse above response and get the results in JSON format.

To parse the above response first we split this result using “batch” word and slice it as shown in below.

data.split(“–batch”).slice(1, reqCount + 1);

Once we split, we get the array then looping through that array get the result of individual using

result = value.substring(value.indexOf(‘{‘), value.lastIndexOf(‘}’) + 1);

And finally parse that result to get it JSON format using following line of code.

 JSON.parse(result)

//Retrieval function in web api library

batchRequestRetrieve: function (requests, batchRequestRetrieveSuccess, batchRequestRetrieveError) {

var reqColl = [];

var reqCount = 0;

try {

reqCount = requests.length;

//encode the fetchxml

$.each(requests, function (index, req) {

reqColl.push(‘–batch_fetch’);

reqColl.push(‘Content-Type: application/http’);

reqColl.push(‘Content-Transfer-Encoding: binary’);

reqColl.push(”);

if (req.IsFetchxml != undefined && req.IsFetchxml == true) {

reqColl.push(‘GET ‘ + getWebAPIPath() + req.EntitysetName + ‘?fetchXml=’ + req.Query + ‘ HTTP/1.1’);

}

else if (req.FunctionName != undefined) {

reqColl.push(‘GET ‘ + getWebAPIPath() + req.FunctionName + ‘ HTTP/1.1’);

}

else {

reqColl.push(‘GET ‘ + getWebAPIPath() + req.EntitysetName + req.Query + ‘ HTTP/1.1’);

}

reqColl.push(‘Content-Type: application/json’);

reqColl.push(‘OData-Version: 4.0’);

reqColl.push(‘OData-MaxVersion: 4.0’);

reqColl.push(‘Prefer: odata.include-annotations=*’);

reqColl.push(”);

reqColl.push(‘{}’);

});

reqColl.push(‘–batch_fetch–‘);

var mainReq = reqColl.join(‘\r\n’);

//create AJAX request

$.ajax({

type: “POST”,

contentType: “application/json; charset=utf-8”,

datatype: “json”,

async: true,

url: this.getWebAPIPath() + “$batch”,

data: mainReq,

beforeSend: function (xhr) {

//Specifying this header ensures that the results will be returned as JSON.

xhr.setRequestHeader(“Accept”, “application/json”);

xhr.setRequestHeader(“OData-MaxVersion”, “4.0”);

xhr.setRequestHeader(“OData-Version”, “4.0”);

xhr.setRequestHeader(“Prefer”, “odata.include-annotations=*”);

xhr.setRequestHeader(“Content-Type”, “multipart/mixed;boundary=batch_fetch”);

},

success: function (data, textStatus, xhr) {

var results = data.split(“–batch”).slice(1, reqCount + 1);

var recCollection = [];

$.each(results, function (index, data) {

var result = getFormattedData(data);

if (JSON.parse(result).value != undefined) {

recCollection.push(JSON.parse(result).value);

}

else {

recCollection.push(JSON.parse(result));

}

});

batchRequestRetrieveSuccess(recCollection);

},

error: function (xhr, textStatus, errorThrown) {

batchRequestRetrieveError(xhr);

}

});

} catch (e) {

throw new Error(e);

}

}

getWebAPIPath: function () {

return this.getClientUrl() + “/api/data/v9.1/”;

}

getFormattedData: function (value) {

return value.substring(value.indexOf(‘{‘), value.lastIndexOf(‘}’) + 1);

}

Conclusion

With the help of Batch request we can execute multiple Fetch and OData queries to get the results. This increases the performance and reduces the retrieval time.

Leave a Reply

Your email address will not be published. Required fields are marked *