Web Wiz Forums - 8beta2 Appreciable decline of productivity

Print Page | Close Window

8beta2 Appreciable decline of productivity

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=18571
Printed Date: 13 April 2026 at 6:52am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: 8beta2 Appreciable decline of productivity

Posted By: Ritchie
Subject: 8beta2 Appreciable decline of productivity
Date 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?

Replies:

Posted By: WebWiz-Bruce
Date 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.

Posted By: WebWiz-Bruce
Date 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.

Posted By: Ritchie
Date 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?

Posted By: WebWiz-Bruce
Date 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.

Posted By: Ritchie
Date 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

Posted By: WebWiz-Bruce
Date 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.

Posted By: DeadMeat
Date 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

Posted By: WebWiz-Bruce
Date 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.

Posted By: DeadMeat
Date 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

Posted By: Ritchie
Date 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.

http://217.147.29.102/sample/Snap7.gif">

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?

Posted By: WebWiz-Bruce
Date 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);"

Posted By: DeadMeat
Date 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

Posted By: WebWiz-Bruce
Date 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.

http://217.147.29.102/sample/Snap7.gif">

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.

Posted By: WebWiz-Bruce
Date Posted: 06 March 2006 at 2:42pm

DeadMeat wrote:

Hi Borg

Thanks

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

Posted By: DeadMeat
Date 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

Posted By: WebWiz-Bruce
Date Posted: 06 March 2006 at 2:50pm

I've spent days on it so far, so if you could have a look at the query around line 303 of forum_topic.asp I would be very grateful.

From tests I have done adding, removing, and re-ordering, the SQL in the query the only thing that would speed it up is to have less joins, but then it's a question of getting the required data without the joins.

Posted By: DeadMeat
Date Posted: 06 March 2006 at 2:55pm

Hi Borg

I'll have a look and see what I can find. Just a simple silly question, do you have a test DB that you run against? If so, how many records etc, so that I can try and get a similar result set. If you could throw this Test DB my way, I would greatly appreciate it, as then I can compare apples to apples. If not, then no worry, I will generate some data to test against.

Thanks

Posted By: WebWiz-Bruce
Date Posted: 06 March 2006 at 2:59pm

Not really I just use a backup copy of the database from this site to test on as it has quite a bit of data in now.

The only thing I find that the more pages you have the topics subjects across the slower it gets, being around 0.2 to 0.3 process time.

Posted By: DeadMeat
Date Posted: 06 March 2006 at 3:28pm

Hi Borg

Ok I will have a look for you, and see what I can get and come up with.

Thanks

Posted By: DeadMeat
Date Posted: 09 March 2006 at 6:30am

Hi Borg

Sorry mate, but i only have bad news. I have run through the script again and again, and I am unable to find any other way to make this SQL run faster. Sorry mate, but I tried everything that I could think of to cut down on the joins to get it to play ball better, but to no avail.

On a really silly note, does the SQL time taken to run change when the number of pages is increased? Is this increase in time taken related to the SQL, or to the ASP code. The reason I ask, is that i had a page that did a lot of work, and had a bundle of loop statements in it to handle displays across multiple pages, and it ran like a dog. When I changed the code to hit the database a bit more for things like record counts etc, and simplified my multipage processing the page speed increased. Just something silly I know but stranger things have happened to me before.

Let me know what you think, or if you need any help on anything else. Sorry I was not able to speed up the SQL for you on this one, but I will continue to look for ways to help.

Thanks

Posted By: WebWiz-Bruce
Date Posted: 09 March 2006 at 1:08pm

Many thanks for looking, I've probably spent around 8 hours on that query over the last 6 months, and another 4 hours reading a book on SQL performance tuning.

I was hoping by having another pair of eyes looking at it, you may have seen something I didn't.

I have done quite a few tests on the page and it is the SQL Query that uses the most processing time.

At least by de-normalising the database and using the new query it is much faster than in version 7 and uses allot less resources. I was just hoping to improve it further.

Because I am immediately using GetRows() to place the recordset in to an array the amount of time the rest of the code takes to process is so fast that the system is unable to return a processing time for it.

Posted By: DeadMeat
Date Posted: 09 March 2006 at 1:28pm

Hi Borg

