LINQ query performance issue when fetching data from db in MVC Razor

Question

Problem Statement: I'm trying to bind Multi-table data from db to view using Linq query which is taking more time.I'm having around 10000 records in db.Someone suggested to use the IQueryable instead of IEnumerable,but does it affect my current code(In both View and Controller)?? Or without using that can I accomplish this??

What I should do in-order to increase the performance of loading the result?? What I'm doing wrong?? Please suggest me some better way of doing this...

Controller:

public ActionResult Index()
    {
        var result = (from pr in db.Prod.AsEnumerable()
                      join s in db.Shift.AsEnumerable() on pr.Shift equals s.ShiftID
                      join m in db.Module.AsEnumerable() on pr.Module equals m.ModuleID
                      select new GlobalModel()
                      {
                          prodModelIndex = pr,
                          prodModel = prodModel,
                          shiftModel = s,
                          moduleModel = m,
                          ddlShift = objTransactionGeneralController.GetAllShift(),
                          ddlModule = objTransactionGeneralController.GetAllModule()

                      }).ToList();
    return PartialView(result);

    }





   public TransGeneralModel GetAllModule()
    {

        objTransGeneralModel.ddlModule = (from m in db.Module.AsEnumerable()
                                           select new SelectListItem
                                           {
                                               Value = m.ModuleID.ToString(),
                                               Text = m.ModuleName,
                                           }).ToList();


        return objTransGeneralModel;
    }

    public TransGeneralModel GetAllShift()
    {

        objTransGeneralModel.ddlShift = (from s in db.Shift.AsEnumerable()
                                          select new SelectListItem
                                          {
                                              Value = s.ShiftID.ToString(),
                                              Text = s.ShiftName,
                                          }).ToList();


        return objTranGeneralModel;
    }

View:

@model IEnumerable<SIA.Models.Trans.GlobalModel>

@using GridMvc.Html

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
    ViewBag.Title = "Index";

    }

    <link rel="stylesheet" href="@Url.Content("~/Content/jquery.dataTables.min.css")">
    <script src="@Url.Content("~/Scripts/jquery-2.1.1.min.js")"></script>


    <h2>Details</h2>
    <hr />

    <div style="width: 1000px; padding-left: 70px">
        @Html.Partial("Create")
        <br />
    </div>

    <h5 class="pull-right">
        <b class="fa fa-keyboard-o" style="color: blue"></b>
        @Ajax.ActionLink("Edit", "ProdEdit", "Prod", new { }, new AjaxOptions
    {
        InsertionMode = InsertionMode.Replace,
        UpdateTargetId = "prod-details",
        HttpMethod = "GET",
    }, new { style = "color:blue" })
    </h5>

    <br />
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
        @Html.ValidationSummary(true)
        if (Model.FirstOrDefault().prodModelIndex != null)
        { 

        <div id="prod-details">
            <table class="table table-striped" id="tblProdDetails">
                <thead>
                    <tr>

                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.ProdID)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Date)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Module)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().productionModelIndex.Shift)
                        </th>

                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Hour)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Output)
                        </th>

                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model)
                    {
                        <tr id="customer-row-@item.prodModelIndex.ProdID">


                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.ProdID)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Date)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.moduleModel.ModuleName)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.shiftModel.ShiftName)
                                @Html.HiddenFor(modelItem => item.prodModelIndex.Shift)
                            </td>

                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Hour)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Output)
                            </td>
                        </tr>
                    }
                </tbody>
            </table>

        </div>
        }
    }

    <script>

        $(document).ready(function () {

            $('#tblProdDetails').dataTable({
                "order": [[1, "desc"], [3, "asc"]]

            });

        });
    </script>

    @section Scripts {
        @Scripts.Render("~/bundles/jqueryval")
        @Scripts.Render("~/Scripts/jquery.dataTables.min.js")
        <script type='text/javascript'>
            $(function () {
                $('.datepicker').datepicker({
                    format: "dd M yyyy",
                }).on('changeDate', function (e) {
                    $(this).datepicker('hide');
                });
            })

        </script>
    }

Answer 1

First, when you call a method like ToList(), AsEnumerable() or FirstOrDefault(), it will execute the query on the database. In your case, would be nice to remove them to hit a single query with joins.

var result = (from pr in db.Prod
                      join s in db.Shift on pr.Shift equals s.ShiftID
                      join m in db.Module on pr.Module equals m.ModuleID
                      select new GlobalModel()
                      {
                          prodModelIndex = pr,
                          prodModel = prodModel,
                          shiftModel = s,
                          moduleModel = m

                      }).ToList();

Answer 2

TL;DR

