How to speed up loading data from database

Question

Hi! I'm using Webservice which will return the data from the database through datatable and I'll convert the datatable into byte array. In front end, I'll reconvert the bytearray to datatable and used ajaxloader to display it in the form.. It is dynamic loading. So, for each click, it is taking 10 seconds to retreive the data regardless of the size of the data. So, I used static datatable and I've loaded all data in that datatable in page load event. But, no reaction. It is taking only same time. Even, there is no data to retreive, the ajax loader is going on loading for 10 seconds. Problem is with Ajax or my webservice?? Plz, tell me some other idea??!!

My Code for listboxclick event

protected void listboxcity_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sqlvalue = string.Empty;
        //Thread.Sleep(200);

        for (int i = 0; i < listboxcity.Items.Count; i++)
        {
            if (listboxcity.Items[i].Selected == true)
                sqlvalue += listboxcity.Items[i].ToString() + ",";

        }

        if (sqlvalue.EndsWith(","))
        {
            sqlvalue = sqlvalue.Remove(sqlvalue.Length - 1);
        }


        txtprefcity.Text = sqlvalue;
        if (txtprefcity.Text != string.Empty)
        {
            listboxarea.Items.Clear();

            txtprefarea.Text = "";
            try{
            string[] strarea = txtprefcity.Text.ToString().Split(',');
            foreach (String s in strarea)
            {
                DataTable dtarea = new DataTable();
                DataTable dt = bc.ConvertByteToDataTable(objservice.GetData("getdistrictbyname", new object[] { s }));
                foreach (DataRow r in dt.Rows)
                    dtarea = bc.ConvertByteToDataTable(objservice.GetData("getarea", new object[] { int.Parse(r["countryid"].ToString()), int.Parse(r["stateid"].ToString()), int.Parse(r["districtid"].ToString()) }));
                foreach (DataRow rw in dtarea.Rows)
                {
                    listboxarea.Items.Add(rw["areaname"].ToString());
                }
            }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex.Message);
            }
        }
    }

Web Method is as follows:

public byte[] GetData(string StoredProcedureName, params object[] ParameterValues)
    {
        GC.Collect();
        using (SqlConnection MyConnection = new SqlConnection(connectionstring))
        {
            using (SqlCommand MyCommand = new SqlCommand(StoredProcedureName, MyConnection))
            {
                using (SqlDataAdapter MyAdapter = new SqlDataAdapter())
                {
                    MyConnection.Open();
                    MyCommand.CommandType = CommandType.StoredProcedure;
                    MyAdapter.SelectCommand = MyCommand;
                    SqlCommandBuilder.DeriveParameters(MyCommand);
                    int Index = 0;
                    foreach (SqlParameter Parameter in MyCommand.Parameters)
                    {
                        if (Parameter.Direction == ParameterDirection.Input || Parameter.Direction == ParameterDirection.InputOutput)
                        {
                            if (ParameterValues[Index] != null)
                            {
                                if (ParameterValues[Index].ToString() != string.Empty)
                                {
                                    Parameter.Value = ParameterValues[Index];
                                }
                                else
                                {
                                    Parameter.Value = DBNull.Value;
                                }
                            }
                            else
                            {
                                Parameter.Value = DBNull.Value;
                            }

                            Index++;
                        }
                    }
                    using (DataTable ds = new DataTable())
                    {
                        MyAdapter.Fill(ds);
                        MyConnection.Close();
                        return convertdatatabletobytearray(ds);
                    }
                }

            }
        }
    }

Answer 1

From the question it sounds like you are loading all the data from the database into this DataTable for each request; if that is the case that would explain things - you only want to fetch out the bare minimum of data from the database, via "WHERE" clauses etc.

Beyond that; you're probably going to need to profile. Put in some trace points that allow you to see where the time is being spent. It could be anywhere, but this byte[] sounds an odd thing for ajax (JSON or xml would be the obvious choices, ultimately serialized as part of http/encoding).

Oh; and static data (such as DataTablr) is a big no-no on a web-service; you're either going to corrupt it horribly, or serialize your requests (scaling to 1 user).

Answer 2

You'll probably get more accurate answers if you post some code - you might be making mistakes that are costing you time. Have you profiled anything yet? You should be able to answer your own question with good use of code profilers.