ASP code too slow on SQL Server 2005

[kashif73]

Hi,

I have a problem with my ASP code. My code for a drop down box (which populates from a database table from MS SQL Server 2005) is running dead slow. Can anyone please guide what could be the problem. The same code when run with Access database runs faster & the dropdown populates within 20 secs, but on SQL Server 2005 it takes atleast 5 mins to populate.

 <% sqlgov = "SELECT DISTINCT DISTRICT_E FROM tbl_SubDist ORDER BY DISTRICT_E;"
			Set rs2 = dbConn.Execute(sqlgov)
			if not rs2.eof then
				rs2.movefirst
				do while not rs2.eof
				if not rs2("DISTRICT_E")="" then
				%>
	              <optgroup label="<%=rs2("DISTRICT_E")%>">
	                
					<%
			sql = "SELECT DISTINCT Subdist_E FROM tbl_SubDist where DISTRICT_E = '"&rs2("DISTRICT_E")&"' ORDER BY Subdist_E;"
			Set rs = dbConn.Execute(sql)
			if not rs.eof then
				rs.movefirst
				do while not rs.eof
					if not rs("Subdist_E")="" then
			%>
	            <option value="<%response.Write(rs("Subdist_E"))%>">
	              <%response.Write(rs("Subdist_E"))%>
                </option>
	            <%
					end if
				rs.movenext
				loop
				rs.close
			end if
			%></optgroup>
	              <%end if
				rs2.movenext
				loop
				rs2.close
			end if%>
                </select>

In my DB table, I have 109 districts & 280 sub distrcits ( for the 109 districts). I am really bogged down here & frustrated, since the code on Access db returns values in flash. I have a very limited knowledge of SQL Server 2005 & have to run the code on it.Thank you for helping me out.

[AricC]

How long does it take to return those queries in the SQL Server 05 Management Studio? Is the server the SQL is on getting hammered? 20 secs is to long to populate a list box. With 200 rows it should populate in less than 2 secs.

[kashif73]

Thanks AricC, I've figured out the problem, actually I was using the wrong cursors & that caused a delay to populate my listboxes. Thanks again.