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.