| Author |
 Topic Search  Topic Options
|
WebWiz-Bruce 
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
 Post Options
 Thanks(0)
 Quote  Reply
 Posted: 06 March 2006 at 9:55am |
|
I have been working on this to try and find a solution.
I did speed things up slightly by using 3 more efficient quires, but I don't like the extra database hits.
I'll do some testing with the 3 various solutions, and yours this morning and see what offers the best performance.
Many thanks, I'll let you know how the testing goes.
|
 |
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 1:31pm |
Hi Borg
Just glad I could help in some way. Let me know what the outcome is of your testing, as i am very interested. If you have some other SQL statements that you would like to to look at to help sort out, then just let me know and I will gladly give a helping hand.
Thanks
DM
|
|
Ritchie
Groupie
Joined: 14 June 2005
Location: Russian Federation
Status: Offline
Points: 91
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:27pm |
Это круто! Borg лучший! Excellent work!  Speed of generation of head page of your forum has grown in some times ! At me a small question on productivity forum_topics.asp. I have noticed, that very strongly speed of generation decreases in themes which have a plenty of pages.
At quantity such topics 10-15 on one page speed of generation of page can be 0,9 - 2 seconds!  (I tested on 2 x P4 3.0 GGz 2Gb RAM) whether variants of optimization of a code Are possible?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:29pm |
I found a slight problem with your SQL, that if no-one had a Birthday on that day it wouldn't return the number of members as well, and as I also need to get the last member registered I had to alter the SQL a bit and came up with:-
strSQL = "SELECT " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.DOB, " & _
"(SELECT COUNT (*) FROM " & strDbTable & "Author WHERE 1=1) AS memberCount " & _
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"WHERE tblAuthor.Author_ID = " & _
"(SELECT " & strDBTop1 & " " & strDbTable & "Author.Author_ID " & _
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC " & strDBLimit1 & ") " & _
"OR (MONTH(" & strDbTable & "Author.DOB) = " & Month(dtmNow) & " AND DAY(" & strDbTable & "Author.DOB) = " & Day(dtmNow) & ") " & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC;" |
However, due to the sub queries that was required I found it slowed the query down some what. The fastest SQL I have at the moment is 3 SQL queries:-
Query for Birthdays:-
strSQL = "SELECT " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.DOB " & _
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"WHERE (MONTH(" & strDbTable & "Author.DOB) = " & Month(dtmNow) & " "
"AND DAY(" & strDbTable & "Author.DOB) = " & Day(dtmNow) & " " & _
"AND tblAuthor.DOB IS NOT NULL " & _
"AND tblAuthor.DOB <> '') " & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC;"
Query for last member:-
strSQL = "SELECT " & strDBTop1 & " " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID " & _
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC " & strDBLimit1 & ";"
Query for member count:-
strSQL = "SELECT Count(tblAuthor.Author_ID) AS memberCount " & _
"FROM tblAuthor" & strDBNoLock & " " & _
"ORDER BY Count(tblAuthor.Author_ID);" |
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:37pm |
Hi Borg
I see what you mean, to be honest I think the best you are going to get is 2 queries, I would merge the last 2 queries (Query for last member and Query for last member) together so that they are one, and then at least you will only have 2 queries instead of 3, and as you are only looking for the TOP1 in the second query it should be slightly faster than 2.
Thanks
DM
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:38pm |
 Ritchie wrote:
Это круто! Borg лучший! Excellent work! Speed of generation of head page of your forum has grown in some times! At me a small question on productivity forum_topics.asp. I have noticed, that very strongly speed of generation decreases in themes which have a plenty of pages.
At quantity such topics 10-15 on one page speed of generation of page can be 0,9 - 2 seconds! (I tested on 2 x P4 3.0 GGz 2Gb RAM) whether variants of optimization of a code Are possible? |
I have gone through the page and even read an entire book on SQL performance tuning to try and get some tips to get the SQL to perform at higher speed, but the problem is the amount of joins needed in the SQL on that page in order to get all the required data. I even tried different join techniques, but found the old pre SQL 99 join technique the fastest in tests.
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:42pm |
DeadMeat wrote:
Hi Borg
I see what you mean, to be honest I think the best you are going to get is 2 queries, I would merge the last 2 queries (Query for last member and Query for last member) together so that they are one, and then at least you will only have 2 queries instead of 3, and as you are only looking for the TOP1 in the second query it should be slightly faster than 2.
Thanks
DM |
I've done as you suggested and the speed remains the same, but at least there is one less database hit. Also you can disable the displaying of Birthdays, so that should increase performance for those who don't want that feature. Thanks for your suggestions it's certainly helped. Now just need to speed up the performance on the forum_topics.asp page
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:45pm |
Hi Borg
Well if you need help there, let me know where you think the problem is, and I will have a look for you, is it in the SQL or the page itself?
I will have a dig around and see what I can find, but some clues would be really great.
Thanks
DM
|
|
