| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 09 March 2003 : 11:19:26
|
For a while I've been helping people trying to work around the 40 secs timeout problem experimented on search.asp, for forums with many posts. Recently, while trying to have a consistent look on the existing indexes for the SQL Server DB I realized a different approach could be taken, in the attempt to diminish the time the search query takes to complete, when performing wide scoped searches on forums with a lot of posts.
Basically the code has been changed to implement the search over the topics and replies tables as a UNION of two different queries, instead of a single query with a LEFT JOIN. In a test forum with many posts, in my own computer (not a very fast one, and only with 384 MB of RAM for that matter), this strategy has shaved off something like 30 to 60 secs of a query that takes between 90 secs to 120 secs to complete, and that I use as a benchmark.
So, for anyone who might be interested, I'm posting the link to the search page. It is ONLY for SQL SERVER databases, since MySQL does not support UNION Queries and Access does not support UNIONS in subqueries.
The search page also has the paging feature disabled. This has been the way to avoid the annoying 40 sec timeout.
So, here is the link to the page. Just replace your current search.asp by the one included. I've tested it as much as I could, so I don't expect problems with it, but one never knows... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 09 March 2003 16:44:38 |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 09 March 2003 : 13:05:00
|
| Is that the same one you emailed to me in searchNoPaging.zip ? |
The UK MkIVs Forum |
 |
|
|
Jeepaholic
Average Member
USA
697 Posts |
|
|
PeeWee.Inc
Senior Member
United Kingdom
1893 Posts |
Posted - 09 March 2003 : 16:34:05
|
now if only we could think of a way to make it faster for access  |
De Priofundus Calmo Ad Te Damine |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 March 2003 : 16:41:22
|
quote:
Originally posted by DavidRhodes
Is that the same one you emailed to me in searchNoPaging.zip ?
No David, this is a new one. The SQL statement was significantly changed in this one. The previous one only had the paging code removed. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 09 March 2003 16:42:00 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 March 2003 : 16:43:24
|
quote:
Originally posted by Jeepaholic
Fantastic job, Rui. This has shaved nearly a minute off of a two-minute query on my forums. Whoohoo!
Al
Glad to know that. I had your forum in mind when I did this. It has bugged me for quite a while  . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 09 March 2003 : 16:46:24
|
| cheers, i'll try the new one asap, good work! |
The UK MkIVs Forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 March 2003 : 16:58:29
|
quote:
Originally posted by PeeWee.Inc
now if only we could think of a way to make it faster for access
It's a bit difficult, since Access does not support the required SQL. With stored queries it could be doable, but it's a completely different approach than the one used in the forum.
I may try to implement it for Access one of these days. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Webguy
New Member
Australia
65 Posts |
Posted - 09 March 2003 : 17:23:28
|
| What about MYSQL, it's free text searching does suck somewhat! ;-( it's so bad I have disabled searching the archives till I either go to MS SQL or somebody suggests a better way for MY SQL. |
"Special bulletin: There is still no news from New Zealand. If they ever have any, we'll be sure to let you know." |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 March 2003 : 17:42:41
|
| Last time I looked UNION support was programmed for version 4.0 (which I don't know if it's in release status) and I think subquery support was not yet available. Anyway I don't know MySQL very well, so I'm not the guy to port this to MySQL (if it is possible to do it at all). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Diogo
Starting Member
9 Posts |
Posted - 10 March 2003 : 07:46:45
|
There is an erro in line 252:
strSqlTopics = " SELECT T.TOPIC_ID FROM FORUM_TOPICS T "
Should be:
strSqlTopics = " SELECT T.TOPIC_ID FROM " & strTablePrefix & "TOPICS T "
Nice work Rui / Optimo trabalho!!
Diogo
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 March 2003 : 09:41:25
|
I've just found another problem, regarding searching the archives. Will post a fix in a few minutes.
Update: Corrected. Please download the new file from the previous link. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 10 March 2003 09:46:31 |
|
|
|
Diogo
Starting Member
9 Posts |
Posted - 11 March 2003 : 06:55:39
|
Can you post where you change the code for that new bug?
Obrigado! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 March 2003 : 12:09:43
|
| Basically I changed strTablePrefix by strActivePrefix in a couple places. I can't remember exactly where. If you download the current file and search for strActivePrefix and do the same in the one you have you'll find it. I don't have the files where I am now, but I may try to tell ya when I get back home, if you need it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
PeeWee.Inc
Senior Member
United Kingdom
1893 Posts |
|
|
Topic |
|
Topic Locked
