gHell
asked on
MS SQl 2005 full-text index (FTI)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
One last thing. i have other database on the same server, how will they get affected?
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you both for your advise.
ASKER
thank you for you help.
I realize you are new here, but please read up on the EE Guidelines regarding grading standards (https://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.
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.
ASKER
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.
That is very fair. Thank you.
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.