Yeah I know wht you mean, you sometimes spend so much time working on something, that you can't see the wood for the trees. Sorry I was not able to find something, but not too worry, I will continue to look at it, and see during my travels whether I can find something that will make it perform better and improove performance.

Thanks

Posted By: WebWiz-Bruce
Date Posted: 09 March 2006 at 2:02pm

Thanks DeadMeat

Posted By: Ritchie
Date Posted: 09 March 2006 at 6:58pm

Greetings Borg! I have an offer of more optimum method of sample of data instead of what is used now. It is so-called " the Method paginal SQL samples " I it did not test on WWF 8 beta 2, but I can tell, that it is less exacting to resources and returns only that quantity of data which is necessary in each concrete sample.

The offered method does not apply for a rank of the best. It, maybe, and not better, but precisely is not worse than others.

1. Search of the decision was based on following wishes: the Reference to new page of sample should not lead re-query all sample.

2. The inquiry of page should return usual habitual recordset. Inizialisation should not be excessively long that the inquiry to the table in 1000000 lines has not tired with expectation.

3. It should be at any moment authorized to choose any page from sample, including earlier chosen.

4. The page should contain any set in lines.

5. The page should begin with any set line under number.

6. The decision satisfying to all these wishes to a srtass, has been found.

1. Here it: we Have inquiry which we wish to choose page by page

select * from BigTable

we it we do not start, and it we pass to a step 2.

2. We initialize thus: declare @handle int,

@rows int exec sp_cursoropen @handle OUT, ' select * from BigTable ', 1, 1, @rows OUT select @handle, @rows

thus we receive empty recordset, containing metadata-descriptions of columns who can be used for reception of the name of fields (and types). After NextRecordset also we receive handle the turned out cursor and in lines in all sample. handle it to be necessary for us for substitution in following calls, and it should be kept on the client in a global variable, and in lines can be used for definition pages.

3. We receive the necessary page from sample:

exec sp_cursorfetch @handle, 16, @rowid, @rowcount

Where in @handle it is substituted the kept value handle, in @rowid number of a line with which the page interesting us begins is substituted, and in @rowcount is substituted in lines on the given page.

The step 3 is repeated so much how many it is necessary time.

4. We release resources after pages already any more will not be necessary

exec sp_cursorclose @handle

In our case in Query Analizer it is carried out following SQL a code

-----------------------------------------------------------------------------

Declare @handle int,@rows int

EXEC sp_cursoropen @handle OUT, 'SELECT TOP 1000000 tblTopic.Topic_ID, tblTopic.Poll_ID, tblTopic.Moved_ID,
tblTopic.Subject, tblTopic.Icon, tblTopic.Start_Thread_ID, tblTopic.Last_Thread_ID,
tblTopic.No_of_replies, tblTopic.No_of_views, tblTopic.Locked, tblTopic.Priority,
tblTopic.Hide, tblThread.Message_date, tblThread.Message, tblThread.Author_ID,
tblAuthor.Username, LastThread.Message_date, LastThread.Author_ID, LastAuthor.Username,
tblTopic.Event_date
FROM tblTopic WITH (NOLOCK) , tblThread WITH (NOLOCK) , tblThread AS LastThread WITH (NOLOCK) , tblAuthor WITH (NOLOCK) , tblAuthor AS LastAuthor WITH (NOLOCK)
WHERE ( tblThread.Author_ID = tblAuthor.Author_ID AND LastThread.Author_ID = LastAuthor.Author_ID AND tblTopic.Start_Thread_ID = tblThread.Thread_ID AND tblTopic.Last_Thread_ID = LastThread.Thread_ID AND (tblTopic.Priority = 3 OR tblTopic.Moved_ID = 2 OR tblTopic.Forum_ID = 2) ) AND (tblTopic.Hide = 0 )
ORDER BY tblTopic.Priority DESC, LastThread.Message_date DESC;',1, 1, @rows OUT

SELECT @handle, @rows

Declare @rowid int,@rowcount int
SET @rowid = 2000
SET @rowcount = 20

exec sp_cursorfetch @handle,16,@rowid,@rowcount

------------------------------------------------------------------------------------------

The necessary set of records stands out practically instantly and well works with a lot of records in the table.

Posted By: WebWiz-Bruce
Date Posted: 10 March 2006 at 12:47pm

