Results 1 to 13 of 13
  1. #1

    ASP Record Set Open (SLOW DOWN)

    I am developing an intranet site for my company and I am calling information from about 6 different tables. I am created a site map page that lists pretty much every entry in the database (6 Tables) no more than 100 or so records.

    My question is I am using SQL 2000 and W2k Server IIS 5.0 with nothing else on the box. I don't have more than 30 users on the site at one time, but when I load my Sitemap page it is dog slow.

    Could it be because I do this???

    set rsDept = server.CreateObject("ADODB.Recordset")
    rsDept.Open "select * from tbl_dept order by deptTitle",dbConn,1

    and I never close the connection (rsDept.Close)

    Or could it be that I am using nText for items (I don't pull nText on this page but the items have that field associated to them)

    Or could it be something else?

    Please let me know what you think!

  2. #2
    how slow is dog slow? 30+ seconds?

    have you checked for open loops etc..?

  3. #3
    Join Date
    Sep 2002
    Location
    Dallas, TX
    Posts
    205
    Yeah, how slow exactly is "slow"? Try getting tick counts before and after every major section of code. See if you can't isolate what line of code or two is taking so much time.
    justin 'at' abrogo.com
    http://www.abrogo.com
    Shared Unix Hosting

  4. #4
    always close the recordset object immediately when you are done with it. don't know if that is your problem, it would help to see the code.

    good luck!

  5. #5
    I timed the load of the page three times and this is what I get 32.94 Seconds
    30.07 Seconds
    28.45 Seconds

    I don't think there are any open loops because my server would hang up. Here is part of the code maybe someone can give me some insight. Also how do you do that Tick thing?

    The example below is calling from three tables and there are 4 seconds like this on the page at once.

    Let me know what you think!

    <%
    set rsDept = server.CreateObject("ADODB.Recordset")
    rsDept.Open "select * from tbl_dept order by deptTitle",dbConn,1
    count = 0
    while not( rsDept.eof )
    if ( ( count mod 3 ) = 0 ) then
    count1=0
    if (count1=0 and count > 0) then
    %>
    </tr>
    <% end if %>
    <tr>
    <td width="33%" valign="top"><b><% if rsDept("deptLink") <> "" then %>
    <a href="<%= rsDept("deptLink") %>" <% if rsDept("external") = 1 then
    response.write "target=""_blank"""
    end if %>><%= rsDept("deptTitle") %></a>
    <% else %>
    <% if rsDept("deptDes") <> "" or rsDept("deptImage") <> "" then %>
    <a href="dept.asp?id=<%= rsDept("id") %>"><%= rsDept("deptTitle") %></a>
    <% else %>
    <%= rsDept("deptTitle") %>
    <% end if %>
    <% end if %></b>
    <%
    set rsDeptSec = server.CreateObject("ADODB.Recordset")
    rsDeptSec.open "select * from tbl_deptSec where deptId = " & rsDept("id") & " and active = 1 order by deptSecTitle",dbConn,1

    %>
    <% if rsDept("deptLink") = "" then %>
    <ul>
    <%
    while not(rsDeptSec.EOF)
    varText1 = rsDeptSec("deptSecLink")
    set rsDeptTri = server.CreateObject("ADODB.Recordset")
    set rsDeptTri = dbConn.Execute("select * from tbl_deptTri where deptSecId = " & rsDeptSec("id") & " and active = 1 order by deptTriTitle")
    %>
    <li><% if rsDeptSec("deptSecLink") <> "" then %>
    <a href="<%= varText1 %>" <% if rsDeptSec("external") = 1 then
    response.write "target=""_blank"""
    end if %>><%= rsDeptSec("deptSecTitle") %></a>
    <% else %>
    <% if rsDeptSec("deptSecDes") <> "" or rsDeptSec("deptSecImage") <> "" then %>
    <a href="deptSec.asp?secId=<%= rsDeptSec("id") %>"><%= rsDeptSec("deptSecTitle") %></a>
    <% else %>
    <%= rsDeptSec("deptSecTitle") %>
    <% end if %>
    <% end if %>
    <%
    while not(rsDeptTri.EOF)
    varText1 = rsDeptTri("deptTriLink")
    %>
    <dd><li type="circle">
    <% if rsDeptTri("deptTriLink") <> "" then %>
    <a href="<%= varText1 %>" <% if rsDeptTri("external") = 1 then
    response.write "target=""_blank"""
    end if %> class="triNav"><%= rsDeptTri("deptTriTitle") %></a>
    <% else %>
    <% if rsDeptTri("deptTriDes") <> "" or rsDeptTri("deptTriImage") <> "" then %>
    <a href="deptTri.asp?triId=<%=rsDeptTri("id") %>" class="triNav"><%= rsDeptTri("deptTriTitle") %></a>
    <% else %>
    <span class="triNorm"><%= rsDeptTri("deptTriTitle") %></span>
    <% end if %>
    <% end if %>
    <%
    rsDeptTri.MoveNext
    wend
    %>
    <%
    rsDeptSec.MoveNext
    wend
    %>
    </ul>
    <% end if
    %>
    </td>
    <%
    else
    %>
    <td width="33%" valign="top"><b><% if rsDept("deptLink") <> "" then %>
    <a href="<%= rsDept("deptLink") %>" <% if rsDept("external") = 1 then
    response.write "target=""_blank"""
    end if %>><%= rsDept("deptTitle") %></a>
    <% else %>
    <% if rsDept("deptDes") <> "" or rsDept("deptImage") <> "" then %>
    <a href="dept.asp?id=<%= rsDept("id") %>"><%= rsDept("deptTitle") %></a>
    <% else %>
    <%= rsDept("deptTitle") %>
    <% end if %>
    <% end if %></b>
    <%
    set rsDeptSec = server.CreateObject("ADODB.Recordset")
    rsDeptSec.open "select * from tbl_deptSec where deptId = " & rsDept("id") & " and active = 1 order by deptSecTitle",dbConn,1

    %>
    <% if rsDept("deptLink") = "" then %>
    <ul>
    <%
    while not(rsDeptSec.EOF)
    varText1 = rsDeptSec("deptSecLink")
    set rsDeptTri = server.CreateObject("ADODB.Recordset")
    set rsDeptTri = dbConn.Execute("select * from tbl_deptTri where deptSecId = " & rsDeptSec("id") & " and active = 1 order by deptTriTitle")
    %>
    <li><% if rsDeptSec("deptSecLink") <> "" then %>
    <a href="<%= varText1 %>" <% if rsDeptSec("external") = 1 then
    response.write "target=""_blank"""
    end if %>><%= rsDeptSec("deptSecTitle") %></a>
    <% else %>
    <% if rsDeptSec("deptSecDes") <> "" or rsDeptSec("deptSecImage") <> "" then %>
    <a href="deptSec.asp?secId=<%= rsDeptSec("id") %>"><%= rsDeptSec("deptSecTitle") %></a>
    <% else %>
    <%= rsDeptSec("deptSecTitle") %>
    <% end if %>
    <% end if %>
    <%
    while not(rsDeptTri.EOF)
    varText1 = rsDeptTri("deptTriLink")
    %>
    <dd><li type="circle">
    <% if rsDeptTri("deptTriLink") <> "" then %>
    <a href="<%= varText1 %>" <% if rsDeptTri("external") = 1 then
    response.write "target=""_blank"""
    end if %> class="triNav"><%= rsDeptTri("deptTriTitle") %></a>
    <% else %>
    <% if rsDeptTri("deptTriDes") <> "" or rsDeptTri("deptTriImage") <> "" then %>
    <a href="deptTri.asp?triId=<%=rsDeptTri("id") %>" class="triNav"><%= rsDeptTri("deptTriTitle") %></a>
    <% else %>
    <span class="triNorm"><%= rsDeptTri("deptTriTitle") %></span>
    <% end if %>
    <% end if %>
    <%
    rsDeptTri.MoveNext
    wend
    rsDeptTri.Close
    %>
    <%
    rsDeptSec.MoveNext
    wend
    rsDeptSec.Close
    %>
    </ul>
    <% end if

    %>
    </td>
    <%
    end if
    rsDept.MoveNext
    count = count + 1
    count1 = count1 + 1
    wend
    count1=3-count1
    if count1<3 and Count1>0 then
    for i=1 to count1%>
    <td>&nbsp;</td>
    <%next%>
    </tr>
    <%else%>
    </tr>
    <%
    end if
    %>
    </td>
    </tr>
    </table>
    <% rsDept.Close %>
    Seth M. Kane
    skane@mpcproducts.com
    Intranet/Internet Specialist

    MPC Products Corporation
    ISO 9001 Certified
    Skokie, IL USA

  6. #6
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    You might want to try this to speed it up (I still wouldn't expect it to shave 25 seconds off though)

    rsDept.LockType = adLockReadOnly
    rsDept.CursorType = adOpenForwardOnly

    Call that after you have made a connection but before an rsDept.Open

  7. #7
    That didn't help at all... I also reduced the number of tables that page is calling and used only the coded listed above and it is still slow. Any other ideas?
    Seth M. Kane
    skane@mpcproducts.com
    Intranet/Internet Specialist

    MPC Products Corporation
    ISO 9001 Certified
    Skokie, IL USA

  8. #8
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    Have you tried calling the SQL directly on the SQL server to see if it's the SQL taking a long time or the ASP

  9. #9
    Join Date
    Sep 2002
    Location
    Dallas, TX
    Posts
    205
    I don't code with ASP so maybe someone else can help. Basically, the tick count is usually the time in milliseconds since some epoch (ie. just a set time in history we can compare against).

    Basically, you get a start tick count somewhere in your code, then you get an end tick count somewhere else. Subtract the former from the latter and you have the approximate time in millseconds it took to execute the code in between the tick count statements.

    It would help you to at least find which section of your code is taking so long.
    justin 'at' abrogo.com
    http://www.abrogo.com
    Shared Unix Hosting

  10. #10
    I was talking to someone and they said it could be this...

    I have the DSN Connection in an include.... What are the performance issues with that?
    Seth M. Kane
    skane@mpcproducts.com
    Intranet/Internet Specialist

    MPC Products Corporation
    ISO 9001 Certified
    Skokie, IL USA

  11. #11
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    I think basically you need to start at the beginning.

    As said above, at the beginning and end of major sections of code and queries simply echo the current timestamp to the screen.

    Then workout which section is causing the slow execution time and refine it. It helps track bugs no end! Trust me!

    If loads of time is lost where you include your DSN connection... then that's the slow bit!

    As a second method try running the same SQL statement directly on the SQL server to determine whether it's the database which is slow or the coding of the ASP script.

  12. #12

    it's all in the loops

    Your major hangup is happening because of the nested loops. Nested loops are cool, but not when they are nestedly looping through recordset objects, for example:

    while not rscon1.eof
    ...
    while not rscon2.eof
    ...
    while not rscon3.eof
    ...
    wend
    wend
    wend

    Especially when the "..." is a lot of conditional (if...then) statements like you have. It's just gonna take a while (no pun intended). One thing you might consider is using the GetRows() function so that you can close the recordset connection before you start looping. This always tends to speed things up a bit. You will have to do a little research to figure out how GetRows() works, but it is usually well worth it.

    good luck buddy!

  13. #13
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    125
    I couldn't follow your code until I cleaned it up a little. Haven't checked that it works but it's clearer what is going on.

    This kind of query is going to take an age to work. There are too many nested connections and database calls dependant on values in a previous recordsets.

    The only way to make this work with any reasonable speed is to use a View to collate your data, and then access the view using GetRows or GetString and bringing a "lump" of data back with a single call to the DB.

    Due to the complexity of the call, I'd consider using Stored Procedures as well to speed up execution.

    No matter what, it will still take some time, but you'll get it down to a couple of seconds using a View and Stored Procedure.

    If you send me a SQL Script and some table data I'll help you sort it out. A diagram of the relationships would be handy as well.


    <%
    '####################################################################
    ''# Get info from tbl_depot
    '###################################
    set rsDept = server.CreateObject("ADODB.Recordset")
    rsDept.Open "select * from tbl_dept order by deptTitle",dbConn,1
    count = 0
    while not( rsDept.eof )

    if ( ( count mod 3 ) = 0 ) then
    count1=0
    if (count1=0 and count > 0) then
    Response.write "</tr> "
    end if

    With Response
    .Write "<tr>"
    .Write "<td width=""33%"" valign="top"><b>"

    if rsDept("deptLink") <> "" then
    '# Create Link
    .Write "<a href="& rsDept("deptLink")&">"
    if rsDept("external") = 1 then .write "target=""_blank"""
    .Write ">"& rsDept("deptTitle")& "</a>"
    '# rsDept("deptLink") is blank then
    else
    if rsDept("deptDes") <> "" or rsDept("deptImage") <> "" then
    .Write "<a href=""""dept.asp?id=""" & rsDept("id") & """>" & rsDept("deptTitle") & "</a> " & vbCrLf
    else
    .write rsDept("deptTitle")
    end if
    end if

    .Write "</b> "

    '####################################################################
    '# Get Info from tbl_deptSec
    '###################################

    set rsDeptSec = server.CreateObject("ADODB.Recordset")
    rsDeptSec.open "select * from tbl_deptSec where deptId = " & rsDept("id") & " and active = 1 order by deptSecTitle",dbConn,1

    if rsDept("deptLink") = "" then
    .Write"<ul>"

    while not(rsDeptSec.EOF)
    varText1 = rsDeptSec("deptSecLink")

    set rsDeptTri = server.CreateObject("ADODB.Recordset")
    set rsDeptTri = dbConn.Execute("select * from tbl_deptTri where deptSecId = " & rsDeptSec("id") & " and active = 1 order by deptTriTitle")
    .Write "<li>"

    if rsDeptSec("deptSecLink") <> "" then
    .Write "<a href=""" & varText1 & """ " & vbCrLf

    if rsDeptSec("external") = 1 then
    .write "target=""_blank"""&rsDeptSec("deptSecTitle")&"</a>"
    else
    if rsDeptSec("deptSecDes") <> "" or rsDeptSec("deptSecImage") <> "" then
    .Write "<a href=""deptSec.asp?secId=" & rsDeptSec("id") & """>" & rsDeptSec("deptSecTitle") & "</a> " & vbCrLf
    else
    .Write rsDeptSec("deptSecTitle")
    end if
    end if

    while not(rsDeptTri.EOF)
    varText1 = rsDeptTri("deptTriLink")
    .Write "<dd><li type=""circle""> " & vbCrLf

    if rsDeptTri("deptTriLink") <> "" then
    .Write "<a href=""" & varText1 & """ <% " & vbCrLf
    if rsDeptTri("external") = 1 then "target=""_blank"""
    .Write "class=""triNav"">"&rsDeptTri("deptTriTitle")&"</a> "
    else
    if rsDeptTri("deptTriDes") <> "" or rsDeptTri("deptTriImage") <> "" then
    .Write "<a href=""deptTri.asp?triId=" & rsDeptTri("id") & """ class=""triNav"">" & rsDeptTri("deptTriTitle") & "</a> " & vbCrLf
    else
    .Write "<span class=""triNorm"">" & rsDeptTri("deptTriTitle") & "</span> " & vbCrLf
    end if
    end if

    rsDeptTri.MoveNext
    wend

    rsDeptSec.MoveNext
    wend

    .Write "</ul> "

    '####################################################################
    ' THERE IS A PROBLEM HERE. SHOULDN'T THIS "END IF" BE INSIDE THE LOOP??????

    ' wend

    '# END IF

    ' rsDeptSec.MoveNext

    '###################################
    end if
    .Write "</td> "

    else
    .Write "<td width=""33%"" valign=""top""><b>" & vbCrLf

    if rsDept("deptLink") <> "" then
    .Write "<a href=""" & rsDept("deptLink") & """ " & vbCrLf

    if rsDept("external") = 1 then .write "target=""_blank"">"

    .Write rsDept("deptTitle")&"</a> "
    else
    if rsDept("deptDes") <> "" or rsDept("deptImage") <> "" then
    .Write "<a href=""dept.asp?id=" & rsDept("id") & """>" & rsDept("deptTitle") & "</a> " & vbCrLf
    else
    .Write rsDept("deptTitle")
    end if
    end if

    .Write "</b> "

    set rsDeptSec = server.CreateObject("ADODB.Recordset")
    rsDeptSec.open "select * from tbl_deptSec where deptId = " & rsDept("id") & " and active = 1 order by deptSecTitle",dbConn,1

    if rsDept("deptLink") = "" then
    .Write "<ul>"

    while not(rsDeptSec.EOF)
    varText1 = rsDeptSec("deptSecLink")

    set rsDeptTri = server.CreateObject("ADODB.Recordset")
    set rsDeptTri = dbConn.Execute("select * from tbl_deptTri where deptSecId = " & rsDeptSec("id") & " and active = 1 order by deptTriTitle")

    .Write "<li>"

    if rsDeptSec("deptSecLink") <> "" then
    .Write "<a href=""" & varText1 & """ " & vbCrLf

    if rsDeptSec("external") = 1 then .write "target=""_blank"">"

    .Write rsDeptSec("deptSecTitle")&"</a> "
    else
    if rsDeptSec("deptSecDes") <> "" or rsDeptSec("deptSecImage") <> "" then
    .Write "<a href=""deptSec.asp?secId=" & rsDeptSec("id") & """>" & rsDeptSec("deptSecTitle") & "</a> " & vbCrLf
    else
    .Write rsDeptSec("deptSecTitle") & " " & vbCrLf
    end if
    end if

    while not(rsDeptTri.EOF)
    varText1 = rsDeptTri("deptTriLink")

    .Write "<dd><li type=""circle""> " & vbCrLf

    if rsDeptTri("deptTriLink") <> "" then
    .Write "<a href=""" & varText1 & """ " & vbCrLf

    if rsDeptTri("external") = 1 then .write "target=""_blank"""

    .Write "class=""triNav"">" & rsDeptTri("deptTriTitle") & "</a> " & vbCrLf

    else
    if rsDeptTri("deptTriDes") <> "" or rsDeptTri("deptTriImage") <> "" then
    .Write "<a href=""deptTri.asp?triId=" & rsDeptTri("id") & """ class=""triNav"">" & rsDeptTri("deptTriTitle") & "</a> " & vbCrLf
    else
    .Write "<span class=""triNorm"">" & rsDeptTri("deptTriTitle") & "</span> " & vbCrLf
    end if
    end if

    rsDeptTri.MoveNext
    wend

    rsDeptTri.Close

    rsDeptSec.MoveNext
    wend

    rsDeptSec.Close

    .Write "</ul> "

    end if
    '####################################################################
    ' THERE ARE PROBLEMS WITH THE LOGIC IN THE THREE LINES ABOVE. YOU NEED TO CHECK THE NESTING
    '###################################
    .Write "</td>"
    End with
    end if

    rsDept.MoveNext
    count = count + 1
    count1 = count1 + 1
    wend
    count1=3-count1

    if count1<3 and Count1>0 then
    for i=1 to count1
    Response.write "<td> </td>"
    next
    Response.write "</tr> "
    else
    Response.write "</tr>"
    end if

    Response.write "</td></tr></table> "
    rsDept.Close
    %>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •