0

Hello I am using jqgrid to load some data in a .net MVC 3.0 C# application.

There is a materials grid that needs to be loaded in about 6 different places. They are all the same. The grid is listing the pricing and details of about 8700 items.

The issue that I have is that two of the columns "cost" and "price" have calculations that are performed from the database. These two columns make the grid load extremely slow.

The material testing list we were working with had about 730 items initially. The first time without some sort of optimiztion the grid would take about 1min 30 secs to load completely. After the changes this dropped to about 4 seconds which is acceptable.

We are now working with the real list that will be used for the materials and this list contains 8500+ items. After the innitial load it is talking about 2 mins by watch to load the 8500 items.

This will really not be acceptable and so I am thinking the best solution is to have the search toolbar function or an external search be the one that loads the items but only the search result items.

So what I would like to see is that after the innitial page loads the grid is empty and only gets filled after a search is done and only shows the search results.

If possible would be the best to be able to do this with the search toolbar feature. This is already working properly but after the innitial long load.

Any suggestions would be very welcomed. I am not the original programmer just trying to get some information so I do not have to pay my dev for google searches if possible.

Thank you for your time, if an example of the current code is needed please let me know if it would help or if you could just provide some sample code if what I need is possible,

Serer side code:

 public ActionResult EstimateMaterialAddGridData(string sidx, string sord, int page, int rows)
    {

        IQueryable<Material> mats;
        mats = Material.Find(x => x.OwnerId == UserAccount.GetOwnerId && x.isDeletedFromCatalog == false).AsQueryable();

        int pageIndex = page - 1;
        int pageSize = rows;
        int totalRecords = mats.Count();
        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
        var jsonData = new
        {
            total = totalPages,
            page = page,
            records = totalRecords,
            rows = (
                from sub in mats
                select new
                {
                    i = sub.Id,
                    cell = new string[] {
                        sub.Id.ToString(),
                        sub.Id.ToString(),
                        sub.Id.ToString(),
                        sub.ProductCode,
                        sub.Description, 
                        sub.Units,
                        sub.Categories,
                        sub.UnitCost.ToString(),
                        sub.Price.ToString()
                    }
                }
            ).ToArray()
        };

        return Json(jsonData);
    }

JS grid code`jQuery(document).ready(function () { var grid = jQuery("#mgrid");

grid.jqGrid({
    url: '/Material/EstimateMaterialAddGridData',
    datatype: 'json',
    mtype: 'POST',
    colNames: ['Id', '','View/Edit',  'Product Code', 'Description', 'Units', 'Categories', 'Cost', 'Price'],
    colModel: [
        { name: 'Id', index: 'Id', key: true, hidden: true, editrules: { edithidden: true} },
        { name: 'Add', index: 'Add', sortable: false, width: 50,search:false, resizable: false, editable: false, align: 'center', formatter: formatLink, classes: 'not-editable-cell' },
        { name: 'Open', index: 'Open', sortable: false, width: 90,search:false, resizable: false, editable: false, align: 'center', formatter: formatLinkNew, classes: 'not-editable-cell' },
        { name: 'ProductCode', index: 'ProductCode', sorttype: 'text',search:true, width: 100, resizable: false },
        { name: 'Description', index: 'Description', sorttype: 'text',search:true, width: 275, resizable: false },
        { name: 'Units', index: 'Units', sorttype: 'text', width: 75,search:true, resizable: false },
        { name: 'Categories', index: 'Categories', sorttype: 'text',search:true, width: 300, resizable: false, editable: false,  },
        { name: 'UnitCost', index: 'UnitCost', sorttype: 'float', width: 75,search:true, align: 'right', resizable: false, editable: false, formatter: 'currency' },
        { name: 'Price', index: 'Price', sorttype: 'float', width: 75, search:true,align: 'right', resizable: false, editable: false, formatter: 'currency' },
    ],
    pager: '#mpager',
    height: '100%',
    rowNum: 10,
    rowList: [10, 20, 50, 100],
    sortname: 'Id',
    sortorder: 'desc',
    sortable: true,
    loadonce: true,
    ignoreCase: true,
    viewrecords: true,
    caption: 'Material',
    cellEdit: false,
    hidegrid: false,
    viewrecords: true,

});

grid.jqGrid('navGrid', '#mpager',
    { resize: false, add: false, del: false, search: true, refresh: true, edit: false, alerttext: 'Please select an material' }
).jqGrid('navButtonAdd', '#mpager',
    { title: "Create New Material Catalouge", buttonicon: "ui-icon-plus", onClickButton: newMaterial, position: "First", caption: "" });`
tereško
  • 58,060
  • 25
  • 98
  • 150
GTITC
  • 139
  • 2
  • 2
  • 12
  • Could you include the code which uses jqGrid currently? Could you includes more information about the implementation of the columns "cost" and "price". I don't understand why you should have any problem with the columns. Is the SELECT which produces the value of the columns is too slow? – Oleg Feb 13 '12 at 10:34

1 Answers1

1

I can forward you to the following two old answers: this and this. The answers contains demo projects which demonstrates how to use the searching toolbar in the jqGrid.

