User Profiles: Fixing Differences in Performance

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/59576?pagenum=1
05 November 2025, 08:46

Topic

SiSL
User Profiles: Fixing Differences in Performance
24 September 2005, 11:56


What may cause this?
Here some results,

As admin, my profile page (when looking at others profiles pop_profile.asp) is created like 0.8 seconds, as user 32.85 seconds... But there should not be that much difference between admin & user, right? I mean where I can try to find solution in codes?
What may cause this?
Mods I have installed:
- Anti-spam mod (cant recall exact name)
- Avatar mod
- Poll mod
- Active Users

NOTE: I have tried it several times and got it tried to some of my users.
TXT Version of POP_PROFILE.ASP
TXT Version of INC_PROFILE.ASP
Moved and renamed by ruirib<

 

Replies ...

ruirib
24 September 2005, 12:03


Very weird. That's not a common issue at all. Maybe post a link to a text version of your pop_profile.asp and inc_profile.asp files. <
OneWayMule
24 September 2005, 12:06


You might want to try to disable the Recent Topics feature (Admin Options -> Member Details Configuration) and see if it makes any difference.<
SiSL
24 September 2005, 12:12


ruirib: Added txt versions of files into first posts.
OneWayMule: I will try and let you know the results.<
SiSL
24 September 2005, 12:16


Recent topics made the difference, but what causing difference in recent topics between admin & users I wonder :(<
Podge
24 September 2005, 12:40


This won't help you but it happened here at Snitz once - http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=57286&whichpage=1

No cause found. It was fixed by moving to a new server.<
SiSL
24 September 2005, 13:17


It is almost very same problem. HuWR was getting fast results as admin, but users dont, hence I dont have chance to change my host since it is co-location computer. Just wondering if it is from MSSQL settings should be different or what.


<
ruirib
24 September 2005, 15:20


SiSL,

In your pop_profile.asp, around line# 399, here now you have,
Code:

                                        strsql = "SELECT F.FORUM_ID"
					strSql = strSql & ", T.TOPIC_ID"
					strSql = strSql & ", T.T_SUBJECT"
					strSql = strSql & ", T.T_STATUS"
					strSql = strSql & ", T.T_LAST_POST"
					strSql = strSql & ", T.T_REPLIES "
					strSql = strSql & " FROM ((" & strTablePrefix & "FORUM F LEFT JOIN " & strTablePrefix & "TOPICS T"
					strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN " & strTablePrefix & "REPLY R"
					strSql = strSql & " ON T.TOPIC_ID = R.TOPIC_ID) "
					strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
					strSql = strSql & " AND (T.T_AUTHOR = " & ppMember_ID
					strSql = strSql & " OR R.R_AUTHOR = " & ppMember_ID & ")"
					strSql = strSql & " AND (T_STATUS < 2 OR R_STATUS < 2)"
					strSql = strSql & " AND F.F_TYPE = 0"
					strSql = strSql & " ORDER BY T.T_LAST_POST DESC, T.TOPIC_ID DESC"

replace it by
Code:

	                                strsql = "SELECT F.FORUM_ID"
					strSql = strSql & ", T.TOPIC_ID"
					strSql = strSql & ", T.T_SUBJECT"
					strSql = strSql & ", T.T_STATUS"
					strSql = strSql & ", T.T_LAST_POST"
					strSql = strSql & ", T.T_REPLIES "
					strSql = strSql & " FROM (" & strTablePrefix & "FORUM F INNER JOIN " & strTablePrefix & "TOPICS T"
					strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID)"
					strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
					strSql = strSql & " AND (T.T_AUTHOR = " & ppMember_ID & ")"
					strSql = strSql & " AND (T_STATUS < 2)"
					strSql = strSql & " AND (T_REPLIES < 1) AND F.F_TYPE = 0"
					strSql = strSql & " UNION"
					strsql = strsql & " SELECT F.FORUM_ID"
					strSql = strSql & ", T.TOPIC_ID"
					strSql = strSql & ", T.T_SUBJECT"
					strSql = strSql & ", T.T_STATUS"
					strSql = strSql & ", T.T_LAST_POST"
					strSql = strSql & ", T.T_REPLIES "
					strSql = strSql & " FROM ((" & strTablePrefix & "FORUM F INNER JOIN " & strTablePrefix & "TOPICS T"
					strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID) INNER JOIN " & strTablePrefix & "REPLY R"
					strSql = strSql & " ON T.TOPIC_ID = R.TOPIC_ID) "
					strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
					strSql = strSql & " AND (R.R_AUTHOR = " & ppMember_ID & ")"
					strSql = strSql & " AND (T_STATUS < 2 OR R_STATUS < 2)"
					strSql = strSql & " AND F.F_TYPE = 0"
					strSql = strSql & " ORDER BY T.T_LAST_POST DESC, T.TOPIC_ID DESC"

Let me know if it improves on your problem.
P.S.:This code is good only for Access or SQL Server. MySQL versions that support UNIONS (4.0+) should also be ok, BUT NO TESTING WAS DONE.<
SiSL
24 September 2005, 17:51