Thanks Ritchie for the suggestion, but do you have a version that doesn't use Stored Procedures as most of the supported database types don't support them and because of other issues with SP's version 8 won't be using them.

Posted By: DeadMeat
Date Posted: 11 March 2006 at 6:38am

Hi Borg

I had an idea on this, that would speed the search up, but I would not recomend it, unless you are left with no choice. It is what I would call a really serious hack of sorts, and I will only ever do it in a DB where time critical transactions are taking place. I have mainly only ever used it for financial transactions etc, where time is more important than the perfect DB. I am still not sure that 0.1 of a second is that important, but then others may disagree. So to this a way to simplify the query is to alter the TOPICS table, by adding in 4 extra fields, these being :

Start_Author_ID : int

Start_Thread_Date : datetime

Last_Author_ID : int

Last_Thread_Date : datetime

These fields would then be updated at the time that you update the fields Start_Thread_ID and Last_Thread_ID. This means that you do not have to include the THREAD table in the search.

I would not recomend this option, as it kinda removes the normalisation of the DB, but this option would definitely speed up the search, as the search is not a simple 2 layer Join as apposed to the 3 layer join that is currently in place.

Any way just another option to think about.

Thanks

Posted By: WebWiz-Bruce
Date Posted: 11 March 2006 at 3:06pm

I have already thought of this and have already denormalised the tblTopics table to an extent, but I didn't want to go to far with it as storing loads of extra data would increase the size of the database.

But it is certainly something worth considering for very large large forums, however, to get around speed issues I decided to employ a different tactic of limiting the search results to the first 1000 (can't see anyone reading through more results than that) this then means the search runs very fast as only a maximum of 1000 records require the join.

To be honest, I'm wondering if it is worth limiting this amount further.

Posted By: Ritchie
Date Posted: 12 March 2006 at 9:45am

I think, that the basic problem all the same remains in superfluous quantity of data which returns Recordset. In process of growth of the table tblTopics it will be necessary to limit them (SELECT TOP 1000, SELECT TOP 500, SELECT TOP 200 and so on). Recordset it is necessary to limit to any possible way. One of ways in MS SQL has been offered by me. Possibly it should to work well with huge quantity of messages in the table tblTopics it will not be natural to work it with MS Acsess and MySQL. MS Acsess it is impossible to use as a source of data for greater forums. Therefore I consider, that application of one storage procedure for optimization of speed in MS SQL versions is quite justified. WWF - best of the forums, written on ASP also has no competitors. The engine of a forum should be calculated on work with greater databases

-------------
Чем дальше в лес, тем толще партизаны

Posted By: DeadMeat
Date Posted: 13 March 2006 at 5:15am

Hi Borg

I know what you mean. Another approach would be to do a double hit on the database. he first to get the count of records that are returned (this would be a simplare join as you do not need the author tables), so that you know the number of pages, and the second is to do the actual select. Now you already know how many topics per page each user has, so I would limit the TOP portion of the select statement to 5 times that limit, this will make it really fast, then if he bounces onto the page 6 or 11 etc, then you would increase the select statement by a further 5 times the topics.

There is another approach that also might work, and that is to do the same as Yahoo, and MSN search with their results. They display only page 1-7 and when you hit page 5 then the number of pages increases etc.

From my stand point I prefer the first approach as it makes it a lot easier to handle, the only problem is that you end up with 2 hits against the DB, but this might e able to be converted to one hit. I just need to test some things under Mysql. I know how it works under MSSQL, and I will feed you back my findings.

Thanks

Posted By: DeadMeat
Date Posted: 13 March 2006 at 1:41pm

Hi Borg

I just thought of a simpler approach, that would not require the DB to be hit twice, and that is to do a TOP select of 5 times the number of topics per page plus 1. Then in the code check to see if the number of returned records is equal to this, if so then the number of pages at the top and bottom would end up being 1 2 3 4 5 more... When the user selects page 4 then you increase the Select amount to 10 times the number of topics per page, etc etc. This might cause a slow down in the release of the new V8, but this would sort out your speed issue on the topics page, as you would normally only be loading 5 times the topic count. I know that is adds to the code, but it would make the topics page display quicker.

Anyway just some ideas.

Thanks
DM

Posted By: WebWiz-Bruce
Date Posted: 13 March 2006 at 3:15pm

Some nice ideas, I will certainly have to look into them and see what can be done.