| Author |
 Topic  |
|
|
Jeepaholic
Average Member
USA
697 Posts |
 Posted - 30 October 2002 : 18:20:40
|
Hi folks, the content of this topic is spawned from another that I created some time back that's gotten a decent amount of attention. I will try to summarize the original topic before providing a link to it for reference. The reason I am posting it here is that I feel this is a relatively major issue that will affect many different forums of significant size. These very forums here at Snitz are also affected by this.
Essentially, SQL has an issue of some kind regarding the Paging queries that Snitz uses in it's search functionality. Mind you, this is not a Snitz issue...it's a Paging issue with SQL. Rui has validated this same problem with other applications that use paging. The issue is that it will timeout after merely ~40 seconds regardless of any ASP, SQL or other timeout settings.
A secondary issue is that SQL 2k takes about 3 times as long to complete the same query as Access 2k. I consider this a secondary issue since the ~40 second timeout nullifies everything anyway.
For real-world example purposes, I'll provide my own server's statistics and metrics:
SERVER: Win 2k Server, Dual PII 400's, 512MB RAM. Rui tested on his server and found the same ~40 second timeout, even though his hardware seems to be faster at returning searches.
SNITZ: Version 3.4.03, SQL 2k, 120000+ posts, 12000+ topics
QUERIES: (all are based on a default "Hello" query over the entire forum)
1) Access 2k: Results in ~1:20
2) SQL 2k using Query Analyzer: Results in ~3:50
In order for me to get the query to complete in < 40 seconds, I had to archive archive all but 1/10th of the topics, leaving ~1200 of the original ~12000 topics. Of course, searching on the archives does not work because of the size.
There were a number of suggestions in the original thread that were discussed, some of which were:
1) Optimizing the Snitz database to speed up query times in searches
2) Enabling Microsoft's Full-Text Searching
3) Use a non-paging query to populate a temporary table with search results, then use a paging-query to query the temporary table
4) Remove paging of the query.
My thoughts on each one are the following:
1) Rui spent a lot of time doing this, and while it improved things - it wasn't enough to beat the 40 second timeout at all.
2) Although I believe this would provide rediculously fast searches...From what I can tell, this would be a fairly serious modification...certainly not something for mass consumption.
3) This would add a little bit of time to the query, but theoretically I believe it would at least finish and circumvent the ~40 second timeout quirk.
4) I assume this would display the entire search recordset, which might pose it's own set of bandwidth problems for many.
Personally, I think a combination of #1 and #3 would be the best solution...but maybe there are better solutions unmentioned thus far? Again, I feel this is a fairly serious issue to forum admins who have large databases, which is why I'm revisiting it in this forum. For my forums, having a full history of all the information is a very important and valuable asset.
I guess I'm hoping to spark a little brainstorming on a way to help alleviate this problem. I would have attempted #3 myself, but after looking at the code - it's beyond my experience to make this happen.
The link to the original topic is here:
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=34674
Thoughts? Thanks for your time. |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
Edited by - Jeepaholic on 30 October 2002 18:31:45 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 October 2002 : 18:26:54
|
| I'll try to find out some time to participate in this discussion. Now I just wanted to state this is not a Snitz problem. I've hit the 40 sec barrier in other ASP scripts using SQL Server DBs. This seems to be asscoiated with the type of recordset and locking used and I've managed to have recordsets created without the problem. However, those recordsets did not support paging... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 30 October 2002 : 20:02:09
|
quote:
2) Although I believe this would provide rediculously fast searches...From what I can tell, this would be a fairly serious modification...certainly not something for mass consumption.
This is not strictly true, and depends entirely on the hardware underneath, so it is not always faster.
If you really want a fast search which won't time out, then you should look at writing a com object or isapi dll to do the queries instead of your web pages |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 30 October 2002 : 22:33:33
|
Hey Huw, that's unfortunately beyond my capabilities right now. Besides, that really wouldn't help the Snitz community at large, which I think is pretty important. Even if the query takes a couple minutes, I'd be happy as long as it finished.
Of course, if someone has or wants to code a DLL or COM object...I'm certainly not going to complain. I'd just rather we come up with a solution that works across the board...if it's possible. |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 31 October 2002 : 03:43:09
|
quote:
I'd just rather we come up with a solution that works across the board...if it's possible.
In that case (2) is not an option either
1) That has been done here, and as you can see it makes no difference.
4) you would then move the timeout from SQL to ASP, that is how the search used to work |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 03 November 2002 : 16:13:28
|
Yes, I see, and yes.
Any thoughts on #3? That would eliminate the issue of SQL timeout, and retain the new paging setup. Theoretically, it seems like it would work...but I'm not good enough with this code to have noticed any caveats. |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
|
|
| |
Topic |
|
|
|
Topic Locked
