jQuery DataTable Server Side Processing Integration

Question

**"My Code is : - I have Added the .Aspx Script, Also I have included the JS and .Cs part.

This Code is Working and it returns more than 70k result and search and pagination functionality is not working.

Also it shows Page Unresponsive message on loading. I belive it give this error as its retun 70 K result .Please Help me to make this usuable."**

<table id="dataGrid" class="widthFull fontsize10 displayNone">
         <thead>
             <tr>
                 <th>RowID</th>
                 <th>District</th>
                 <th>BlockName</th>
                 <th>VillageName</th>
                 <th>SchoolCode</th>
                 <th>SchoolName</th>
             </tr>
         </thead>
         <tbody>
         </tbody>
     </table>

                    <script type="text/javascript">
                            $(document).ready(function ()
                            {
                                $("#btnSend").click(getUserNames());
                            });
                    
                            var getUserNames = function ()
                            {
                                //$("#dataGrid").hide();
                                $("#dataGrid").DataTable({
                                    dom: 'Bfrtip',
                                    buttons: ['pageLength', 'excel', 'print'],
                                    "lengthMenu": [[100, 200, 300, -1], [100, 200, 300, "All"]],
                                    "iDisplayLength": 100,
                                    "processing": true,
                                    "serverSide": true,
                                    "sAjaxSource": '/WebApp/Login/WebService1.asmx/GetData',
                                    "bJQueryUI": true,
                                    "bDeferRender": true,
                                    "fnServerData": function (sSource, aoData, fnCallback)
                                    {
                                        $.ajax({
                                            "dataType": 'json',
                                            "contentType": "application/json; charset=utf-8",
                                            "type": "POST",
                                            "url": sSource,
                                            success: function (data)
                                            {
                                                var new_data = {
                                                    "data": jQuery.parseJSON(data.d)
                                                };
                                                fnCallback(new_data);
                                                //console.log(new_data);
                                            }
                                        });
                                    },
                                    "columns": [
                                        { "data": "RowID" },
                                        { "data": "District" },
                                        { "data": "BlockName" },
                                        { "data": "VillageName" },
                                        { "data": "SchoolCode" },
                                        { "data": "SchoolName" }
                                    ]
                                });
                            }
                        </script>

                    using PortalLib.BLL;
                        using Newtonsoft.Json;
                        using System.Collections.Generic;
                        using System.Configuration;
                        using System.Data;
                        using System.Data.SqlClient;
                        using System.Web.Services;

                        namespace CitizenPortal.WebApp.Login
                        {
                            /// <summary>
                            /// Summary description for WebService1
                            /// </summary>
                            [WebService(Namespace = "http://tempuri.org/")]
                            [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
                            [System.ComponentModel.ToolboxItem(false)]
                            // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
                            [System.Web.Script.Services.ScriptService]
                            public class WebService1 : System.Web.Services.WebService
                            {
                                public class OData
                                {
                                    public List<DataOBJ> data { get; set; }
                                }

                                public class DataOBJ
                                {
                                    public string RowID, District, BlockName, VillageName, SchoolCode, SchoolName;
                                }

                                [WebMethod]
                                public string GetData()
                                {
                                    List<DataOBJ> lst = new List<DataOBJ>();
                                    PledgeBLL m_PledgeBLL = new PledgeBLL();
                                    string scmd = "SELECT RowID,District,[Block Name] AS 'BlockName',[Village Name] AS 'VillageName', " +
                                        "[School Code] AS 'SchoolCode',[School Name] AS 'SchoolName' FROM Assam_ConsolidatedData ORDER BY RowID DESC";
                                    DataTable table = new DataTable();
                                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterDB"].ConnectionString);
                                    SqlCommand cmd = new SqlCommand(scmd, con);
                                    con.Open();
                                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                                    da.Fill(table);
                                    con.Close();
                                    da.Dispose();

                                    for (int i = 0; i < table.Rows.Count; i++)
                                    {
                                        DataOBJ d = new DataOBJ();
                                        d.RowID = table.Rows[i]["RowID"].ToString();
                                        d.District = table.Rows[i]["District"].ToString();
                                        d.BlockName = table.Rows[i]["BlockName"].ToString();
                                        d.VillageName = table.Rows[i]["VillageName"].ToString();
                                        d.SchoolCode = table.Rows[i]["SchoolCode"].ToString();
                                        d.SchoolName = table.Rows[i]["SchoolName"].ToString();
                                        lst.Add(d);
                                    }

                                    //return (new JavaScriptSerializer().Serialize(lst));
                                    return JsonConvert.SerializeObject(lst, Formatting.Indented);
                                }
                            }
                        }

Answer 1

Optimization problems are not so easy to diagnose or solve. Most times the bottleneck will occur at the database level. But in itself it does not have to be related to the code. It could be a problem for the host hosting the database, or maybe not.

The importance is in the queries and their optimization covers a very broad spectrum how to summarize it in a response.

If in some way you can detail where the problem occurs, it may be easier to optimize in some way, but in principle I do not see much that could be the cause.

I would start by launching the query directly by attacking the database and observing times.

Also try to do a query where you use filters (WHERE) to see if it helps.

Edit: Use a timer to count time and see where performance is lost. In JS doing:

var time1 = performance.now();

measurementFocus();

var time2 = performance.now();
console.log(time2 - time1);

Log the time of each part putting timers: Before ajax call, just when success, after parseJSON part, after fnCallback

You know what I mean? Then bring results and we see.