DataTablesResponse.Create() takes ~10 secs

[stefanoberetta]

Hi!

Beautiful package, but I was wondering if it's normal that the instruction
var response = DataTablesResponse.Create(request, data.Count(), filteredData.Count(), dataPage);
takes about 10 seconds to be executed with a total of 30 records processed.

All the previous instruction are executed with a normal timing.

I'm using it into an Umbraco (CMS) Surface Controller (it's just an MCV controller with some utilities for the CMS, nothing that compromises the speed of the code execution).
At the bottom there's my controller.

P.S.: I also tried to put the code inside a "normal" MVC Controller action, but the timing is the same.

Thank you :D

public ActionResult ADM(IDataTablesRequest request)
        {
            
            string stateFilter = string.Empty;
            string intExtFilter = string.Empty;
            string evAvvFilter = string.Empty;
            string sitSpecFilter = string.Empty;
            string tipologyFilter = string.Empty;
            string productFilter = string.Empty;
            string datesFilter = string.Empty;
            DateTime start = DateTime.MinValue;
            DateTime end = DateTime.MinValue;
            CultureInfo cc = null;
            if (request.AdditionalParameters != null && request.AdditionalParameters.Any())
            {
                var clt = request.AdditionalParameters.FirstOrDefault(x => x.Key == "culture");
                var stateFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "rState");
                var intExtFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "intExt");
                var evAvvFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "evAvv");
                var sitSpecFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "sitSpec");
                var tipologyFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "typology");
                var productFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "product");
                var datesFilterParam = request.AdditionalParameters.FirstOrDefault(x => x.Key == "dates");


                stateFilter = stateFilterParam.Value != null ? stateFilterParam.Value.ToString() : stateFilter;
                intExtFilter = intExtFilterParam.Value != null ? intExtFilterParam.Value.ToString() : intExtFilter;
                evAvvFilter = evAvvFilterParam.Value != null ? evAvvFilterParam.Value.ToString() : evAvvFilter;
                sitSpecFilter = sitSpecFilterParam.Value != null ? sitSpecFilterParam.Value.ToString() : sitSpecFilter;
                tipologyFilter = tipologyFilterParam.Value != null ? tipologyFilterParam.Value.ToString() : tipologyFilter;
                productFilter = productFilterParam.Value != null ? productFilterParam.Value.ToString() : productFilter;
                datesFilter = datesFilterParam.Value != null ? datesFilterParam.Value.ToString() : datesFilter;

                if (clt.Value != null)
                {
                    System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo(clt.Value.ToString());
                    cc = new CultureInfo(clt.Value.ToString());
                }
            }


            var currentMember = Members.GetCurrentMember();
            var dNode = Umbraco.TypedContent(ConfigurationHelper.Content("nodes.requests.parent").Id);
            IEnumerable<DataTableADMINRequestModel> data = null;

            //ADMIN BASED FILTERING
            var adminCat = HelperMethods.GetAdminCategory(currentMember.Id);

            data = Umbraco.TypedContent(dNode.Id)
                .Children(x =>
                {
                    var ty = x as Request;
                    var memberCat = HelperMethods.GetMemberCategory(ty.Applicant.Id);
                    if (adminCat.Contains("GOD"))
                    {
                        return true;
                    }
                    else if(adminCat.Contains(memberCat))
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                    

                })
                .Select(x => new DataTableADMINRequestModel(x));



            // Global filtering.
            // Filter is being manually applied due to in-memmory (IEnumerable) data.
            // If you want something rather easier, check IEnumerableExtensions Sample.
            var orderColums = request.Columns.Where(x => x.Sort != null);
            var orderedData = data.AsQueryable().OrderBy(orderColums);

            var filteredData = String.IsNullOrWhiteSpace(request.Search.Value) ?
                orderedData :
                orderedData.Where(_item =>
                    _item.Articles.InvariantContains(request.Search.Value) ||
                    _item.TitleOrResponse.InvariantContains(request.Search.Value) ||
                    _item.SubmissionDate.ToString(CultureInfo.InvariantCulture).InvariantContains(request.Search.Value) ||
                    _item.InternalOrExternal.InvariantContains(request.Search.Value) ||
                    _item.Applicant.InvariantContains(request.Search.Value) ||
                    _item.Status.InvariantContains(request.Search.Value) ||
                    _item.ClosingDate.GetValueOrDefault().ToString(CultureInfo.InvariantCulture).InvariantContains(request.Search.Value) ||
                    _item.RequestType.InvariantContains(request.Search.Value) ||
                    _item.Drug.InvariantContains(request.Search.Value) ||
                    _item.AdverseReaction.InvariantContains(request.Search.Value) ||
                    _item.Drug.InvariantContains(request.Search.Value));
            if (!string.IsNullOrWhiteSpace(stateFilter))
            {
                filteredData = filteredData.Where(x => x.IsClosed == (stateFilter == "2"));
            }
            if (!string.IsNullOrWhiteSpace(intExtFilter))
            {
                filteredData = filteredData.Where(x => x.InternalOrExternal.InvariantEquals(Umbraco.GetDictionaryValue(intExtFilter)));
            }
            if (!string.IsNullOrWhiteSpace(evAvvFilter))
            {
                filteredData = filteredData.Where(x =>x.AdverseReaction.InvariantEquals(Umbraco.GetDictionaryValue(evAvvFilter)));
            }
            if (!string.IsNullOrWhiteSpace(sitSpecFilter))
            {
                filteredData = filteredData.Where(x => x.IsSpecial.InvariantEquals(Umbraco.GetDictionaryValue(sitSpecFilter)));
            }
            if (!string.IsNullOrWhiteSpace(tipologyFilter))
            {
                filteredData = filteredData.Where(x => x.RequestType.InvariantContains(Umbraco.GetDictionaryValue(tipologyFilter)));
            }
            if (!string.IsNullOrWhiteSpace(productFilter))
            {
                filteredData = filteredData.Where(x => x.Drug.InvariantContains(
                    HelperMethods.GetValueFromDB("EdusuiteDbDSN",
                        "SELECT [ID_PRO] as [Value],[DESCRIT_PRO] as [Text]" +
                        "FROM [EDUSUITE_DOMPE].[dbo].[MIS_PRODOTTO]" +
                        $" where [ID_PRO] = {productFilter}")
                ));
            }
            if (!string.IsNullOrWhiteSpace(tipologyFilter))
            {
                filteredData = filteredData.Where(x => x.RequestType.InvariantContains(Umbraco.GetDictionaryValue(tipologyFilter)));
            }
            if (!string.IsNullOrWhiteSpace(datesFilter))
            {
                var dates = datesFilter.Split(new[] { "###" }, StringSplitOptions.RemoveEmptyEntries);
                if (dates.Length == 2)
                {
                    var e1 = DateTime.TryParseExact(dates[0], "yyyy/MM/dd", cc,DateTimeStyles.None, out start);
                    var e2 = DateTime.TryParseExact(dates[1], "yyyy/MM/dd", cc, DateTimeStyles.None, out end);
                    if (e1 && e2)
                    {
                        filteredData = filteredData.Where(x => x.SubmissionDate.Date >= start.Date && x.SubmissionDate.Date <= end.Date);
                    }
                }

            }
            // Paging filtered data.
            // Paging is rather manual due to in-memmory (IEnumerable) data.
            var dataPage = filteredData.Skip(request.Start).Take(request.Length);

            // Response creation. To create your response you need to reference your request, to avoid
            // request/response tampering and to ensure response will be correctly created.
            var response = DataTablesResponse.Create(request, data.Count(), filteredData.Count(), dataPage);

            //response = new DataTablesResponse(request,data.Count(), filteredData.Count(), dataPage);
            // Easier way is to return a new 'DataTablesJsonResult', which will automatically convert your
            // response to a json-compatible content, so DataTables can read it when received.

            return new DataTablesJsonResult(response, JsonRequestBehavior.AllowGet);
        }

[ALMMa]

This seems related to the query execution, but now that I have resumed work on this library I will double-check for possible delays within the internals of it as well.