Speed up page loading and information processing

Question

On my web application I'm loading a page which can load up to 8000 or more rows in a table, each row having its own dropdownlist. This first process proves to be extremely inefficient but I was asked to do it like this. The code loading the rows goes below:

 <tbody>
        <%  var i = 0;



            foreach (var row in Model)
            {
                var comp = "ok";
                if (row.LidExpected != (string.IsNullOrEmpty(row.LidObtained) ? null : row.LidObtained) || row.QuantityExpected != row.QuantityObtained)
                {
                    comp = "ko";
                }
                %>
        <tr class="child_row <%= comp %>">
            <input type="hidden" name="Goods.index" value="<%= i %>" />
            <td class="field <%= InventoryGoods.Metadata.Gid.CssClass %>">
              <%--  <%= Html.Encode(row.Gid) %>--%>
              <%--http://tecnicos.urbanos.com/Goods/Details/... --%>
               <%= Html.ActionLink(row.Gid, "Details", "Goods", new {id = row.Gid}, null) %>
                <%= Html.Hidden(String.Format("Record[{0}].Gid", i), row.Gid) %>
            </td>
            <td class="field <%= InventoryGoods.Metadata.LidExpected.CssClass %>">
                <%= Html.Encode(row.LidExpected) %>
                <%= Html.Hidden(string.Format("Record[{0}].LidExpected", i), row.LidExpected)%>
            </td>
            <td class="fieldRight <%= InventoryGoods.Metadata.QuantityExpected.CssClass %>">
                <%= Html.Encode(row.QuantityExpected) %>
                <%= Html.Hidden(string.Format("Record[{0}].QuantityExpected", i), row.QuantityExpected)%>
            </td>
            <td class="field <%= InventoryGoods.Metadata.LidObtained.CssClass %>">
                <%= Html.Encode(row.LidObtained) %>
                <%= Html.Hidden(string.Format("Record[{0}].LidObtained", i), row.LidObtained)%>
            </td>
            <td class="fieldRight <%= InventoryGoods.Metadata.QuantityObtained.CssClass %>">
                <%= Html.Encode(row.QuantityObtained) %>
                <%= Html.Hidden(string.Format("Record[{0}].QuantityObtained", i), row.QuantityObtained)%>
            </td>
             <%if (int.Parse(state.ToString()) == (int)InventoryStateEnum.Closed || int.Parse(state.ToString()) == (int)InventoryStateEnum.Verified)
               { %>
            <td class="field <%= InventoryGoods.Metadata.Action.CssClass %>">
                <%                        
                    switch (row.Action)
                    {
                        case (int)InventoryGoodsActionEnum.AdjustQuantity: %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {
                        new SelectListItem { Text= "Ajustar Quantidade", Value= ((int)InventoryGoodsActionEnum.AdjustQuantity).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantity == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                      <%      break;
                        case (int)InventoryGoodsActionEnum.AdjustLocation: %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {

                        new SelectListItem { Text= "Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocation == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                          <%  break;
                        case (int)InventoryGoodsActionEnum.AdjustStockType:  %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {                        
                        new SelectListItem { Text= "Ajustar Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustStockType == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                         <%   break;
                        case (int)InventoryGoodsActionEnum.AdjustQuantityLocation:  %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {
                        new SelectListItem { Text= "Ajustar Quantidade", Value= ((int)InventoryGoodsActionEnum.AdjustQuantity).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantity == row.Action ? true : false},
                        new SelectListItem { Text= "Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocation == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Quantidade e Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustQuantityLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantityLocation == row.Action ? true : false},                                                
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                          <%  break;
                        case (int)InventoryGoodsActionEnum.AdjustQuantityStockType: %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {
                        new SelectListItem { Text= "Ajustar Quantidade", Value= ((int)InventoryGoodsActionEnum.AdjustQuantity).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantity == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ajustar Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustStockType == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Quantidade e Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustQuantityStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantityStockType == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                         <%   break;
                        case (int)InventoryGoodsActionEnum.AdjustLocationStockType: %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {

                        new SelectListItem { Text= "Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocation == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ajustar Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustStockType == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ajustar Tipo de Stock e Relocalizar", Value = ((int)InventoryGoodsActionEnum.AdjustLocationStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocationStockType == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                         <%   break;
                        case (int)InventoryGoodsActionEnum.AdjustQuantityLocationStockType: %>
                             <%=    Html.DropDownList(String.Format("Record[{0}].Action", i), new[]
                    {
                        new SelectListItem { Text= "Ajustar Quantidade", Value= ((int)InventoryGoodsActionEnum.AdjustQuantity).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantity == row.Action ? true : false},
                        new SelectListItem { Text= "Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocation == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Quantidade e Relocalizar", Value= ((int)InventoryGoodsActionEnum.AdjustQuantityLocation).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantityLocation == row.Action ? true : false},                        
                        new SelectListItem { Text= "Ajustar Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustStockType == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Quantidade e Tipo de Stock", Value = ((int)InventoryGoodsActionEnum.AdjustQuantityStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantityStockType == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Tipo de Stock e Relocalizar", Value = ((int)InventoryGoodsActionEnum.AdjustLocationStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustLocationStockType == row.Action ? true : false},
                        new SelectListItem { Text= "Ajustar Quantidade, Tipo de Stock e Relocalizar", Value = ((int)InventoryGoodsActionEnum.AdjustQuantityLocationStockType).ToString(), Selected= (int)InventoryGoodsActionEnum.AdjustQuantityLocationStockType == row.Action ? true : false},
                        new SelectListItem { Text= "Ignorar", Value= ((int)InventoryGoodsActionEnum.Ignore).ToString(), Selected= (int)InventoryGoodsActionEnum.Ignore == row.Action ? true : false},
                    })%>
                        <%    break;
                    }


                %>
                <%= Html.Hidden(string.Format("db_Action[{0}]", i), row.Action)%>
            </td>
            <td>
                <a title="Apagar Evento" id="delete_Event" class="ui-widget ui-state-default ui-icon ui-icon-trash">
                </a>
            </td>
            <td class="field <%= InventoryGoods.Metadata.ActionOn.CssClass %>" id="dateCell">
                <span id="ActionOn_<%= i %>">
                    <%= Html.Encode(row.ActionOn.HasValue ? Html.FormatDateTime(row.ActionOn.Value) : Html.Encode(""))%></span>
                <%= Html.Hidden(String.Format("Record[{0}].ActionOn", i), row.ActionOn.HasValue ? row.ActionOn.Value : new DateTime())%>
            </td>
            <% } %>
            <%= Html.Hidden("lineVal", i) %>
            <%  i++;
            } %>
        </tr>
    </tbody>

So this is the root of all problems. Next problem is when I need to send this data back to the server, which of course, will take up a lot of time as well. I don't know any way to make it any faster, as it takes right now about 8 or 9 minutes to send 9000 rows back to the server.

The problems continue when I use these rows for a batch update, as follows:

public void UpdateInventoryGoods(List<InventoryGoods> list, int id)
        {
            //int index = 0;

            var query = from inventoryGoods in context.InventoryGoods
                        where inventoryGoods.ParentId == id
                        select inventoryGoods;

            List<InventoryGoods> goodsList = query.ToList();
            var memmoryInventoryGoodsEvent = context.InventoryGoodsEvents.ToList();//obter apenas o do Id

            using (var scope = new TransactionScope())
            {
                var events = from g in context.GoodsEvent
                             select g;
                List<GoodsEvent> goodsEventList = events.ToList();

                foreach (InventoryGoods i in list)
                {
                    foreach (InventoryGoods e in goodsList)
                    {
                        //if (index == 30)
                        //{
                        //    index = 0;
                        //    context.SubmitChanges();
                        //}
                        var eventId = getEventId(e.Id, memmoryInventoryGoodsEvent);
                        if (e.Gid == i.Gid && !eventId.HasValue && !e.ActionOn.HasValue)
                        {
                            e.Action = i.Action;

                        }
                        else if ((e.Gid == i.Gid && eventId.HasValue) && (e.Action != i.Action || i.ActionOn == DateTime.MinValue))
                        {
                            e.Action = i.Action;
                            e.ActionOn = null;



                            var inventoryGoodsEventsList = memmoryInventoryGoodsEvent.Where(x => x.InventoryGood == e.Id); 





                            foreach (InventoryGoodsEvents goodsEvent in inventoryGoodsEventsList)
                            {
                                context.InventoryGoodsEvents.DeleteOnSubmit(goodsEvent);

                                foreach (GoodsEvent ge in goodsEventList)
                                {
                                    if (ge.Id == goodsEvent.EventId)
                                    {
                                        ge.IsDeleted = true;
                                        ge.DeletedOn = DateTime.Now;
                                        ge.DeletedBy = System.Web.HttpContext.Current.User.Identity.Name;
                                    }
                                }
                            }




                        }
                        //++index;
                    }
                }
                context.SubmitChanges();
                scope.Complete();
            }

        }

   public int? getEventId(int InventoryGood,List<InventoryGoodsEvents> memmoryList)
        {
            //var firstinventoryGoodsEvents = context.InventoryGoodsEvents.Where(i => i.InventoryGood == InventoryGood).FirstOrDefault();
            var firstinventoryGoodsEvents = memmoryList.Where(i => i.InventoryGood == InventoryGood).FirstOrDefault();

            if (firstinventoryGoodsEvents != null && firstinventoryGoodsEvents.InventoryGood > 0)
            {
                return firstinventoryGoodsEvents.InventoryGood;

            }
            else
            {
                return null;
            }
        }

The i and e variables in the double foreach iteration above mean the rows I got from the View and the rows in the database. I run them against each other to find any differences, and if there are differences, update them. But all these processes are proving TOO slow and I really need to improve this quickly. Specially the double foreach iteration for which I can't find a better solution.

So could you people help me out?

EDIT: I fixed at least the update method by the use of dictionaries and escaping the double foreach iterations I was doing in my previous code like this:

public void UpdateInventoryGoods(List<InventoryGoods> list, int id)
    {
        //int index = 0;

        var query = from inventoryGoods in context.InventoryGoods
                    where inventoryGoods.ParentId == id
                    select inventoryGoods;

        Dictionary<string, InventoryGoods> goodsDictionary = query.ToDictionary(p => p.Gid);
        var memmoryInventoryGoodsEvent = (from c in context.InventoryGoodsEvents
                                         join a in context.InventoryGoods on c.InventoryGood equals a.Id
                                         where a.ParentId == id
                                         select c).ToList();//obter apenas o do Id

        using (var scope = new TransactionScope())
        {
            var events = from g in context.GoodsEvent
                         select g;

            Dictionary<int, GoodsEvent> goodsEventDictionary = events.ToDictionary(p => p.Id);

            //List<GoodsEvent> goodsEventList = events.ToList();

            foreach (InventoryGoods i in list)
            {

                var eventId = getEventId(i.Id, memmoryInventoryGoodsEvent);

                var objectToUpdate = goodsDictionary[i.Gid];


                if (!eventId.HasValue && !objectToUpdate.ActionOn.HasValue)
                {
                    objectToUpdate.ActionOn = i.ActionOn;
                    continue;
                }
                else if (eventId.HasValue && objectToUpdate.Action != i.Action || i.ActionOn == DateTime.MinValue)
                {
                    objectToUpdate.Action = i.Action;
                    objectToUpdate.ActionOn = null;

                    var inventoryGoodsEventsList = memmoryInventoryGoodsEvent.Where(x => x.InventoryGood == objectToUpdate.Id);

                    foreach (InventoryGoodsEvents goodsEvent in inventoryGoodsEventsList)
                    {
                        context.InventoryGoodsEvents.DeleteOnSubmit(goodsEvent);

                        var eventToUpdate = goodsEventDictionary[goodsEvent.EventId];

                                eventToUpdate.IsDeleted = true;
                                eventToUpdate.DeletedOn = DateTime.Now;
                                eventToUpdate.DeletedBy = System.Web.HttpContext.Current.User.Identity.Name;                            
                    }

                }
            }
            context.SubmitChanges();
            scope.Complete();
        }

    }

Answer 1

Page your list. Nobody can possibly deal with 8000 rows at a time anyway. Page your list, provide filters to help your users find stuff and you'll cut the amount of data being sent either way by a huge amount.

Answer 2

8000 rows? You should abandon that idea and implement some sort of paging. No matter what the application, displaying 8000 rows to a user is useless. I use mvccontrib grid which has paging capability.

Answer 3

Dont do it. No browser is going to like rendering that much information even if you can get your server to send it quickly. Any browser showing this many rows is going to run really slow on the client, scrolling will be jerky and it will be generally unusable.

I would advise either paging the information or even better filter the information to only show what the user is interested in, there is no way any user will ever be interested in that much data at once.

Answer 4

Two options:

1. Convince the customer that pagination is necessary or performance will suffer. Show him how other well established sites are doing it.
2. Use AJAX. Load only what can be seen on the screen, pick a jQuery scroll plugin and when the user scrolls load the missing part with AJAX. A bit like Google Images. You could also try some out of the box controls to avoid the heavy lifting.

No matter what option you choose fix the tag soup that the view represents currently by using helpers.

Answer 5

One option that could help is if you only render the data items for the drop downs one time for each type of drop down, in json format on the page. then create and populate all of the select ddl's on the client using javascript. it will save all of the excessive redundant data transfer for each row that gets rendered. you just have to store the value that will be selected in each row for when you build the select. this is just an idea i haven't tried, this could lead to the client just waiting for the javascript to do its thing as well.

also, for updating the data, is ajax a possibility and just call for each dropdown that gets changed right as the change happens?

Answer 6

The first question come to me is that where is the slow code?

On the server processing time, network transmitting or the render time in browser?

In some cases, large HTML TABLE is too slow on IE (even IE8). Use CSS "table-layout: fixed" might help on this.