| Author |
 Topic  |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
 Posted - 25 April 2001 : 14:20:37
|
While doing some index studies on searching, I discovered this unnecesary behaviour.
After the search query is performed, the code iterates through the result set checking the cat and forum status for each result, now say for instance you search for test, and it returns 50 results, 40 of which are in the same forum, you guessed it, the query gets executed 40 times returning the same result each time.
Solution.
look for this section of code
rs.MoveFirst
currForum = 0
currTopic = 0
do until rs.EOF
if ChkForumAccess(rs("FORUM_ID")) then
'## Forum_SQL - Find out if the Category is Locked or Un-Locked and if it Exists
strSql = "SELECT " & strTablePrefix & "CATEGORY.CAT_STATUS "
strSql = strSql & ", " & strTablePrefix & "FORUM.F_STATUS "
strSql = strSql & " FROM " & strTablePrefix & "CATEGORY "
strSql = strSql & " , " & strTablePrefix & "FORUM "
strSql = strSql & " WHERE " & strTablePrefix & "CATEGORY.CAT_ID = " & strTablePrefix & "FORUM.CAT_ID "
strSql = strSql & " AND " & strTablePrefix & "FORUM.FORUM_ID = " & rs("FORUM_ID")
set rsCFStatus = my_Conn.Execute (StrSql)
and make the changes in red below
rs.MoveFirst
currForum = 0
currTopic = 0
dim cStatus
dim fStatus
dim lastForumID
lastForumID = 1
do until rs.EOF
if ChkForumAccess(rs("FORUM_ID")) then
'## Forum_SQL - Find out if the Category is Locked or Un-Locked and if it Exists
if lastForumID <> rs("FORUM_ID") then
strSql = "SELECT " & strTablePrefix & "CATEGORY.CAT_STATUS "
strSql = strSql & ", " & strTablePrefix & "FORUM.F_STATUS "
strSql = strSql & " FROM " & strTablePrefix & "CATEGORY "
strSql = strSql & " , " & strTablePrefix & "FORUM "
strSql = strSql & " WHERE " & strTablePrefix & "CATEGORY.CAT_ID = " & strTablePrefix & "FORUM.CAT_ID "
strSql = strSql & " AND " & strTablePrefix & "FORUM.FORUM_ID = " & rs("FORUM_ID")
set rsCFStatus = my_Conn.Execute (StrSql)
cStatus = rsCFStatus("CAT_STATUS")
fStatus = rsCFStatus("F_STATUS")
lastForumID = rs("FORUM_ID")
rsCFStatus.close
end if
OK, now you need to replace every occurence of
rsCFStatus("CAT_STATUS") except the ones above to cStatus
and all the rsCFStatus("F_STATUS") to fStatus
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 25 April 2001 : 23:27:40
|
Good catch HuwR! 
I'm wondering if active.asp and possibly bookmark.asp (if using that mod) may also be guilty of this? Briefly thinking about default.asp, I think it is probably okay. Hard to tell looking through all the modified code tho'.
Edited by - work mule on 25 April 2001 23:29:13 |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 26 April 2001 : 01:28:30
|
bookmark is ok, only uses one query
|
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 26 April 2001 : 05:22:29
|
Doh!! You're right. In practice, the only way a user can bookmark something is if they already have access to it anyways. 
|
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 24 December 2001 : 03:20:19
|
HuwR, this doesn't seem like it was implemented.
This sounds like it would gain some performance on the search.asp page, in light of our recent performance discussions.
- David |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 24 December 2001 : 04:21:31
|
Good idea, maybe Spooky could test it for performance gain.
|
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 24 December 2001 : 04:48:48
|
It's a little different now with the addition of Category/Forum Subscriptions.
Here is what it looks like after incorporating the changes:
rs.MoveFirst
currForum = 0
currTopic = 0
dim Cat_Status
dim Cat_Subscription
dim Forum_Status
dim Forum_Subscription
dim lastForumID
lastForumID = 0
do until rs.EOF
if ChkForumAccess(rs("FORUM_ID"),getNewMemberNumber()) then
if lastForumID <> rs("FORUM_ID") then
'## Forum_SQL - Find out if the Category is Locked or Un-Locked and if it Exists
strSql = "SELECT C.CAT_STATUS, C.CAT_SUBSCRIPTION , F.F_SUBSCRIPTION, F.F_STATUS" & _
" FROM " & strTablePrefix & "CATEGORY C, " & strTablePrefix & "FORUM F" & _
" WHERE C.CAT_ID = F.CAT_ID" & _
" AND F.FORUM_ID = " & rs("FORUM_ID")
set rsCFStatus = my_Conn.Execute (StrSql)
Cat_Status = rsCFStatus("CAT_STATUS")
Cat_Subscription = rsCFStatus("CAT_SUBSCRIPTION")
Forum_Status = rsCFStatus("F_STATUS")
Forum_Subscription = rsCFStatus("F_SUBSCRIPTION")
lastForumID = rs("FORUM_ID")
rsCFStatus.close
set rsCFStatus = nothing
end if
Then in addition to changing all instances of
rsCFStatus("CAT_STATUS") to Cat_Status
&
rsCFStatus("F_STATUS") to Forum_Status
change all instances of
rsCFStatus("CAT_SUBSCRIPTION") to Cat_Subscription
&
rsCFStatus("F_SUBSCRIPTION") to Forum_Subscription |
|
|
|
James
Average Member
USA
539 Posts |
Posted - 24 December 2001 : 16:54:07
|
quote:
Then in addition to changing all instances of
rsCFStatus("CAT_STATUS") to Cat_Status
&
rsCFStatus("F_STATUS") to Forum_Status
change all instances of
rsCFStatus("CAT_SUBSCRIPTION") to Cat_Subscription
&
rsCFStatus("F_SUBSCRIPTION") to Forum_Subscription
No other files need changing other than search.asp, correct?
-
*Interested in Radio Control*
*The RC Web Board - http://www.rcwebboard.com/* |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 29 December 2001 : 01:56:18
|
yes, just search.asp
fixed in v3.4 |
|
|
| |
Topic |
|
Topic Locked
