If you look in topic.asp you'll see this code around line 133:
'## Forum_SQL
strSql ="SELECT " & strMemberTablePrefix & "MEMBERS.M_NAME, " & strMemberTablePrefix & "MEMBERS.M_ICQ, " & strMemberTablePrefix & "MEMBERS.M_YAHOO, " & strMemberTablePrefix & "MEMBERS.M_AIM, " & strMemberTablePrefix & "MEMBERS.M_TITLE, " & strMemberTablePrefix & "MEMBERS.M_Homepage, " & strMemberTablePrefix & "MEMBERS.M_LEVEL, " & strMemberTablePrefix & "MEMBERS.M_POSTS, " & strMemberTablePrefix & "MEMBERS.M_COUNTRY, " & strTablePrefix & "REPLY.REPLY_ID, " & strTablePrefix & "REPLY.R_AUTHOR, " & strTablePrefix & "REPLY.TOPIC_ID, " & strTablePrefix & "REPLY.R_MESSAGE, " & strTablePrefix & "REPLY.R_DATE "
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "REPLY "
strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "REPLY.R_AUTHOR "
strSql = strSql & " AND TOPIC_ID = " & Request.QueryString("TOPIC_ID") & " "
strSql = strSql & " ORDER BY " & strTablePrefix & "REPLY.R_DATE"
if strDBType = "mysql" then 'MySql specific code
...
and you'll see the exact same code again around line 236 !
Since this is rather a heavy sql statement, the question "why duplicate ?" is valid.
Well, the way the code is now, all the first statement does is retrieve the info for the paging numbers above the page, and the second retrieves all the replies for display and calculates the paging numbers for the footer of the page again.
If you just remove the second statement it won't work.
This is because of the call forGetFirst()
at line 226.
That sub also uses rs as variable to store the recordset for the Topic.
However if you declare strSql and rs inside the sub, the recordset created in the first call doesn't get destroyed and the second call can be removed.
So, add this to the sub GetFirst():
sub GetFirst()
dim rs, rs2
dim strSql
'## Forum_SQL - Get Origional Posting
and you can delete this code:
'## Forum_SQL - Get all topicsFrom DB
strSql ="SELECT " & strMemberTablePrefix & "MEMBERS.M_NAME, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_ICQ, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_YAHOO, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_AIM, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_TITLE, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_Homepage, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_LEVEL, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_POSTS, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_COUNTRY, "
strSql = strSql & strTablePrefix & "REPLY.REPLY_ID, "
strSql = strSql & strTablePrefix & "REPLY.R_AUTHOR, "
strSql = strSql & strTablePrefix & "REPLY.TOPIC_ID, "
strSql = strSql & strTablePrefix & "REPLY.R_MESSAGE, "
strSql = strSql & strTablePrefix & "REPLY.R_DATE "
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "REPLY "
strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "REPLY.R_AUTHOR "
strSql = strSql & " AND TOPIC_ID = " & Request.QueryString("TOPIC_ID") & " "
strSql = strSql & " ORDER BY " & strTablePrefix & "REPLY.R_DATE"
if strDBType = "mysql" then 'MySql specific code
if mypage > 1 then
intOffSet = CInt((mypage - 1) * strPageSize) - 1
strSql = strSql & " LIMIT " & intOffSet & ", " & CInt(strPageSize) & " "
end if
'## Forum_SQL - Get the total pagecount
strSql2 = "SELECT COUNT(" & strTablePrefix & "REPLY.TOPIC_ID) AS REPLYCOUNT "
strSql2 = strSql2 & " FROM " & strTablePrefix & "REPLY "
strSql2 = strSql2 & " WHERE TOPIC_ID = " & Request.QueryString("TOPIC_ID") & " "
set rsCount = my_Conn.Execute(strSql2)
if not rsCount.eof then
maxpages = (rsCount("REPLYCOUNT") \ strPageSize )
if rsCount("REPLYCOUNT") mod strPageSize <> 0 then
maxpages = maxpages + 1
end if
else
maxpages = 1
end if
set rs = Server.CreateObject("ADODB.Recordset")
' rs.cachesize = strPageSize
rs.open strSql, my_Conn, 3
else 'end MySql specific code
set rs = Server.CreateObject("ADODB.Recordset")
rs.cachesize = 20
rs.open strSql, my_Conn, 3
if not(rs.EOF or rs.BOF) then '## Replies found in DB
rs.movefirst
rs.pagesize = strPageSize
maxpages = cint(rs.pagecount)
rs.absolutepage = mypage
end if
end if
from lines 236 - 295 saving an expensive call for a recordset you allready retrieved.
Pierre
Forum Locked
Topic Locked
Posted - 10 March 2001 : 10:38:34
