locked
JSON Retrieval Speed RRS feed

  • Question

  • User-1305530094 posted

     Hi all

    I m using the following codes to populate HTML table,

     <WebMethod()> _
        Public Function SrcTblReg(ByVal SrchTxt As String, ByVal FnameSrctxt As String, _
                                      ByVal SnameSrctxt As String, ByVal TnameSrctxt As String, _
                                      ByVal LnameSrctxt As String, ByVal TelSrcTxt As String, _
                                      ByVal SSNSrcTxt As String, ByVal EmailSrctxt As String, _
                                      ByVal DOBSrcTxt As String)
            Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand("TblRegSearch", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    If (String.IsNullOrEmpty(SrchTxt)) Then
                        cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = SrchTxt.Trim()
                    End If
                    If (String.IsNullOrEmpty(FnameSrctxt)) Then
                        cmd.Parameters.Add("@Fname", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Fname", SqlDbType.NVarChar).Value = FnameSrctxt
                    End If
                    If (String.IsNullOrEmpty(SnameSrctxt)) Then
                        cmd.Parameters.Add("@Sname", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Sname", SqlDbType.NVarChar).Value = SnameSrctxt
                    End If
                    If (String.IsNullOrEmpty(TnameSrctxt)) Then
                        cmd.Parameters.Add("@Tname", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Tname", SqlDbType.NVarChar).Value = TnameSrctxt
                    End If
                    If (String.IsNullOrEmpty(LnameSrctxt)) Then
                        cmd.Parameters.Add("@Lname", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Lname", SqlDbType.NVarChar).Value = LnameSrctxt
                    End If
                    If (String.IsNullOrEmpty(TelSrcTxt)) Then
                        cmd.Parameters.Add("@TelMob", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@TelMob", SqlDbType.NVarChar).Value = TelSrcTxt
                    End If
                    If (String.IsNullOrEmpty(SSNSrcTxt)) Then
                        cmd.Parameters.Add("@SSnum", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@SSnum", SqlDbType.NVarChar).Value = SSNSrcTxt
                    End If
                    If (String.IsNullOrEmpty(EmailSrctxt)) Then
                        cmd.Parameters.Add("@Email", SqlDbType.Int).Value = DBNull.Value
                    Else
                        cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = EmailSrctxt
                    End If
                    If (String.IsNullOrEmpty(DOBSrcTxt)) Then
                        cmd.Parameters.Add("@DOB", SqlDbType.Date).Value = DBNull.Value
                    Else
                        Dim DOBDte As DateTime = String.Format("{0:dd-MMMM-yyyy}", DOBSrcTxt)
                        cmd.Parameters.Add("@DOB", SqlDbType.Date).Value = DOBDte
                    End If
                    cmd.Connection = con
                        Dim ds As New DataSet()
                        Using sda As New SqlDataAdapter(cmd)
                            sda.Fill(ds, "TblRegSrc")
                        End Using
                        Return ds.GetXml()
                    End Using
                End Using
        End Function
    function srctbl() {
               
                $(function () {
                    var obj = {};
                   
                    obj.SrchTxt = $('#srctxt').val()
                    obj.FnameSrctxt =$('#fnamesrc').val()
                    obj.SnameSrctxt = $('#snamesrc').val()
                    obj.TnameSrctxt = $('#tnamesrc').val()
                    obj.LnameSrctxt = $('#lnamesrc').val()
                    obj.TelSrcTxt = $('#telsrc').val()
                    obj.SSNSrcTxt = $('#ssnsrc').val()
                    obj.EmailSrctxt = $('#emailsrc').val()
                    obj.DOBSrcTxt = $('#dobsrc').val()
                    
                    $.ajax({
                        type: "POST",
                        url: "../CONFIG/WebSerTblsSearch.asmx/SrcTblReg",
                        data: JSON.stringify(obj),
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: OnSuccess2,
                        failure: function (response) {
                            alert('f' + response.d);
                        },
                        error: function (response) {
                            alert('E' + response.d);
                        }
                    });
                });
    
                function OnSuccess2(response) {
                    var xmlDoc = $.parseXML(response.d);
                    var xml = $(xmlDoc);
                    var customers = xml.find("TblRegSrc");
                    var row = $("[id*=RegSrc] > tbody tr:last-child").clone(true);
                    $("[id*=RegSrc] tr").not(':has(th)').remove();
                    $.each(customers, function () {
                        $("#ptfn", row).html($(this).find("Column1").text());
                        $("#ptname", row).html($(this).find("FullName").text());
                        $("#ptdob", row).html($(this).find("DOB").text());
                        $("[id*=RegSrc]").append(row);
                        row = $("[id*=RegSrc] > tbody tr:last-child").clone(true);
                    })
                }
            }


    The code performs search in a table, whenever I get more than 500 rows, the speed in data retrieval drops drastically, the table takes a long time to populate.

    any idea how to speed the process up?

    thanks

    Wednesday, April 26, 2017 8:18 AM

Answers

  • User753101303 posted

    Hi,

    It's best to use tools such as F12 to see first what happens but my first thought would be that the page is updated too often.., you could likely keep a blank clone to avoid searching the last row again and again etc...

    But once again use F12 Tools to see if this is client side or server side and if it is a JavaScript or rendering issue maybe. It's also best to see which effect a change you are doing have...

    Edit: also using Json might be quicker than XML ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 25, 2017 4:54 PM

All replies

  • User-707554951 posted

    Hi Embryologist,

    , whenever I get more than 500 rows, the speed in data retrieval drops drastically

    You said the speed in data retrieval drops drastically, there may have main two reason for this.

    1. the speed drops when retrieve data from database,

    2.the speed drops when display record in page.

    Which reason lead to the speed drops and you want to improve?

    Best Regards

    Cathy

    Thursday, April 27, 2017 9:22 AM
  • User-1305530094 posted

    the speed in displaying records in the page 

    Monday, May 1, 2017 9:01 AM
  • User753101303 posted

    Hi,

    It's best to use tools such as F12 to see first what happens but my first thought would be that the page is updated too often.., you could likely keep a blank clone to avoid searching the last row again and again etc...

    But once again use F12 Tools to see if this is client side or server side and if it is a JavaScript or rendering issue maybe. It's also best to see which effect a change you are doing have...

    Edit: also using Json might be quicker than XML ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 25, 2017 4:54 PM