It is now much faster for 'admin' but still having (will keep code)

This page was generated in 0.41 seconds. = admin
This page was generated in 23.06 seconds. = user

You can see at http://www.amedia.org/forum/

user: demo
pass: test<
ruirib
24 September 2005, 18:02


Can you compare both versions of the file (with and without the change) regarding the recent posts that appear for each user? I think they should be, but want to be sure...<
ruirib
24 September 2005, 18:08


The difference in performance quite surely results from the fact that each topic must be evaluated against users permissions, to ensure that the user viewing the profile has access to the topics.
Using getrows instead of the navigating the recordset could also help improving the performance.<
ruirib
24 September 2005, 19:19


SiSL
24 September 2005, 20:34


I did some testing my avarage non-admin user see ~23-24 seconds..
With latest code, that didnt change actually. Above user & pass active for each code changes.
Is it from setup variables or something I had to do with settings of MSSQL to increase performance? <
modifichicci
25 September 2005, 02:55


Code:
P.S.:This code is good only for Access or SQL Server. MySQL versions that support UNIONS (4.0+) should also be ok, BUT NO TESTING WAS DONE.

Mysql 4.0.25
I get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Unknown column 'T.TOPIC_ID' in 'ORDER BY'

/forumlang/test/pop_profile.asp, line 565

and this is the sqlstr
SELECT F.FORUM_ID, T.TOPIC_ID, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, T.T_REPLIES FROM (FORUM_FORUM F INNER JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) WHERE (T_DATE > '20050826090251') AND (T.T_AUTHOR = 2) AND (T_STATUS < 2) AND (T_REPLIES < 1) AND F.F_TYPE = 0 UNION SELECT F.FORUM_ID, T.TOPIC_ID, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, T.T_REPLIES FROM ((FORUM_FORUM F INNER JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) INNER JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) WHERE (T_DATE > '20050826090251') AND (R.R_AUTHOR = 2) AND (T_STATUS < 2 OR R_STATUS < 2) AND F.F_TYPE = 0 ORDER BY T.TOPIC_ID DESC
<
ruirib
25 September 2005, 05:18


I don't know MySQL that well. Don't use this until I get the chance to test it with it.<
SiSL
25 September 2005, 13:54


ruirib: Found an error I made while pasting your code, it seems now it works like a charm :),

Thanks a lot

Here is some new results with "Recent Topics" on

This page was generated in 0.08 seconds. This page was generated in 0.17 seconds. This page was generated in 0.19 seconds. This page was generated in 0.2 seconds.<
ruirib
25 September 2005, 13:58


SiSL, that's great. I was thinking I had spent a whole lot of time for nothing. Likely you're one of the few people who can really check whether there is an advantage in coding the page like this.<
muzishun
26 September 2005, 16:29


Would this be something worthwhile for others to add into their pages? I haven't really experience any issues yet, but if this helps with performance, I don't really see how it could be a bad thing.<
ruirib
26 September 2005, 16:37


My guess is that the change is only relevant when you have someone who has a lotta posts recently and it takes a lotta time for the server to navigate the recordset until it finds 10 topics to display.<
SiSL
26 September 2005, 16:44


Originally posted by ruirib
My guess is that the change is only relevant when you have someone who has a lotta posts recently and it takes a lotta time for the server to navigate the recordset until it finds 10 topics to display.

True, such as the forum database I'm trying on, has 10K users with over 300K unarchieved posts which makes it almost impossible below 20 seconds for a 'Normal User' to check 10 messages after comparing forums that normal user has access or not.<
muzishun
26 September 2005, 17:55


Gotcha. Thanks.<
gpspassion
25 June 2007, 16:12


Stumbled onto this topic as I've noticed my CPU ressources going out of control when someone tries to use the "Find all non-archived posts by xxxx" feature. I have people with 10,000 messages so understandbly that's a lot to handle.
I was going to try this MOD, but it seems to be for "recent topics" mostly ? Any idea how the "Find all non-archived posts by xxxx" could be optimized ? Maybe limiting it to the 100 latest messages ?<
ruirib
25 June 2007, 17:41


Yep, that would be a way. Sometime ago I wrote an optimized search.asp for SQL Server. Have you tried using it?<
gpspassion
25 June 2007, 19:46


If "sometime" is 3 years ago, yes I had ugraded to your search.asp MOD to make it display faster, if not I probably missed it ;-) I limit the search to subjects too.<
ruirib
25 June 2007, 19:56


No changes since then. If you're using my search.asp, paging could help too, cause I don't think it uses it now. <
gpspassion
29 June 2007, 18:23


you mean displaying 15 resutls per page ? Yes, that would be useful, any pointers on how to implement that ?<
ruirib
29 June 2007, 18:28


You could try and use the paging in a normal search.asp to do it. It won't be any different... but it will also mean adding the code to change pages, which is not present.
Anyway, as it can be guess from a current discussion started by Podge in the SQL Server forum, using the normal Snitz paging may not help that much. I'd probably advise disabling that feature, at least for normal users.<
© 2000-2021 Snitz™ Communications