I should mention the following things which can dramatically improve the performance of jqGrid

  • you should always use gridview: true option of jqGrid. In my opinion it should be default option in jqGrid.
  • you should definitively use server side paging. I see no sense to show the user 8500+ (or 730) items at once. No monitor can display the items and no user can assimilate the information from so many items. What the user really need is to be able to have intelligent filtering of the data - the way which you decide to go yourself. I use mostly combination from the filtering of grid in the searching toolbar and the advanced searching which help advanced users to construct more complex searching filter. Additionally you can consider to save some common used filters in the predefined named filter templates. You will find examples of the filter template on the official demo page under "Searching" / "Search Templates". Another way is to use external filters. In the answer you will find implementation details which could help you on the way.
  • If you want prevent the first loading of the grid you can use datatype: 'local' initially which skip any requests to the server. To activate the communication to the server you can change the datatype to 'json' or 'xml' whenever you as need. For example you can place $(this).jqGrid('setGridParam', {datatype: 'json'}); directly inside of loadComplete.
Community
  • 1
  • 1
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • ok will look into this see if it might work. Just to clarify my grid is set to only show 20 results per page. Even though it is set like this, the whole 8500+ items are loaded in the background so when i go to page 2, 3, 4... they are all loaded already. If change to only load 20 per page when I enter a search term will it only look through the 20 loaded items or will it still look though all the 8300 items? – GTITC Feb 14 '12 at 04:35
  • @user1206514: The searching like paging and sorting should be implemented *on the server side*. The server get the current filters as the `filters` parameter. You should construct the `WHERE` part of the `SELECT` to the database and return like before only the requested page to the client. The requested page are identified by `page` and `rows` parameters. If you follow the scenario the results of the previous query (to get another page) the data of the full SELECT (without paging) can be cached in the database. So the select of another page are typically quickly. – Oleg Feb 14 '12 at 06:05
  • @user1206514: In any way you have typically <= 1 second per page as the loading time for the data like 10000 items if you use server side paging, sorting and *filtering* and if you carefully implemented all the parts. If you construct the `WHERE` parts of the queries using SQL parameters you will be free from any SQL injection attack. So you can really implement all which works not only quickly but also safe. – Oleg Feb 14 '12 at 06:09
  • thank you Oleg I was just given a copy of the server side code as well as the javascript code, I would appreciate any opinion on this as to how I can incorporate your suggestion or if after you see my code it still applies. – GTITC Feb 14 '12 at 10:44
  • @user1206514: In your code there are one important error: you use `i = sub.Id` instead of `id = sub.Id`. I would recommend you read [the answer](http://stackoverflow.com/a/5501644/315935) and [another one](http://stackoverflow.com/a/7392816/315935) which contains more recent code which you can download and adopt to your project. – Oleg Feb 14 '12 at 11:09
  • couldnt add this last JS grid code not sure why.. but here it is – GTITC Feb 14 '12 at 11:11
  • jQuery("#mgrid").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : false, defaultSearch : "cn"} ); }); function formatLink(cellValue, options, rowObject) { return 'Add'; } function formatLinkNew(cellValue, options, rowObject) { return 'Open'; } function newMaterial() { showMaterialCalc(0); } – GTITC Feb 14 '12 at 11:12
  • just to add something here the cost and price columns calculate the following for each line item unit cost - discount % * ship % * labour burden= unit cost // unit cost = profit % * OH % – GTITC Feb 14 '12 at 11:15
  • each line item has to calculate this and is pulling information from as " estimate catalogue" or master page with the OH % and Profit % calculation this is why it becomes slow, all other columns are straight info – GTITC Feb 14 '12 at 11:16
  • @user1206514: Please read [the post](http://meta.stackexchange.com/a/22189/147495) which described detailed how to format the code. The code of `showMaterialCalc` and all custom formatters could be the most important part for you so it would be interesting to see this. Moreover I don't understand *why* you send the information about `Id` **4 times** (!!!???). The usage of `i = sub.Id` instead of `id = sub.Id` is also an important problem. – Oleg Feb 14 '12 at 11:35
  • thank you Oleg, please keep in mind what I am trying to do, I would like to have the grid empty when the page loads, and if possible, keep the search toolbar function working so that when a search term is entered the list is populated ONLY with the search results. – GTITC Feb 14 '12 at 12:37
  • with the posted code would you be able to suggest something? If doesnt have to be working with the search toolbar, would be nice but maybe an external search bar could be programmed to load the grid what do you think, also please note I am not the developer but if you have any specific technical questions please let me know I can get the answers from my current dev again thank you – GTITC Feb 14 '12 at 12:39
  • @user1206514: Sorry, but I answered already you on the question. If you download the demo from [the answer](http://stackoverflow.com/a/5501644/315935) or from [another one](http://stackoverflow.com/a/7392816/315935) you will have the code which implement filtering *on the server side*. NO `loadonce: true` is needed. Then you can change `datatype` to `'local'` to prevent initial loading of the data and reset the `datatype` to `'json'` in the `loadComplete`. – Oleg Feb 14 '12 at 12:48
  • thank you Oleg I appreciate it, will try to the suggestions and report – GTITC Feb 14 '12 at 13:47