Loading data from SQL server to a web page

Question

There's a div in my webpage which will load data from an SQL server when the user clicks the button loadresource.

Here's the JavaScript for button click:

$("#ButtonLR").click(function () {
    //stopwatch
    var start = window.performance.now();

    $("#getData").empty();
    $("#stateB").empty();
    // display loading bar.
    $("#getData").append("<div id='loadingBar'></div>");

    var projects = [];
    var cultures = [];

    $("#CheckBoxListProjects").find("input:checked").each(function () {
        projects.push(this.value);
    });
    $("#CheckBoxListLanguage").find("input:checked").each(function () {
        cultures.push(this.value);
    });

    $.ajax({
        type: "POST",
        url: "Ajax/GetData.ashx",
        data: { projects: projects, cultures: cultures },
        dataType: "json",
    })
    .done(function (data) {
        var count = updateDataTable(data).count;

        //display statistics
        $("#stateB").append("<div id='stateBottom'></div>");
        $("#stateBottom").append("<div>" +
            "<span>" + "About  " + count + "  results  " + "</span>" +
         "</div>");
    });
});

Here's the JavaScript for load table:

function updateDataTable(data) {
    var count = 0;
    var translated = 0;

    // Clear the data table.
    $("#getData").empty();

    // Create new data table.
    $("#getData").append("<div id='resources'></div>");

    // Create header for data table.
    $("#resources").append("<div>" +
        "<span class='noedit'>" + "ProjectFile" + "</span>" +
        "<span class='noedit'>" + "Culture" + "</span>" +
        "<span class='noedit'>" + "TranslatedFlag" + "</span>" +
        "<span class='noedit'>" + "ResourceValue" + "</span>" +
        "<span class='noedit'>" + "Comments" + "</span>" +
        "<span class='noedit'>" + "IsApproved" + "</span>" +
        "</div>");
    $("#getData").append("<div id='resources'></div>");
    // For each item, append to data table.
    $.each(data, function (i, item) {
        if (item["IsApproved"] == true) {
            item["ApproveCheck"] = "<input  type='checkbox' checked>";
        }
        else {
            item["ApproveCheck"] = "<input type='checkbox' unchecked>";
        }
        if (item["TranslatedFlag"] == "1") {
            item["flag"] = "<select><option selected>1</option><option>2</option><option>3</option><option>4</option></select>";
        }
        if (item["TranslatedFlag"] == "2") {
            item["flag"] = "<select><option>1</option><option selected>2</option><option>3</option><option>4</option></select>";
        }
        if (item["TranslatedFlag"] == "3") {
            item["flag"] = "<select><option>1</option><option>2</option><option selected>3</option><option>4</option></select>";
        }
        if (item["TranslatedFlag"] == "4") {
            item["flag"] = "<select><option>1</option><option>2</option><option>3</option><option selected>4</option></select>";
        }

        if (Role == "administrator") {
            $("#resources").append("<div>" +
           "<span class='noedit'>" + item["ProjectFile"] + "</span>" +
           "<span class='noedit'>" + item["Culture"] + "</span>" +
           "<span class='noedit'>" + item["TranslatedFlag"] + "</span>" +
           "<span>" + item["ResourceValue"] + "</span>" +
           "<span>" + item["Comments"] + "</span>" +
               "<span class = 'CB'>" + item["ApproveCheck"] + "</span>" +
               "<span class = 'CB'>" + item["Ignore"] + "</span>" +
               "</div>");

            count++;
        }
        else if (Role == "manager") {
                $("#resources").append("<div>" +
               "<span class='noedit'>" + item["ProjectFile"] + "</span>" +
               "<span class='noedit'>" + item["ResourceFile"] + "</span>" +
               "<span class='noedit'>" + item["ResourceName"] + "</span>" +
               "<span class='noedit'>" + item["Culture"] + "</span>" +
               "<span class='noedit'>" + item["TranslatedFlag"] + "</span>" +
               "<span>" + item["ResourceValue"] + "</span>" +
               "<span>" + item["Comments"] + "</span>" +
               "<span class = 'CB'>" + item["ApproveCheck"] + "</span>" +
               "<span class = 'CB'>" + item["Ignore"] + "</span>" +
               "</div>");
           count++;

        else {

                $("#resources").append("<div>" +
               "<span class='noedit'>" + item["ProjectFile"] + "</span>" +
               "<span class='noedit'>" + item["ResourceFile"] + "</span>" +
               "<span class='noedit'>" + item["ResourceName"] + "</span>" +
               "<span class='noedit'>" + item["Culture"] + "</span>" +
               "<span class='noedit'>" + item["TranslatedFlag"] + "</span>" +
               "<span class = 'noedit'>" + item["ResourceValue"] + "</span>" +
               "<span class = 'noedit'>" + item["Comments"] + "</span>" +
           "<span class = 'noedit'>" + item["ApproveCheck"] + "</span>" +
           "<span class = 'noedit'>" + item["Ignore"] + "</span>" +
           "</div>");

        count++;
        }

    });
    return {
        count: count,
    };
}

Here's the Ajax to get data from an SQL server stored procedure:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Security;
using System.Data.SqlClient;
using System.Configuration;

using System.Web.Script.Serialization;

namespace webpage.Ajax
{
    public class ResourceEntry
    {
        public string ProjectFile { get; set; }
        public string Culture { get; set; }
        public string TranslatedFlag { get; set; }
        public string ResourceValue { get; set; }
        public string Comments { get; set; }
        public bool IsApproved { get; set; }

    }

    /// <summary>
    /// Summary description for GetData
    /// </summary>
    public class GetData : IHttpHandler
    {

        private string arrayToXml(string[] data)
        {
            string xml = string.Empty;

            foreach (string item in data)
            {
                xml += string.Format("<M>{0}</M>", item);
            }

            return xml;
        }


        private void getData(HttpContext context, string[] projects, string[] cultures)
        {
            List<ResourceEntry> resources = new List<ResourceEntry>();

            // connect to sql and execute stored proc
            using (SqlConnection m_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString))
            {
                m_conn.Open();

                using (SqlCommand com = new SqlCommand("GETData", m_conn))
                {
                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.Add("@ProjectList", SqlDbType.Xml).Value = arrayToXml(projects);
                    com.Parameters.Add("@CultureList", SqlDbType.Xml).Value = arrayToXml(cultures);

                    using (SqlDataReader reader = com.ExecuteReader())
                    {
                        try
                        {
                            while (reader.Read())
                            {
                                resources.Add(new ResourceEntry()
                                {
                                    ProjectFile = reader["ProjectFile"].ToString(),
                                    Culture = reader["Culture"].ToString(),
                                    TranslatedFlag = reader["TranslatedFlag"].ToString(),
                                    ResourceValue = reader["ResourceValue"].ToString(),
                                    Comments = reader["Comments"].ToString(),
                                    IsApproved = Convert.ToBoolean(reader["IsApproved"]),

                                });
                            }
                        }
                        catch (Exception ex) { }

                    }
                }

                m_conn.Close();
            }

            // Create a json serializer.
            JavaScriptSerializer json = new JavaScriptSerializer();
            json.MaxJsonLength = Int32.MaxValue;

            // Serialize the data table and output results.
            context.Response.Write(json.Serialize(resources));
        }

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            // Get request data.
            var projects = context.Request.Form.GetValues("projects[]");
            var cultures = context.Request.Form.GetValues("cultures[]");

            // Execute function.
            if (projects != null && projects.Length > 0 && cultures != null && cultures.Length > 0)
            {
                getData(context, projects, cultures);
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

The stored procedure is just simple select query and only takes 3 seconds to load 500000 rows of data.

I think the writing data to the web page is the part takes most of time.

The problem with this code is that it's too slow when loading large amount of data. It's fine if the data is under 1000 rows, but after that it takes years to load. I have a commonly used project containing 300000 rows, which will take 10 minutes to load the table and often makes the web page become unresponsive during the loading. Another problem is that the web page becomes very slow and laggy once a large amount of data is loaded, and I'm not sure how to fix these problems.

Could you please give me some idea about how to optimize my code, or any idea to fix the issue about slow loading speed? I thought about load data partially like Facebook but I'm not sure how to implement that in my code.

Answer 1

This code has quite a few problems. You should be using a HTML table for your output; it has a better fallback when CSS is disabled than your custom div/span markup. It also makes greater semantic sense.

Forgive me as I don't really know how ASP.NET works, but I would have thought that your AJAX call should be a simple GET and not POST. So that you could make a request such as:

$.ajax({
    type: "GET",
    url: "Ajax/GetData.ashx?projects=project1,project2&cultures=culture1,culture2"
});

Better yet would be to provide a RESTful API that used a JSON interface, but I think that's out of the scope of the question.

The solution to the 300,000 row problem is to limit the output from your server side code; use pagination and send a page value with your request; i.e:

$.ajax({
    type: "GET",
    url: "Ajax/GetData.ashx?projects=project1,project2&cultures=culture1,culture2&page=20"
});

Implement some UI for paging through and a key in your response that indicates the number of pages of data. Then send the appropriate amount of data back to the client - maybe 100 is a good starting point. Again I have no experience with ASP.NET so I can't help you there.

In terms of JavaScript refactoring, use truthiness to your advantage:

// Bad
if (item["IsApproved"] == true)
// Good
if (item["IsApproved"])

And I would recommend strict testing against integers, rather than testing against strings that contain integers:

// Bad
if (item["TranslatedFlag"] == "4")
// Good
if (item["TranslatedFlag"] === 4)

This is also unnecessary:

// Bad
item["ApproveCheck"] = "<input type='checkbox' unchecked>";
// Good
item["ApproveCheck"] = "<input type='checkbox'>";

Hope this helps.