| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 24 March 2002 : 08:58:15
|
That's a great improvement indeed  . I hope that can be used with 3.4...
Congratulations on your work.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
Kal Corp
Average Member
USA
878 Posts |
Posted - 24 March 2002 : 10:02:55
|
quote:
If your not using Topic Moderation then make sure you switch it off in the Admin Options. This alone reduces the number of queries by around 30%.
I had Just tested this on my forum, im getting 80-100% better page build time.
I only had one cat and topic that had moderation on.
And with Group categories you can have many forums. So my all forums groups was about 3.5 - 4.2 to page build. all cats and forums had mod off, but one cat and one forum.
I turn mod off in Admin section Now it 1.5- 1.7 page build time?
Just a FYI
Edited by - kal corp on 24 March 2002 10:04:21 |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 24 March 2002 : 10:49:28
|
Is there some reason that lines 109 and 110 are both:
Session(strCookieURL & "last_here_date") = ReadLastHereDate(strDBNTUserName)
Session(strCookieURL & "last_here_date") = ReadLastHereDate(strDBNTUserName)
 |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 24 March 2002 : 10:59:53
|
quote:
After all of the above changes I've noted have been made, I've gone from 1234 DB Calls to get the Last Months Active topics, taking 20 seconds to execute to 10 Calls taking 0.5 seconds Now thats what I call a nice little performance increase 
A standard single keyword search over my forums took 30 seconds and 5725 DB calls before the changes, the same search is now taking 10 seconds and 2103 DB calls.
Can I just start out with a big "YOU ROCK"! So which files did you have to modify and how do I get them?  I'm a little curioius why search needs so many database calls. As I said before I'm happy to write SQL to return as much as possible. I just need to know what information is needed back from the query. Although I'm amazed that you guys have done so much so fast :)
Any chance that some of these improvements will fix the 100+ DB calls that default.asp makes (http://www.sqlteam.com/snitz/Articles/defaultpageperformance.htm)?
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 24 March 2002 : 11:05:00
|
You can apply the same inStr function (or an ubound array which I prefer) to subscriptions as you did for moderation.
If subs are turned on, you would put all the subs for that Member ID into an array (or pipe variable) (this could be called GetSubscription).
Then you could check against this instead of hitting the Db everytime (this could be called CheckSubscription).
This would probably be preferred to adding it to the SQL string since it would cut down on the joins.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 24 March 2002 : 11:08:48
|
Ok, most of these functions have been changed for version 3.4, plus inc_functions has now been split into 6 different files, and many redundant function calls have been removed.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 24 March 2002 : 11:34:12
|
quote:
mySql supports those joins. If it didn't the forum would not function.
Are you sure, the forum only uses about 4 joins, thes are all standard left joins, it does not use any inner joins that I know of.
All the calls to getmemberNo getmemberId etc have been consoildate to one call in inc_header, nothing else need use these functions.
I am not saying it is fixed in 3.4, but most of your assumptions are no longer correct as half of the functions have gone.
|
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 24 March 2002 : 12:07:31
|
Isn't using WHERE This_ID = ThisOther_ID just doing an inner join? I really need to get my SQL lingo going 
It doesn't matter now anyway, because Gremlin's method would take care of it better.
Nathan Bales - Romans 15:13
----------------------------------
Snitz Exchange | Do's and Dont's |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 March 2002 : 12:15:35
|
quote:
Isn't using WHERE This_ID = ThisOther_ID just doing an inner join? I really need to get my SQL lingo going
It doesn't matter now anyway, because Gremlin's method would take care of it better.
Nathan Bales - Romans 15:13
----------------------------------
Snitz Exchange | Do's and Dont's
It is, although it doesn't use the JOIN keyword. But you wrote about OUTER joins. Last time I had looked they were not supported by MySQL.
I've been to MySQL site today and the current version supports outer joins. Don't know when they started supporting those, so an upgrade to the installed version of MySQL may be needed if outer joins are used with MySQL.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 24 March 2002 : 12:25:58
|
Working on the current active.asp, I have managed to remove about half of the queries, plus some which were always being executed now have conditions around them which reduces them further depending on your settings.
I have mad a couple of minor additions to the inc_headers, so the MemberID is now set by the first call which sets MLev, the means thet getNewmemberNuber is no longer called(one less call for every page)
|
|
|
|
nomad_2k
Junior Member
United Kingdom
173 Posts |
Posted - 24 March 2002 : 13:44:23
|
I tried out the sql statment nathan posted and it works in acces but no joy with mysql. According to the documentation mysql supports inner joins from v3.27.17, I'm running v3.27.47 and I can't get them to work so I don't think they are supported. I'm no expert with mysql, so I might be doing something wrong.
Edited by - nomad_2k on 24 March 2002 13:48:09 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 24 March 2002 : 17:46:03
|
*Davio runs to the defense of MySQL once again*
Acording to MySQL documentations, MySQL does support inner joins and outer joins.
http://www.mysql.com/doc/J/O/JOIN.html
Might be done differently than how other databases use it.
«------------------------------------------------------»
Read the Do's and Don'ts before you post for help |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 24 March 2002 : 17:48:01
|
quote:
I tried out the sql statment nathan posted and it works in acces but no joy with mysql.
Well, like I said before, if we use Gremlins array logic then we wouldn't need to use that join from heck.
Nathan Bales - Romans 15:13
----------------------------------
Snitz Exchange | Do's and Dont's |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 24 March 2002 : 17:51:42
|
I will run tests doing it various ways and see what I come up with, but have already removed a lot of the queries anyway.
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 March 2002 : 18:48:47
|
quote:
*Davio runs to the defense of MySQL once again*
Acording to MySQL documentations, MySQL does support inner joins and outer joins.
http://www.mysql.com/doc/J/O/JOIN.html
I had said that before... Read my post (3 messages before). No need for you to do that. I've backed off already ...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Topic |
|
Topic Locked
