Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/Code)
 SQL Server full-text searching
 New Topic  Topic Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 6

-gary
Development Team Member

406 Posts

Posted - 11 August 2004 :  15:43:41  Show Profile
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  Show Profile  Visit Jeepaholic's Homepage
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
Go to Top of Page

-gary
Development Team Member

406 Posts

Posted - 14 August 2004 :  14:14:48  Show Profile
The original was Ruirib's, I just added the full text contains to it.

KawiForums.com


Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 14 August 2004 :  17:21:45  Show Profile  Send ruirib a Yahoo! Message
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
Go to Top of Page

Etymon
Advanced Member

United States
2383 Posts

Posted - 14 August 2004 :  23:19:20  Show Profile  Visit Etymon's Homepage
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
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 14 August 2004 :  23:47:19  Show Profile  Send ruirib a Yahoo! Message
No, the mod is specific to SQL Server.


Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20577 Posts

Posted - 15 August 2004 :  05:39:49  Show Profile  Visit HuwR's Homepage
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.
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 15 August 2004 :  08:47:41  Show Profile  Send ruirib a Yahoo! Message
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
Go to Top of Page

StephenD
Senior Member

Australia
1044 Posts

Posted - 15 August 2004 :  09:32:48  Show Profile  Send StephenD a Yahoo! Message
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 ..?
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 15 August 2004 :  09:35:00  Show Profile  Send ruirib a Yahoo! Message
quote:
Originally posted by StephenD

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 ..?


Yes.


Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20577 Posts

Posted - 15 August 2004 :  10:06:36  Show Profile  Visit HuwR's Homepage
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.
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 15 August 2004 :  10:20:17  Show Profile  Send ruirib a Yahoo! Message
Ah, ok then.


Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Etymon
Advanced Member

United States
2383 Posts

Posted - 15 August 2004 :  13:34:22  Show Profile  Visit Etymon's Homepage

Thank you for the help!

Etymon
Go to Top of Page

Jeepaholic
Average Member

USA
697 Posts

Posted - 18 August 2004 :  03:54:08  Show Profile  Visit Jeepaholic's Homepage
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
Go to Top of Page

Jeepaholic
Average Member

USA
697 Posts

Posted - 18 August 2004 :  03:56:37  Show Profile  Visit Jeepaholic's Homepage
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
Go to Top of Page

-gary
Development Team Member

406 Posts

Posted - 18 August 2004 :  12:19:45  Show Profile
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


Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.18 seconds. Powered By: Snitz Forums 2000 Version 3.4.07