| Author |
 Topic  |
|
|
SiSL
Average Member
Turkey
671 Posts |
 Posted - 18 October 2005 : 22:05:03
|
I'm not sure why but even slightest code changes can affect speed on my boards dramatically...
Last time I've noticed as 'admin', my post.asp loading over 20 seconds, but that speed thing not effecting users at all.
So I decided to do some WriteFooter / response end results to see timer.. Here what I did to speed it up...
Found following lines around 1590.
' DEM --> Added Select of Moderation Fields
strSQL = "SELECT C.CAT_MODERATION, F.F_MODERATION "
strSQL = strSQL & "FROM " & strTablePrefix & "CATEGORY C, " & strTablePrefix & "FORUM F "
strSQL = strSQL & " WHERE C.CAT_ID = " & strRqCatID
strSQL = strSQL & " AND F.FORUM_ID = " & strRqForumID
set rsa = my_Conn.Execute (strSql)
' ## Moderators and Admins can see unmoderated posts.
if (mLev = 4) or (chkForumModerator(strRqForumId, strDBNTUserName) = "1") then
Moderation = "N"
else
if strModeration = 1 and rsa("CAT_MODERATION") = 1 and (rsa("F_MODERATION") = 1 or rsa("F_MODERATION") = 3) then
Moderation = "Y"
else
Moderation = "N"
end if
end if
' DEM --> End of Moderation Code
I cut the lines in red... and placed it over first line in this code... Therefor, if you are admin, it does not check if you are moderator or what...
So it has become like this:
if (mLev = 4) or (chkForumModerator(strRqForumId, strDBNTUserName) = "1") then
Moderation = "N"
else
' DEM --> Added Select of Moderation Fields
strSQL = "SELECT C.CAT_MODERATION, F.F_MODERATION "
strSQL = strSQL & "FROM " & strTablePrefix & "CATEGORY C, " & strTablePrefix & "FORUM F "
strSQL = strSQL & " WHERE C.CAT_ID = " & strRqCatID
strSQL = strSQL & " AND F.FORUM_ID = " & strRqForumID
set rsa = my_Conn.Execute (strSql)
' ## Moderators and Admins can see unmoderated posts.
if strModeration = 1 and rsa("CAT_MODERATION") = 1 and (rsa("F_MODERATION") = 1 or rsa("F_MODERATION") = 3) then
Moderation = "Y"
else
Moderation = "N"
end if
end if
' DEM --> End of Moderation Code
With that change somehow my Speed gets back to 4 seconds than 18-24 seconds as admin..
You know what speed me up more than that?
I added following line below final line of above code:
set rsa = nothing
Now it is 0.04 seconds!
If I did something wrong please remind me, if not let me know if you have speed issues like me.< |
CHIP Online Forum
My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
|
Edited by - Davio on 26 September 2006 05:44:52 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 October 2005 : 03:27:27
|
quote:
Found following lines around 1590.
There are < 1500 lines in a standard post.asp  so for anyone else it is around line 1357.
I don't see anythig wrong with your changes, in fact they look pretty sensible, however I fail to see what effect setting rsa to nothing would have on the speed of the query, it should only affect the number of open connections not query speed, but obviously it ought to be done.
Could one of the Admin's move this to the bug forum since this ought to be fixed in the base code.< |
 |
|
|
SiSL
Average Member
Turkey
671 Posts |
Posted - 19 October 2005 : 03:48:44
|
Oh sorry, that must be because of Poll mod
I guess open connections effect speed a lot in my case, since same computer running 4 different Snitz and having huge number of visitors on same MSSQL
However my theory is kinda more like this, correct me if I'm wrong : "Once a recordset open, opening another without closing causing to be slow" , at least that's at my MSSQL thing.
I have some more problems to solve (especially with mods like Events Calender doing same thing, this time users getting too slow if events calender there)
More interesting another result at me, this only happens when a page checks for "moderated" or hidden stuff.. I'll check connections again I guess /sigh
EDIT: I checked more codes what causing slowness. final result: Any DO.. LOOP causes slowness on large databases (not suprising eh?)
< |
CHIP Online Forum
My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
|
Edited by - SiSL on 19 October 2005 14:23:29 |
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 19 October 2005 : 07:06:53
|
Just FYI ; Implemented this, and went from 0,3224 seconds on post.asp to 0,3145 seconds (0,2832 seconds for non-registered visitors).
 Not really the 500x speed improvement you're getting, but still.
