| Author |
 Topic Search  Topic Options
|
Ritchie 
Groupie
Joined: 14 June 2005
Location: Russian Federation
Status: Offline
Points: 91
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: 8beta2 Appreciable decline of productivity
Posted: 02 March 2006 at 9:01pm |
SQL the inquiry in lines 193-195 of a file default.asp returns huge quantity of data which are not used.
strSQL = "SELECT " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.DOB " & _
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC;"
In this connection I have noticed almost triple decline of productivity at quantity of the registered users less than 5000. And in process of growth of their quantity I believe productivity will decrease very strongly. And in the event that the forum is visited by many people simultaneously there is a danger of a unjustified expenditure of resources of a server - these unnecessary data it is necessary to store somewhere. Whether probably this inquiry to break into two simple inquiries so that this delay was not such essential?
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 10:15am |
|
The reason behind using just the one query is to cut down on database calls, recordset objects, etc.
I know the query is not the most efficient, but to get the required data it needs to be like that.
The only other way is to use 3 queries which is 3 database hits to get the required data, below are some tests I did.
I did tests using SQL Server and 9,000 database members.
In the results I found that the 3 database hits took around 0.2 seconds to run and process all queries.
I've just run the same tests again now on the present version on same database and the result was that it took 0.00000000 seconds to run the query on the database and took 0.04 seconds to run the query and process it before closing the recordset.
The data returned by the query is, Todays Birthdays, number of registered users, and the newest member.
I would rather not have the query at all, but people want statistics on the main forum index page.
Edited by -boRg- - 03 March 2006 at 10:16am
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 10:19am |
|
If you can find away to run just one query more productively to get the required data, please let me know.
|
|
Ritchie
Groupie
Joined: 14 June 2005
Location: Russian Federation
Status: Offline
Points: 91
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 11:04am |
|
In one inquiry it is impossible to receive all data set which would not be superfluous and ideally approached for gathering statistics. The unique decision - use of two inquiries. 9000 users are not so much. And here if them will 100000-200000 at a simultaneous finding of a plenty of users on a site there can be problems. Can was optimum add one more inquiry?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 11:30am |
|
I shall look into a way to make the query run faster and return less data.
I'm sure there is away to do it.
|
|
Ritchie
Groupie
Joined: 14 June 2005
Location: Russian Federation
Status: Offline
Points: 91
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 2:38pm |
It would be abruptly! My Variant works only for SQL a server. It is possible to use stored procedure. I did not test it with use of a code of a forum, but I think, that it is less exacting to resources of a server.
Declare @RegisteredUsers int
SELECT @RegisteredUsers = COUNT(*)
FROM dbo.tblAuthor
SELECT Username, Author_ID, DOB, YEAR({ fn NOW() }) - YEAR(DOB) AS Yea, @RegisteredUsers AS RegisteredUsers
FROM dbo.tblAuthor
WHERE (MONTH(DOB) = MONTH(GETDATE())) AND (DAY(DOB) = DAY(GETDATE()))
ORDER BY Yea DESC
Edited by Ritchie - 03 March 2006 at 2:41pm
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2006 at 2:43pm |
|
Thanks, only problem is that the date/time off-set needs to be applied to the date/time value
Also, stored procedures have been dropped in version 8 because they are more trouble than they are worth, from a support pint of view, when doing this type of software. They also don't give a performance boost.
Edited by -boRg- - 03 March 2006 at 2:44pm
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 7:03am |
Hi Borg
You could give this a try, I have not tested it, but I don't see why it should not work, and it should help with the amount of data you are returning. You might have to do some simple tweaking on this, and it should work, if not let me know and I will sort it out for you.
dtmNow = getNowDate()
strSQL = "SELECT " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.DOB " & _
", (SELECT COUNT (*) FROM " & strDbTable & "Author WHERE 1=1) as num_registered_users "
"FROM " & strDbTable & "Author" & strDBNoLock & " " & _
"WHERE MONTH(" & strDbTable & "Author.DOB) = " & Month(dtmNow) & " AND DAY(" & strDbTable & "Author.DOB) = " & Day(dtmNow) & _
"ORDER BY " & strDbTable & "Author.Author_ID DESC;"
Thanks
DM
|
|
