javascript - 从 Controller 传递数据时，Jquery Grid 绑定(bind)需要时间

Question

当我使用 api 获取超过 20k 数据时遇到问题。

数据获取速度很快，但在 jquery 网格中绑定(bind)时，需要大约 5 到 8 分钟的时间

这里我在 db.js 中使用 jquery 插件，绑定(bind)在 View 页面中，并且花费了很多时间。

我正在开发 mvc4(.net)。

Controller :

[HttpGet]
    public ActionResult GetMerchandise(string Vendor = "", string Desc = "", string Model = "", string SKU = "", string SKU2 = "",
                string Comment = "", string Received = "", int Location = 0, int Barcode = 0, string BarcodePrinted = "",
        string LastUpdate = "", int Damage = 0, string Created = "", string ShipDate = "", string ConsumeDate = "", string Pending = "",
        string IsDonated = "", string RetReq = "", string ReturnTo = "", int Quantity = 0, string GrName = "", int CustId = 0)
    {
        var model = new ItemModels();
        ItemBL objItem = new ItemBL();
        CodeBL objCode = new CodeBL();

        if (Session["CurrentCustomerId"] != null)
        {
            tItemApiController _apitItem = new tItemApiController();
            //var ItemList = _apitItem.getItemsByCustomerId(Convert.ToInt32(Session["CurrentCustomerId"]), Model, Desc);
            var ItemList = _apitItem.getItemsByCustomerId(Convert.ToInt32(Session["CurrentCustomerId"]));
            IEnumerable<sp_tItems_GetByCustomerId_Result> filteredItem;

            filteredItem = ItemList.Where(x =>
                (Model != "" ? (x.ModelNumber != null && x.ModelNumber.ToLower().Contains(Model.Trim().ToLower())) : true)
                 && (Desc != "" ? (x.Description != null && x.Description.ToLower().Contains(Desc.Trim().ToLower())) : true)
                && (Barcode != 0 ? (x.Barcode != 0 && x.Barcode.ToString().Contains(Barcode.ToString())) : true)
               && (Vendor != "" ? (x.Source != null && x.Source.ToLower().Contains(Vendor.Trim().ToLower())) : true)
               && (Comment != "" ? (x.Comments != null && x.Comments.ToLower().Contains(Comment.Trim().ToLower())) : true)
               && (SKU != "" ? (x.SKU != null && x.SKU.ToLower().Contains(SKU.Trim().ToLower())) : true)
               && (SKU2 != "" ? (x.SKU2 != null && x.SKU2.ToLower().Contains(SKU2.Trim().ToLower())) : true)
                && (CustId != 0 ? (x.CustomerID != null && x.CustomerID.ToString().Contains(CustId.ToString())) : true)
                && (BarcodePrinted != "" ? (x.BCPrinted != null && x.BCPrinted == Convert.ToBoolean(BarcodePrinted)) : true)
                && (Pending != "" ? (x.PendingShipment != null && x.PendingShipment == Convert.ToBoolean(Pending)) : true)
                && (IsDonated != "" ? (x.Donated != null && x.Donated == Convert.ToBoolean(IsDonated)) : true)
                && (RetReq != "" ? (x.ReturnRequested != null && x.ReturnRequested == Convert.ToBoolean(RetReq)) : true)
                && (ReturnTo != "" ? (x.ReturnTo != null && x.ReturnTo.ToString().Contains(ReturnTo.Trim())) : true)
                && (Quantity != 0 ? (x.Quantity != null && x.Quantity.ToString().Contains(Quantity.ToString())) : true)
                && (GrName != "" ? (x.GroupName != null && x.GroupName.ToString().Contains(GrName.ToString())) : true)
                && (Location != 0 ? (x.LocationID != null && x.LocationID.ToString().Contains(Location.ToString())) : true)
                && (Damage != 0 ? (x.DamageCodeID != null && x.DamageCodeID.ToString().Contains(Damage.ToString())) : true)
                ).ToList();

            //filteredItem = ItemList.ToList();

            model.ItemList = filteredItem.Select(x =>
            {
                return new ItemModels()
                {
                    Barcode = x.Barcode,
                    CustomerID = x.CustomerID != null ? x.CustomerID.Value : 0,
                    BCPrinted = x.BCPrinted != null ? x.BCPrinted.Value : false,

                    ConsumeDate = x.ConsumeDate.HasValue != false ? x.ConsumeDate.Value : default(DateTime),
                    Comments = x.Comments != null ? x.Comments : "",
                    strCreatedDate = x.CreatedDate.HasValue != false ? x.CreatedDate.Value.ToString("MM/dd/yyyy") : "",
                    DamageCode = x.DamageCodeID != null ? x.DamageCodeID.Value : 0,
                    strDeliveredForShotDate = x.DeliveredForShotDate.HasValue != false ? x.DeliveredForShotDate.Value.ToString("MM/dd/yyyy") : "",
                    Description = x.Description != null ? x.Description : "",
                    Description2 = x.Description2 != null ? x.Description2 : "",
                    Donated = x.Donated != null ? x.Donated.Value : false,
                    GroupName = x.GroupName != null ? x.GroupName : "",
                    ItemName = x.ItemName != null ? x.ItemName : "",
                    ItemTypeCodeID = x.ItemTypeCodeID != null ? x.ItemTypeCodeID.Value : 0,

                    strLastUsedDate = x.LastUsedDate.HasValue != false ? x.LastUsedDate.Value.ToString("MM/dd/yyyy") : "",
                    strLastUpdatedDate = x.LastUpdatedDate.HasValue != false ? x.LastUpdatedDate.Value.ToString("MM/dd/yyyy") : "",
                    //LastUpdatedDate = x.LastUpdatedDate.HasValue != false ? x.LastUpdatedDate.Value : default(DateTime),

                    Location = x.LocationID != null ? x.LocationID : "",
                    //LocationID = x.LocationID != null ? oblLoc.getAllLocationsById(x.LocationID).FirstOrDefault().Description : "",
                    LocationID = x.locationDescription != null ? x.locationDescription : "",

                    strMerchOrdered = x.MerchOrdered.HasValue != false ? x.MerchOrdered.Value.ToString("MM/dd/yyyy") : "",
                    ModelNumber = x.ModelNumber != null ? x.ModelNumber : "",

                    strOrderDate = x.OrderDate.HasValue != false ? x.OrderDate.Value.ToString("MM/dd/yyyy") : "",
                    PackingListID = x.PackingListID != null ? x.PackingListID.Value : 0,
                    PendingShipment = x.PendingShipment != null ? x.PendingShipment.Value : false,
                    Pickup = x.Pickup != null ? x.Pickup.Value : false,
                    Quantity = x.Quantity != null ? x.Quantity.Value : 0,

                    //ReceiveDate = x.ReceiveDate.HasValue != false ? x.ReceiveDate.Value : default(DateTime),
                    strReceiveDate = x.ReceiveDate.HasValue != false ? x.ReceiveDate.Value.ToString("MM/dd/yyyy") : "",

                    strReleaseDate = x.ReleaseDate.HasValue != false ? x.ShipDate.Value.ToString("MM/dd/yyyy") : "",
                    ReturnRequested = x.ReturnRequested != null ? x.ReturnRequested.Value : false,
                    ReturnTo = x.ReturnTo != null ? x.ReturnTo : "",

                    //ShipDate = x.ShipDate.HasValue != false ? x.ShipDate.Value : default(DateTime),

                    SKU = x.SKU != null ? x.SKU : "",
                    SKU2 = x.SKU2 != null ? x.SKU2 : "",
                    Source = x.Source != null ? x.Source : "",
                    UPC = x.UPC != null ? x.UPC : "",


                    strConsumeDate = x.ConsumeDate.HasValue != false ? x.ConsumeDate.Value.ToString("MM/dd/yyyy") : "",
                    strShipDate = x.ShipDate.HasValue != false ? x.ShipDate.Value.ToString("MM/dd/yyyy") : "",

                };
                //}).ToList();
                //}).OrderByDescending(x => x.ShipDate).ToList();
            }).OrderByDescending(x => x.Barcode).ToList();

        }



        var jsonResult = Json(model.ItemList, JsonRequestBehavior.AllowGet);
        jsonResult.MaxJsonLength = int.MaxValue;

        return jsonResult;
    }

    public ActionResult GetMerchant()
    {
        try
        {
            LocationBL oblLoc = new LocationBL();
            CodeBL objCode = new CodeBL();
            var Location = oblLoc.getAllLocationsById("");
            var Code = objCode.getAllDamageCodeById(0).Where(x => x.CodeGroupID == 3).ToList();
            var model = new ItemModels();
            var Loc = Location.Select(x => new { x.LocationID, x.Description }).ToList();
            var loctempValue = new { LocationID = "", Description = "" };
            Loc.Insert(0, loctempValue);
            var damage = Code.Select(x => new { x.CodeValue, x.CodeID }).ToList();
            var dmgtempValue = new { CodeValue = "", CodeID = 0 };
            damage.Insert(0, dmgtempValue);
            var result = new { loc = Loc, dam = damage };
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

查看:

<script>
    $(function () {
        $.ajax({
            type: "GET",            
            url: "/MerchandiseList/GetMerchant",
            datatype:"json"
        }).done(function (data) {

            //$("#leftMenu").hide();
            //reloadpage(data);

            var MyDateField = function (config) {
                jsGrid.Field.call(this, config);
            };            

            MyDateField.prototype = new jsGrid.Field({              

                sorter: function (date1, date2) {
                    return new Date(date1) - new Date(date2);
                },

                itemTemplate: function (value) {
                    //debugger
                    if (value == "")
                        return "";
                    else {

                        var date = new Date(value).toDateString()
                        //var date = new Date(value).toDateString("MM/dd/yyyy")
                        //return new Date(value).toDateString();
                        //return value;

                        var d = new Date(date),
                        month = '' + (d.getMonth() + 1),
                        day = '' + d.getDate(),
                        year = d.getFullYear();

                        if (month.length < 2) month = '0' + month;
                        if (day.length < 2) day = '0' + day;

                        var df = [month, day, year].join('/');
                        date = df;
                        return date;

                    }
                },

                insertTemplate: function (value) {
                    //debugger
                    return this._insertPicker = $("<input>").datepicker({ defaultDate: new Date() });
                },

                editTemplate: function (value) {
                    //debugger 
                    if (value == "")
                        return this._editPicker = $("<input>").datepicker({ defaultDate: new Date() });
                    else {
                        return this._editPicker = $("<input>").datepicker().datepicker("setDate", new Date(value));
                    }
                },

                insertValue: function () {
                     debugger
                    if (this._insertPicker.datepicker("getDate") != null)
                        return this._insertPicker.datepicker("getDate");         //.toISOString("MM/dd/yyyy")

                    else
                        return this._insertPicker.datepicker("getDate");

                },

                editValue: function () {
                    debugger
                    if (this._editPicker.datepicker("getDate") != null) {
                        //.toISOString("MM/dd/yyyy")

                        return this._editPicker.datepicker("getDate");
                    }
                        //return this._editPicker.datepicker("getDate").toISOString();
                    else
                        return this._editPicker.datepicker("getDate");
                }

            });

            jsGrid.fields.myDateField = MyDateField;

            $("#jsGrid").jsGrid({
                height: "50%",
                width: "100%",
                filtering: true,
                editing: true,
                editButtonTooltip: "Edit",
                inserting: true,
                sorting: true,
                paging: true,
                autoload: true,
                pageButtonCount: 5,
                pageSize: 10,
                confirmDeleting: true,               
                deleteConfirm: "Do you really want to delete the merchandise?",
                refreshtext: "Refresh",
                refreshtitle:"Reload Grid",

                controller: db,

                fields: [
                    { type: "control" },
                    {
                        name: "Source", type: "text", width: 120, title: "Vendor"
                    },
                    {
                        name: "Description", type: "text", width: 210,
                        validate: { message: "Description is required!", validator: function (value) { return value != ""; } }
                    },
                    {
                        name: "ModelNumber", type: "text", width: 120, title: "Model#/Item"
                    },
                    { name: "SKU", type: "text", width: 90 },
                    { name: "SKU2", type: "text", width: 90 },
                    { name: "Comments", type: "text", width: 200 },
                    { name: "strReceiveDate", type: "myDateField", width: 80, align: "center", title: "Received" },                    
                    { name: "Location", type: "select", items: data.loc, valueField: "LocationID", textField: "Description", width: 100 },
                    { name: "Barcode", width: 80 },
                    { name: "BarcodePrinted", type: "checkbox", title: "Barcode Printed", sorting: false },
                    //{ name: "strLastUpdatedDate", type: "myDateField", width: 80, title: "Last Updated" },
                    { name: "strLastUsedDate", type: "myDateField", width: 80, title: "Last Updated" },
                    { name: "DamageCode", type: "select", items: data.dam, valueField: "CodeID", textField: "CodeValue", title: "Damage" },
                    { name: "strCreatedDate", editable: false, width: 80, title: "Created Date", type: "myDateField" },
                    { name: "strShipDate", type: "myDateField", myCustomProperty: "bar", width: 80, title: "Ship Date" },
                    { name: "strConsumeDate", type: "myDateField", myCustomProperty: "bar", width: 80, title: "Consume Date" },
                    { name: "PendingShipment", type: "checkbox", title: "Pending", sorting: false, width: 60 },
                    { name: "Donated", type: "checkbox", title: "Is Donated", sorting: false, width: 60 },
                    { name: "ReturnRequested", type: "checkbox", title: "Return Requested", sorting: false },
                    { name: "ReturnTo", type: "text", width: 150, title: "Return To" },
                    { name: "Quantity", type: "number", width: 50, title: "Qty" },
                    { name: "GroupName", type: "text", width: 150, title: "Group Name" },
                    { name: "CustomerID", width: 100, title: "Customer ID" }                   
                ],
            });

        });

    });
</script>

db.js:

(function () {
    debugger
    var db = {

        loadData: function (filter) {            
            var startIndex = (filter.pageIndex - 1) * filter.pageSize;//,PageSize:startIndex + filter.pageSize,PageIndex:startIndex
            return $.ajax({
                type: "GET",
                url: "/MerchandiseList/GetMerchandise",
                data: {
                    Vendor: filter.Source, Desc: filter.Description, Model: filter.ModelNumber, SKU: filter.SKU, SKU2: filter.SKU2,
                    Comment: filter.Comments, Received: filter.strReceiveDate, Location: filter.Location, Barcode: filter.Barcode,
                    BarcodePrinted: filter.BarcodePrinted, LastUpdate: filter.strLastUsedDate, Damage: filter.DamageCode,
                    Created: filter.strCreatedDate, ShipDate: filter.strShipDate, ConsumeDate: filter.strConsumeDate,
                    Pending: filter.PendingShipment, IsDonated: filter.IsDonated, RetReq: filter.ReturnRequested,
                    ReturnTo: filter.ReturnTo, Quantity: filter.Quantity, GrName: filter.GroupName, CustId: filter.CustomerID
                },
                dataType: "json", success: function (result) {
                    itemsCount: result.length
                }
            });
        },

        insertItem: function (insertingClient) {
            return $.ajax({
                type: "Post",
                url: "/MerchandiseList/InsertMerchandise",
                data: {
                    Source: insertingClient.Source, Description: insertingClient.Description, ModelNumber: insertingClient.ModelNumber, SKU: insertingClient.SKU,
                    SKU2: insertingClient.SKU2, Comments: insertingClient.Comments, ReceivedDate: insertingClient.strReceiveDate,
                    Location: insertingClient.Location, BarcodePrinted: insertingClient.BarcodePrinted, strLastUsedDate: insertingClient.strLastUpdatedDate,
                    DamageCode: insertingClient.DamageCode, strCreatedDate: insertingClient.strCreatedDate, strShipDate: insertingClient.strShipDate,
                    strConsumeDate: insertingClient.strConsumeDate, PendingShipment: insertingClient.PendingShipment, Donated: insertingClient.Donated,
                    ReturnRequested: insertingClient.ReturnRequested, ReturnTo: insertingClient.ReturnTo,
                    Quantity: insertingClient.Quantity, GroupName: insertingClient.GroupName

                },
                dataType: "json",
                success: function (result) {                    
                    loadMerchList();
                    //window.location.href = "/Test"                    
                },
                error: function (result) {                    
                    //alert('0');
                    loadMerchList();
                },
            });
        },

        updateItem: function (updatingClient) {            
            //var d = new Date(parseInt(updatingClient.ReceiveDate.substr(6))).toUTCString();            
            return $.ajax({
                type: "Post",
                url: "/MerchandiseList/UpdateMerchandise",
                data: {
                    Source: updatingClient.Source, Description: updatingClient.Description, ModelNumber: updatingClient.ModelNumber, SKU: updatingClient.SKU,
                    SKU2: updatingClient.SKU2, Comments: updatingClient.Comments,
                    strReceiveDate: (updatingClient.strReceiveDate != null && updatingClient.strReceiveDate != "") ? updatingClient.strReceiveDate.toUTCString() : "",
                    Location: updatingClient.Location, barcode: updatingClient.Barcode, BarcodePrinted: updatingClient.BarcodePrinted,
                    //strLastUsedDate: (updatingClient.strLastUpdatedDate != null && updatingClient.strLastUpdatedDate != "") ? updatingClient.strLastUpdatedDate.toUTCString() : "",
                    DamageCode: updatingClient.DamageCode,
                    //strCreatedDate: (updatingClient.strCreatedDate != null && updatingClient.strCreatedDate != "") ? updatingClient.strCreatedDate.toUTCString() : "",
                    strShipDate: (updatingClient.strShipDate != null && updatingClient.strCreatedDate != "") ? updatingClient.strShipDate.toUTCString() : "",
                    strConsumeDate: (updatingClient.strConsumeDate != null && updatingClient.strCreatedDate != "") ? updatingClient.strConsumeDate.toUTCString() : "",
                    PendingShipment: updatingClient.PendingShipment, Donated: updatingClient.Donated, ReturnRequested: updatingClient.ReturnRequested, ReturnTo: updatingClient.ReturnTo,
                    Quantity: updatingClient.Quantity, GroupName: updatingClient.GroupName
                },
                dataType: "json",
                success: function (result) {                    
                    loadMerchList();
                    //window.location.href = "/Test"                    
                },
                error: function (result) {                   
                    //alert('0');
                    loadMerchList();
                },
            });
        },

        deleteItem: function (deletingClient) {

            var barcode = deletingClient.Barcode;
            return $.ajax({
                type: "POST",
                url: "/MerchandiseList/DeleteMerchandise",
                data: { barcode: barcode },
                dataType: "json",
                success: function (result) {                    
                    loadMerchList();
                    //window.location.href = "/Test"                    
                },
                error: function (result) {                    
                    //alert('0');
                    loadMerchList();
                },
            });
            //var clientIndex = $.inArray(deletingClient, this.clients);
            //this.clients.splice(clientIndex, 1);
        },

        finishInsert: function (insertedItem) {
            this.clients.push(insertingClient);
        },                 // handles the finish of inserting item by controller.insertItem

        finishDelete: function (deleteItem, deletedItemIndex) {
            var clientIndex = $.inArray(deletingClient, this.clients);
            this.clients.splice(clientIndex, 1);            
        }
    };

    window.db = db;
}());

Answer 1

我认为问题是由 linq 查询引起的。我认为 linq 查询会创建非常大的 SQL 查询，并导致性能问题。您可以使用https://www.linqpad.net/为了提高 linq 查询的性能。在此应用程序中，您可以查看生成的 SQL 查询并可以修复它。