• Filter your primary table (Prod) with a .Where or at least use .Take() to limit the number of rows to something sane to show on a screen
• Drop the .AsEnumerable() - you are materializing whole tables into memory
• Add foreign keys to your table, regenerate your DBML, and use navigation instead of explicit joins
• Be careful of what you put into a Select projection - ddlShift = objTransactionGeneralController.GetAllShift() will be called for each row in the result set.

In Detail

By applying .AsEnumerable() to your collections like :

var result = (from pr in db.Prods.AsEnumerable()
      join s in db.Shifts.AsEnumerable() on pr.ShiftID equals s.ShiftId
      join m in db.Modules.AsEnumerable() on pr.ModuleID equals m.ModuleId
      select new ...

Your current code results in 3 explicit queries to Sql Server, each of which will load the whole table into memory: (e.g. use Sql Profiler, or LinqPad, etc)

SELECT [t0].[ModuleId], ... other columns
FROM [dbo].[Module] AS [t0];

SELECT [t0].[ShiftId], ... other columns
FROM [dbo].[Shift] AS [t0];

SELECT [t0].[ProdID], [t0].[ShiftID], [t0].[ModuleID], ... other columns
FROM [dbo].[Prod] AS [t0];

Given that you have no WHERE predicate at all, this might not be all that much slower than joining in the database. However, in general, doing this isn't a good idea at all, since:

1. By applying .AsEnumerable(), you are taking away the ability for Linq2Sql to parse an IQueryable expression tree into native Sql. Generally, doing joining and filtering in the database will be quicker, and require less memory than doing this in memory. Assuming that Prod, Shift and Module are the Linq.Table<>s, the solution here is to simply remove .AsEnumerable() - this will allow Linq to use the IQueryable extension methods for joining, filtering, aggregating etc.

2. It is unusual to retrieve all rows in a table and show them all at once in a single screen, unless the table size guaranteed to have a small number of rows. Usually you will apply some kind of filter to a table.

3. As per Bhaarat's comment, if you have set up your foreign keys between the tables correctly (as it is implied by your sample code, it appears there are designed join keys), when you import your tables into Linq2Sql DBML, you will also get navigation between the entities, and thus will not need to join the tables explicitly.

4. Putting this into your Select projection - ddlShift = objTransactionGeneralController.GetAllShift() will be called for each row in the result set. This looks expensive. Do this once, store the result in a local variable, and if needed, reference it in each of the projections. Or change your ViewModel so that it doesn't repeat the reference on every row, if it is needed once.

5. Lazy loading can be a performance problem (the 1 to N problem) - turn this off on DataContext via db.DeferredLoadingEnabled = false, and instead, explicitly specify the depth of graph to be eager loaded with appropriate LoadWith<> statements

Your code will now look like this:

using (var db = new DataClasses1DataContext())
{
    // Switch off Lazy Loading in favour of eager loading
    db.DeferredLoadingEnabled = false;
    var ds = new DataLoadOptions();
    ds.LoadWith<Prod>(p => p.Shift);
    ds.LoadWith<Prod>(s => s.Module);

    // Do this once, not in a tight loop
    var ddlShift = objTransactionGeneralController.GetAllShift();
    var ddlModule = objTransactionGeneralController.GetAllModule();

    var result = db.Prods
        .Where(p => p.ProdID > 5 && p.ProdID < 10) // Apply some kind of filtering
        .Take(1000) // And / Or Limit the rows to something sane
        .Select(pr => new GlobalModel()
          {
              prodModelIndex = pr,
     // These 2 fields are actually redundant, as we now have navigation fields 
     // for these off prodModelIndex
              shiftModel = s, 
              moduleModel = m,
              ddlShift = ddlShift,
              ddlModule = ddlModule
          }).ToList();

    return PartialView(result);
}

And the generated SQL will be a single query, with saner row limits:

SELECT TOP 1000 [t0].[ProdID], [t0].[ShiftID], [t0].[ModuleID], [t1].[ShiftId] AS [ShiftId2], [t2].[ModuleId] AS [ModuleId2]
FROM [dbo].[Prod] AS [t0]
INNER JOIN [dbo].[Shift] AS [t1] ON [t0].[ShiftID] = ([t1].[ShiftId])
INNER JOIN [dbo].[Module] AS [t2] ON [t0].[ModuleID] = ([t2].[ModuleId])
WHERE [t0].[ProdID] BETWEEN 5 AND 10;

Answer 3

If the tables are referenced by foreign key then no need to join. you can directly access the referred record like this

var result = (from pr in db.Prod
                      select new GlobalModel()
                      {
                          prodModelIndex = pr,
                          prodModel = prodModel,
                          shiftModel = pr.Shift,
                          moduleModel = pr.Module     
                      }).ToList();