| Author |
 Topic  |
|
-gary
Development Team Member
406 Posts |
 Posted - 11 August 2004 : 15:43:41
|
This is a follow-up to the mod New, faster search.asp for SQL Server DBs
Added support for MSSQL full-text indexs along with the original MSSQL upgrade. Also tore out includes for future MySQL full-text support and the ability to keep the original code in place.
Quick testing on an 180,000 post database for a popular keyword in the entire message timed out at 5 minutes on the original code, took 12 seconds on the original mod code and .6 seconds on my updated code to return 8,300 topics.
The only important configs are the new variables at line 49.
- intUsePaging: Determines wether to use the built in paging routines or pull them out like the mod above.
- intUseMSSQLFT: Use the SQL Server full-text indexs or not.
- intUseMySQLFT: Use the MySQL full-text indexs or not.
- intGlobalDebug: Displays the SQL strings for debugging.
- strNoiseWords: List of your SQL Server noise words to ignore during search.
Download Version I
Demo with MS SQL full-text enabled
For new un-modded installs replace the search.asp and add the 5 new files in the package. Otherwise, you're on your own.
Errors and suggestions are welcome.
A how to enable full text searching guide at DatabaseJournal.com. |
KawiForums.com
 |
Edited by - -gary on 18 January 2005 18:18:41 |
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 14 August 2004 : 06:20:30
|
I am one HAPPY dude. I never saw your previous MOD, but as you may have found from my posts in the SQL forum here...I have been struggling with search speed for a LOOOONG TIME.
THANK-YOU GARY! 
Here are some before and after statistics. Note that my "Old Search" is an upgraded search page built by Ruirib. The stock Snitz search would be considerably slower than what's shown below.
These are on a 2.6GHz Xeon processor w/2GB RAM (SQL is allowed 1.5GB of that)
Keyword(s) / Topics, Replies Searched / Constraints
Old Search Time / New Search Time / Topics Returned / Pages of Topics
=================================================================
jeep / 18000, 208000 / All Words, Entire Message
35.56s / 3.55s / 10,862 / 435
jeep / 12000, 123000 / All Words, Entire Message
19.33s / 1.50s / 4,398 / 176
jeep off road / 18000, 208000 / All Words, Entire Message
37.23s / 0.77s / 888 / 36
jeep off road / 18000, 208000 / Any Words, Entire Message
54.30s / 3.53s / 13,043 / 522
jeep off road / 12000, 123000 / All Words, Entire Message
18.19s / 2.14s / 329 / 14
jeep off road / 12000, 123000 / Any Words, Entire Message
32.88s / 2.56s / 6,682 / 268
============================================================
Time for bed! Thanks again, Gary. You rock.
|
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
 |
|
|
-gary
Development Team Member
406 Posts |
Posted - 14 August 2004 : 14:14:48
|
| The original was Ruirib's, I just added the full text contains to it. |
KawiForums.com
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 August 2004 : 17:21:45
|
It doesn't surprise me, the improvement. Obviously full text search has an enormous impact on search times. Anyone that can take can use full text search should do it.
Nice work Gary. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 14 August 2004 : 23:19:20
|
Hi Gary,
Will this MOD work also on an Access 2000 database?
Thank you for sharing!
Etymon
|
Edited by - Etymon on 14 August 2004 23:21:11 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 15 August 2004 : 05:39:49
|
well, actually the mod works with all databases from what I can tell, but if you are using Access it just uses the existing search code.
I am going to install the code here later today so everyone can see the improvement. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 15 August 2004 : 08:47:41
|
quote:
Originally posted by HuwR
well, actually the mod works with all databases from what I can tell, but if you are using Access it just uses the existing search code.
I am going to install the code here later today so everyone can see the improvement.
Being based in my own mod, I don't think Access will support the query syntax used. Actually I can't remember if I ever tried it with Access, but I'm almost sure the SQL syntax would need changes. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 15 August 2004 : 09:32:48
|
quote:
Originally posted by -gary
A how to enable full text searching guide at DatabaseJournal.com.
Is this something your ISP could do for you ..? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 15 August 2004 : 10:06:36
|
quote:
Originally posted by ruirib
quote:
Originally posted by HuwR
well, actually the mod works with all databases from what I can tell, but if you are using Access it just uses the existing search code.
I am going to install the code here later today so everyone can see the improvement.
Being based in my own mod, I don't think Access will support the query syntax used. Actually I can't remember if I ever tried it with Access, but I'm almost sure the SQL syntax would need changes.
No I know, but this version supports all dbtypes, it has different includes for each one, admittedly it won't make a lot of difference if your using access, but it could be just dropped into a standard Snitz without any problems. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 15 August 2004 : 13:34:22
|
Thank you for the help!
Etymon |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 18 August 2004 : 03:54:08
|
Just got this error:
=============
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. A clause of the query contained only ignored words.
/support/search_mssqlft.asp, line 193
=============
Any thoughts on how to handle this more gracefully? Happens when all of the words entered are in the ignore list. |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
Edited by - Jeepaholic on 18 August 2004 03:56:59 |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 18 August 2004 : 03:56:37
|
Hmm, also just got this one by putting the word "how" in the search box:
============
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error occurred near '''. Expected '_NOT, '(', _ISABOUT, _FORMSOF, _STRING, _PREFIX_STRING, '!'' in search condition ' '.
/support/search_mssqlft.asp, line 193
============ |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
Edited by - Jeepaholic on 18 August 2004 03:57:14 |
|
|
|
-gary
Development Team Member
406 Posts |
Posted - 18 August 2004 : 12:19:45
|
The easiest way to get around the ignored words error (all errors actually) is to enable the on error resume next line. I always have it enabled, so I never see that.
I'll look at writting some error checking for it and see what's happening with the second error today. |
KawiForums.com
|
|
|
|
Topic |
|
Topic Locked
