File: inc_functions.asp
Function: chkForumModerator()
Description:
The function as distributed performs an unnecessary query.
Detail:
function chkForumModerator(fForum_ID, fMember_Name)
'## Forum_SQL
strSql = "SELECT " & strMemberTablePrefix & "MEMBERS.MEMBER_ID "
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS "
if strAuthType = "db" then
strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_NAME = '" & fMember_Name & "'"
else
if strAuthType = "nt" then
strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_USERNAME = '" & fMember_Name & "'"
end if
end if
set rsUsrName = my_Conn.Execute (strSql)
if rsUsrName.EOF or rsUsrName.BOF or not(ChkQuoteOk(fMember_Name)) or not(ChkQuoteOk(fForum_ID)) then
chkForumModerator = "0"
rsUsrName.close
exit function
else
MEMBER_ID = rsUsrName("MEMBER_ID")
rsUsrName.close
end if
set rsUsrName = nothing
'## Forum_SQL
strSql = "SELECT * "
strSql = strSql & " FROM " & strTablePrefix & "MODERATOR "
strSql = strSql & " WHERE FORUM_ID = " & fForum_ID & " "
strSql = strSql & " AND MEMBER_ID = " & MEMBER_ID
set rsChk = my_Conn.Execute (strSql)
if rsChk.bof or rsChk.eof then
chkForumModerator = "0"
else
chkForumModerator = "1"
end if
rsChk.close
set rsChk = nothing
end function
The code in red can be combined with the 2nd sql query to simplify the operation of this function, and speed it up at the same time.
Proposed replacement:
function chkForumModerator(fForum_ID, fMember_Name)
'## Forum_SQL
strSql = "SELECT mo.FORUM_ID "
strSql = strSql & " FROM " & strTablePrefix & "MODERATOR mo, " & strTablePrefix & "MEMBERS me "
strSql = strSql & " WHERE mo.FORUM_ID = " & fForum_ID & " "
strSql = strSql & " AND mo.MEMBER_ID = me.MEMBER_ID "
if strAuthType = "db" then
strSql = strSql & " AND me.M_NAME = '" & fMember_Name & "'"
else
if strAuthType = "nt" then
strSql = strSql & " AND me.M_USERNAME = '" & fMember_Name & "'"
end if
end if
set rsChk = my_Conn.Execute (strSql)
if rsChk.bof or rsChk.eof then
chkForumModerator = "0"
else
chkForumModerator = "1"
end if
rsChk.close
set rsChk = nothing
end function
By performing a join between the Moderator table and the Members table during the check for existence of a record for the current member id in a given forum, the database performs the 'match this user's name to his id and see if that's in the moderator table for this forum' logic faster than ASP can create the recordset object, open it, check it, close it, etc. Further, by specifically enumerating a field for the query to select from the Moderators table, you save the database time figuring out what fields are there. You really only need one field, because you're only checking for existence of a matching record - not for any specific data in it. 
Note: This mod was designed for use with MS Sql Server 7 and has not been tested for compatibility with MS Access or MySql, but it's straight SQL, so it ought to work.
Darker
Forum Locked
Topic Locked
Posted - 15 October 2000 : 18:54:48
