I have a suggestion regarding the chkUser() function and a new index for MSSQL users.
1. Create an index on m_name (either clustered or non-clustered).
2. Change the chkUser() function to the following:
function chkUser(fName, fPassword, fAuthor)
dim rsCheck
dim strSql
'## Forum_SQL
strSql = "SELECT MEMBER_ID, M_LEVEL, M_NAME, M_PASSWORD, M_STATUS "
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " WHERE " & strDBNTSQLName & " = '" & ChkString(fName, "SQLString") & "' "
Set rsCheck = my_Conn.Execute(strSql)
if rsCheck.BOF or rsCheck.EOF or not(ChkQuoteOk(fName)) or not(ChkQuoteOk(fPassword)) then
MemberID = -1
chkUser = 0 '## Invalid Password
if strDBNTUserName <> "" and chkCookie = 1 then
Call ClearCookies()
strDBNTUserName = ""
end if
else
if rsCheck("M_STATUS") = 1 and ((strAuthType="db" and fPassword=rsCheck("MEMBER_ID")) or (strAuthType<>"db")) then
MemberID = rsCheck("MEMBER_ID")
if (rsCheck("MEMBER_ID") & "" = fAuthor & "") and (cLng(rsCheck("M_LEVEL")) <> 3) then
chkUser = 1 '## Author
else
select case cLng(rsCheck("M_LEVEL"))
case 1
chkUser = 2 '## Normal User
case 2
chkUser = 3 '## Moderator
case 3
chkUser = 4 '## Admin
case else
chkUser = cLng(rsCheck("M_LEVEL"))
end select
end if
else
MemberID = -1
chkUser = 0 '## Invalid Password
if strDBNTUserName <> "" and chkCookie = 1 then
Call ClearCookies()
strDBNTUserName = ""
end if
end if
end if
rsCheck.close
set rsCheck = nothing
end function
How I arrived at this.
We launched the forums on our Trains.com site (still working an a couple of things 
). The site has 116K+ registered members to whom we sent out an announcement a couple of days ago, inviting them back.
Well, within minutes the traffic on the site spiked and everything slowed down to the point of being unuseable. It was pretty bad. I felt like I was in the middle of an IBM commercial. 
Checked the webserver and it was fine. Checked the database server and that sucker was pegged. Within a couple of hours, the number of visitors dropped and the site was slow but useable.
The next day I ran a couple of profiles against the database and before I left I ran the index tuning wizard against the trace file on our development box. It took about 8.5 hours. 
While watching the one trace running (using the wizard option for worst query performance) I noticed the slowest queries was the select statements with m_name, m_password, m_status in the where clause. The results of the wizard suggested creating a clustered index on the m_name field, an non-clustered index on m_name, m_password, m_status, and a few others.
Thinking about the queries I had seen, I realized it was the chkUser function. The first thing that happens when someone visits the forums, or the rest of the site for that matter, the query runs looking for m_name, m_password, m_status which didn't match any indexes and forced a tablescan against 116k+ records. Having to scan the password field for 64 character strings probably didn't help. 
Of course I could accept the recommendation of creating an index on the three fields, however it struck me I could do something better.
I created the clustered index. Then I modified the function so that the SQL statement only searched on the username (m_name). The password and member status fields were already include in the select statement, so when the matching record was returned, I compared the password values and checked the member level.
The result was that now the performance of the site is significantly faster than it has been. An unexpected side benefit is the Members page returns search results faster -- especially when searching on username or names starting with one of the letters. 
Topic Locked
Posted - 19 July 2003 : 16:55:53
