I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.
1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown.
2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored).
The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader:
SqlConnection conn = new SqlConnection(CONNECTION);
SqlCommand ObjCmd = new SqlCommand(query, conn);
ObjCmd.CommandTimeout = 600;
SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time
// Call Read before accessing data.
View Complete Post