Why Post Back Is taking Too Long time to retrieve data from linked server

Question

I am working with a portal which have grid view and inside grid view i have two text box lets name as txtEmployeeNumber and txtEmployeeName. And i have created an event text_changed on txtEmployeeNumber so that after post back second text will fill respective data such as EmployeeName Please guide me what should i change in that. I tried OPENQUERY which is handled at the source (linked server) but not working as accepted. Is there any another approach to retrieve data as fast as possible.

I also use trace and trigger on page and inside my UpdatePanel respectively

Trace="true"

and

<Triggers> <asp:AsyncPostBackTrigger ControlID="txtEmployeeNumber" EventName="TextChanged" /> </Triggers>

in my aspx page for fast postback but no any changes i found

My Stored Procedure

CREATE procedure [dbo].[SP_Get_Employee_Name]  
 @Employee_ID nvarchar(max)  
AS  
BEGIN  

 DECLARE @emp_name nvarchar(max)  
 SELECT @emp_name = Employee_Name FROM [938.987.644.336].[something].[dbo].[tbl_Employee]  
 WHERE Personnel_Number= @Employee_ID  

 IF (@emp_name is  null or @emp_name = '')  
 BEGIN  
  SELECT @emp_name = Name from [938.987.644.336].[something].[dbo].[tbl_Contract1_Employee]  
  WHERE [Unique id]= '110'+ @Employee_ID  
 END  

 SELECT @emp_name AS Employee_Name  
END

C# code :

protected void txtEmployeeNumber_TextChanged(object sender, EventArgs e)
        {
            string EmployeeNo = "";
            foreach (GridViewRow row in grdRegister.Rows)
            {
                if (row.RowType != DataControlRowType.DataRow)
                    continue;

                EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;

                SqlCommand cmd = new SqlCommand("SP_Get_Employee_Name", Con.OpenConnection());
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Employee_ID", EmployeeNo);
                Con.OpenConnection();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    (row.Cells[2].FindControl("txtEmployeeName") as TextBox).Text = dt.Rows[0]["Employee_Name"].ToString();
                }
            }
        }

Answer 1

First of all you need to identify area of blockage then only you optimize your search. Here three scenarios are happening:

1. Text change event fired brower.
2. Text change event on the server in C# .
3. Retrieving data from sql server.

For most of the cases blockage happens in point 2 and 3. Now the best solution is to retrieve the data once in the server and keep in the application variable or in the javascript session (if its not too much) in that case you can avoid point 2 and 3.

But if you have too much data then try using datareader instead of dataset as its faster than dataset. Try these and share your results.

One more point I want to say that on each letter typing you are hitting the server I think you need to find a way so that you don't each on each letter typing instead collectively. I faced it in one of my project, so I used some jquery library for the same.