Results 1 to 13 of 13
Thread: ASP Record Set Open (SLOW DOWN)
-
10-28-2002, 06:11 PM #1Newbie
- Join Date
- Oct 2002
- Location
- Chicago
- Posts
- 13
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!
-
10-28-2002, 06:38 PM #2Newbie
- Join Date
- Sep 2002
- Posts
- 20
how slow is dog slow? 30+ seconds?
have you checked for open loops etc..?
-
10-28-2002, 10:35 PM #3Junior Guru
- 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
-
10-29-2002, 06:05 AM #4Newbie
- Join Date
- Oct 2002
- Location
- Alabama
- Posts
- 13
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!
-
10-29-2002, 09:48 AM #5Newbie
- Join Date
- Oct 2002
- Location
- Chicago
- Posts
- 13
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> </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
-
10-29-2002, 10:15 AM #6Web Hosting Master
- 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.OpenDominion Web - http://www.dominion-web.com/
-
10-29-2002, 10:36 AM #7Newbie
- Join Date
- Oct 2002
- Location
- Chicago
- Posts
- 13
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
-
10-29-2002, 10:55 AM #8Web Hosting Master
- 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
-
10-29-2002, 11:06 AM #9Junior Guru
- 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-29-2002, 11:58 AM #10Newbie
- Join Date
- Oct 2002
- Location
- Chicago
- Posts
- 13
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
-
10-29-2002, 12:41 PM #11Web Hosting Master
- 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.
-
10-29-2002, 07:13 PM #12Newbie
- Join Date
- Oct 2002
- Location
- Alabama
- Posts
- 13
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!
-
10-30-2002, 12:19 PM #13WHT Addict
- 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
%>