I have to agree wit HuwR though ; I also fail to see this increasing speed from 20 seconds to 0,04 seconds, but as it's a correct improvement/fix of the basecode, and as it has solved your performance issue, I'd also say that it should go into the basecode. (Not that I have anything to say about that, but ok.  )< |
portfolio - linkshrinker - oxle - twitter |
Edited by - MarcelG on 19 October 2005 14:23:29 |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 October 2005 : 14:56:55
|
quote:
Originally posted by SiSL
Oh sorry, that must be because of Poll mod
I guess open connections effect speed a lot in my case, since same computer running 4 different Snitz and having huge number of visitors on same MSSQL
However my theory is kinda more like this, correct me if I'm wrong : "Once a recordset open, opening another without closing causing to be slow" , at least that's at my MSSQL thing.
I have some more problems to solve (especially with mods like Events Calender doing same thing, this time users getting too slow if events calender there)
More interesting another result at me, this only happens when a page checks for "moderated" or hidden stuff.. I'll check connections again I guess /sigh
EDIT: I checked more codes what causing slowness. final result: Any DO.. LOOP causes slowness on large databases (not suprising eh?)
if the number of open connections is affecting performance then your MSSQL server is either grossly under specced or massively over-subscribed and in dire need of either more ram or faster/ multiple processors.
Yes do loops are very slow, this could be remedied somewhat by either using arrays and getrows or disconnected record sets< |
Edited by - HuwR on 19 October 2005 16:23:42 |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 20 October 2005 : 04:07:52
|
quote:
Originally posted by SiSL
Oh and MarcelG, it is not about post.asp, it is about post.asp with "Reply" or "Quote" things with "admin" status. Casual post.asp was fast for new topics.
Ah, ok. That makes sense. Sorry if I missed that part earlier.< |
portfolio - linkshrinker - oxle - twitter |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 20 October 2005 : 05:52:04
|
While we're on the subject of "speeding up" post.asp, something I mentioned to Davio a while back, was the way the combo boxes for allowed members and moderators when editing/creating forums are currently poulated using getMemberName and therefore an additional database hit for each member on the list. However post.asp already contains queries to retrieve the MEMBER_IDs to pass through that function so we could save a couple of trips to the database by pulling the M_NAMEs with the same query. I've done this on Woo.ie and it's really helped speed things up.
Find the following beginning on line 857 of post.asp and add the code in green:strSql = "SELECT O.MEMBER_ID, M.M_NAME"
strSql = strSql & " FROM " & strTablePrefix & "MODERATOR O, " & strMemberTablePrefix & "MEMBERS M"
strSql = strSql & " WHERE O.FORUM_ID = " & strRqForumID & " AND M.MEMBER_ID=O.MEMBER_ID" Find the following on line 867 and add the code in green:allForumModeratorData = rsForumModerator.GetRows(adGetRowsRest)
recForumModeratorCount = UBound(allForumModeratorData,2)
moMEMBER_ID = 0
moM_NAME = 1 Find the following on line 917, add the code in green and replace the code in red:ForumModeratorMemberID = allForumModeratorData(moMEMBER_ID, iForumModerator)
ForumModeratorMemberName = chkString(allForumModeratorData(moM_NAME, iForumModerator), "display")
if ForumModeratorMemberID <> "" then
Response.Write " <option value=""" & ForumModeratorMemberID & """>" & ForumModeratorMemberName & "</option>" & vbNewline
end if Find the following on line 1223 and add the code in green:strSql = "SELECT A.MEMBER_ID, M.M_NAME"
strSql = strSql & " FROM " & strTablePrefix & "ALLOWED_MEMBERS A, " & strMemberTablePrefix & "MEMBERS M"
strSql = strSql & " WHERE A.FORUM_ID = " & strRqForumID & " AND M.MEMBER_ID=A.MEMBER_ID" Find the following on line 1233 and add the code in green:allAllowedMemberData = rsAllowedMember.GetRows(adGetRowsRest)
recAllowedMemberCount = UBound(allAllowedMemberData,2)
amMEMBER_ID = 0
amM_NAME = 1 Find the following on line 1282, add the code in green and replace the code in red:AllowedMembersMemberID = allAllowedMemberData(amMEMBER_ID, iAllowedMember)
AllowedMembersMemberName = chkString(allAllowedMemberData(amM_NAME, iAllowedMember), "display")
if AllowedMembersMemberID <> "" then
Response.Write " <option value=""" & AllowedMembersMemberID & """>" & AllowedMembersMemberName & "</option>" & vbNewline
end if < |
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 21 October 2005 : 07:22:48
|
Of course, it all depends on how many moderators of allowed members you have in any given forum but every little helps
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 06 August 2006 : 02:52:04
|
I have added the changes suggested by SiSl in his first post to the 3.4.06 code base. I have also closed all objects that have been opened by the SET keyword in post.asp. Found quite a few of them.
Will add Shaggy's code later. But need persons to test it out on a forum with a large number of moderators and members to notice the speed increase made by his code changes.< |
Support Snitz Forums
|
Edited by - Davio on 06 August 2006 02:52:27 |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 25 August 2006 : 02:41:51
|
Shaggy's code has been added to 3.4.06.
Also more objects were closed in post_info.asp file.< |
Support Snitz Forums
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 25 August 2006 : 05:13:59
|
quote:
Originally posted by Davio
Shaggy's code has been added to 3.4.06.
Yay! What do you know, maybe I do have some use 'round here after all! 
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
| |
Topic |
|
Forum Locked
Topic Locked
