Solved

MS SQl 2005 full-text index (FTI)

Posted on 2010-08-12
9
355 Views
Last Modified: 2012-05-10
Hi,

Ive searched the net high and low but there doesnt seem to be much discussion on the pros and cons of FTI.
Q1. can shed some light on this matter.

Ive been asked to create a search facility that needs to include Internet like search (i.e. "this and that", this and that -Those, this or that +Those, contains etc) and i cant do such search requirements using normal sql. The search is going to be against 15+ tables and numerous columns with no more than 300,000 records.

Q2. I know by adding FTI there is a possibity of performance issues but only if you run into million+ records (i wont have that problem), Is this true? Also, how does does it affect database back up or performance when a search is executed.

Q3. If the advise is not to use FTI, any recommendations? An idea i had was to create a temp table which will hold all the data that would be searchable from all differnet tables/columns and search against those using normal type searching (i.e. and, or and like). Thoughts?

Sorry for having so many questions, i hope you guys can help me out.

regards

David.
0
Comment
Question by:gHell
  • 4
  • 4
9 Comments
 
LVL 2

Accepted Solution

by:
GLoad earned 150 total points
ID: 33426034
Full text indexing is the only way to get FREETEXT type searches and other imperfect string matches. ie, ,you can get a number to rank the relevance for search terms for "my house" as being a match for "my mate's house"

LIKE will not work for that scenario.

Full text indexes do have some overhead, but they are handled out of process from SQL server (it actually uses the microsoft text indexing engine). The main performance hit comes from the building and updating ot those indexes and tables that have millions of rows can take days to build the index the first time. Having said that, they are much faster than the like approaches that try to offer the same functionality.

Having said that I've found them to be maintained very well and provided you have a decent normalized schema with all your indexes in the right place, it is a great tool for user friendly searches.

Key points:

- Create your indexes before you roll out the code that uses them.
- Play with quering the index using the different options available in full text indexes to make sure your relevance is good.
- In SQL server 2005, full text indexes are backed up with the database. 2000 and 2008 have slightly different handling of them.. see http://www.sqlmag.com/article/sql-server/full-text-search-in-backup-recovery.aspx for a bit more info.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33426047
>>can shed some light on this matter.<<
Full-Text Search is not for everyone.  It does require some additional setup and to some extent some maintenance.  One caveat is that many think it is replacement for using LIKE except on steroids.  While it does to regular searches as does LIKE it cannot search for words ending in something.  For example, using LIKE you can do this to find all the instances that start with Experts such as ExpertsExchange:
WHERE YourColumn LIKE 'Experts%'

and with Full-Text Search that is as simple as:
WHERE CONTAINS(YourColumn, '"Experts*"')

What you cannot do with Full-Text Search is the equivalent of:
WHERE YourColumn LIKE '%Exchange'

But Full-Text Search does far more than that and the best advice I can give is to read up on it as much as possible and try it out.

>>I know by adding FTI there is a possibity of performance issues but only if you run into million+ records (i wont have that problem), Is this true?<<
It is quite the opposite.  The reason you add Full-Text search is to improve performance, especially if you have large datasets.

>> If the advise is not to use FTI, any recommendations? <<
This really depends, if you find Full-Text Search to be too constraining this may be your only choice.  But I would only roll your own as a last resort.
0
 

Author Comment

by:gHell
ID: 33437007
Sorry, i didn't explain myself properly, i meant pros/cons server side rather than its usability. I.E disk space, maintenance, most importantly, can the search still operate while indexes are being updated.

One last thing. i have other database on the same server, how will they get affected?

thanks
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 33437072
Disk space should be minimum.  It only saves unique keywords and many words are excluded (noise words).  Without knowing exactly what you are saving it is difficult to be more precise.  That can only come from experience with your data.  The good news is that it does not take long to figure that out in a test environment.

Full-Text Search indexes can be updated in the background and on a continual basis, so there is no problem there.  One caveat you should be aware of, and that is if you insert 10K rows at one time, it may take a few minutes depending on many factors before the index is totally updated.  So while you can still continue to search for keywords in previous rows, the keywords for the new rows may not be immediately available.

Other databases will not be affected.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:gHell
ID: 33437084
thank you both for your advise.
0
 

Author Closing Comment

by:gHell
ID: 33437110
thank you for you help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33437159
I realize you are new here, but please read up on the EE Guidelines regarding grading standards (http://www.experts-exchange.com/help.jsp#hs=29&hi=403):

What's the right grade to give?
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points.
0
 

Author Comment

by:gHell
ID: 33439086
thank you for letting me know, following the guidelines this should have been and A. I dont think i can change it now but ill be sure to assign grades appropriately in future.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33439122
That is very fair. Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now