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="[email protected]">


                            <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

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();