Poor performance of search suggestions in jqgrid

Question

I am using ASP.Net MVC4 with Entity Framework 6 for server side operations and using jqgrid extensively in many pages as it's solves my purpose. Currently I am using server side paging and sorting.

Now I have implemented search suggestions referring this demo. I have to show approx 70K suggestions initially but when I enter a character in search-box, my browser hangs. Sometimes it displays me suggestions but freezes to enter another character. I suppose it is happening because of large number of suggestions. I also looked here but not found a way. (Oleg, I am looking for you man!)

Please help me out to improve the performance of search suggestions. My jqgrid is as follows:

 $("#DetailGrid").jqGrid({
    url: dataTobind,        
    datatype: dataType,      
    rownumbers: false,
    height: 400,
    rowNum: 25,        
    mtype: 'POST',
    colNames: ['ContactOrderNo', 'Contract Order Number', 'Serial Number', 'Model Number', 'Capacity', 'Contract Details'],
    colModel: [
                    { name: 'Capacity', width: 150, editable: false, hidden: true, sortable: false, editrules: { required: true } },
                    {
                        name: 'ContactOrderNo', width: 300, editable: false, sortable: false, hidden: false,searchoptions: {
                            dataInit: function (elem) {
                                // it demonstrates custom item rendering
                                $(elem).autocomplete({ source: 'GetAutocompleteData' });
                                                      }
                        },                               

                            formatter: function (cellvalue, options, rowObject) {

                            return '<a href="/OrderList/AddOrder/?ModelNo=' + cellvalue + '" >' + cellvalue + '</a>';
                        }
                    },

                       { name: 'SerialNumber', width: 150, sortable: false, editable: false, editrules: { required: true } },
                       { name: 'ModelNo', width: 150, sortable: false, search:true, editable: false, editrules: { required: true } },
                        { name: 'Capacity', width: 150, sortable: false,search: false, editable: false, editrules: { required: true } },

                          {
                              name: 'ContractDetails', search: false, sortable: false, formatter: function (cellvalue)
                          {
                              var i, res = "", linkInfo;

                              if (cellvalue == null || !$.isArray(cellvalue)) {
                                  return "&nbsp;"; // empty cell in case or error
                              }

                              for (i = 0; i < cellvalue.length; i++) {
                                  linkInfo = cellvalue[i];
                                  res += (res.length > 0 ? "<br/> " : "") +
                                      '<a href="' + linkInfo.LinkSrc + '" target="_blank"  >' +
                                      linkInfo.LinkText + '</a>';
                              }

                              return res;
                          }
                              }                              

    ],

    rowList : [10,20,30,50],          
    viewrecords: true,        
    gridview: true,
    sortable: false,
    loadonce: false,
    cellsubmit: 'clientArray',
    autowidth: true,
    pager: '#DetailGridPager',            

});
jQuery("#DetailGrid").trigger('reloadGrid');

$("#DetailGrid").jqGrid('navGrid', '#DetailGridPager', { edit: false, add: false, del: false, search: false, refresh: false });
$('#DetailGrid').jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true, enableClear: false, defaultSearch: 'cn' });

And My controller is as follows :

   public JsonResult GetAutocompleteData(string term)
    {
        var cxt = new AppDataContext();
        return Json(cxt.Order.Where(t => t.ContactOrderNo.Contains(term)).Select(t => new { value = t.ContactOrderNo }).ToList(), JsonRequestBehavior.AllowGet);

    }

Probably you should add `.Take(100)` or some other limit to the statement in `GetAutocompleteData` (before `.ToList()` of cause). In the case the method `GetAutocompleteData` will returns maximum 100 items. Moreover I would recommend you to try to use select2 [the demo](http://www.ok-soft-gmbh.com/jqGrid/UsageFormetterSelect2.htm) from [the answer](http://stackoverflow.com/a/19427444/315935). It could be helpful if one have many items whon which one need to choose. — Oleg, Nov 11 '14 at 19:36
Thanks for quick response Oleg! According to my use case I would prefer .Take(100) option rather select2, but in some cases I would be requiring Select2 so thanks for sharing the demo. — tom, Nov 11 '14 at 19:45
You are welcome! I use also both Autocomplete and select2 in my projects. Is the usage of `.Take(100)` could solve your problem? — Oleg, Nov 11 '14 at 19:47
Ok! I see , but in my project's scenario I have to use Autocomplete only. Now I am facing issue in user agent, Chrome renders the autocomplete results smoothly but Firefox hinders while I am entering only first character for search after 10 seconds it displays the results, can you suggest some tuning for it? — tom, Nov 11 '14 at 19:58
Sorry, but I'm not sure that I understand your current problem. In any way you can consider to use `StartsWith` instead of `Contains` it it's possible and to create Index on the `ContactOrderNo` column. It should improve the time to getting the response. Probably you need to change `.Take(100)` to something like `.Take(20)`? All depend on your project and the clients requirements. — Oleg, Nov 11 '14 at 20:03
I am getting the results by using 'StartsWith' but I will try just after creating the index on 'ContactOrderNo'. Thanks for your suggestions ! — tom, Nov 11 '14 at 20:08

score 1 · Accepted Answer · answered Nov 11 '14 at 20:07

One can add the call of .Top in the GetAutocompleteData, for example .Top(100) or .Top(20) before .ToList(). It will reduce the results of the corresponding SELECT statement. You should consider to use .StartsWith instead of .Contains if it's possible. In the case the SELECT could use the Index in the database it such exist on the field ContactOrderNo.

Poor performance of search suggestions in jqgrid

1 Answers1