May 8, 2012 at 8:03 am
Hi ,
I am experiencing performance problems with queries which use wildcards at each end of a search criteria e.g. (WHERE Surname = %Jones%).
I need to allow for this type of querying, and am looking into using Full Text Indexing, as I've been informed that this can help to improve performance.
Is this a good option, and are there any disadvantages to Full Text Indexing please?
Thanks.
May 8, 2012 at 8:18 am
The problem isn't the wild card at the end of the criteria. It's the wild card at the start. That requires a scan of the column, which can lead to poor performance.
Either just use the final wild card, or, look to using the full text index and full text search criteria instead of straight T-SQL.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2012 at 9:15 am
Thanks.
Can you tell me how Full Text Indexes are stored please? I know how clustered indexes are stored. How are Full Text Indexes stored in comparison?
Thanks
May 8, 2012 at 9:27 am
ggjjbb1983 (5/8/2012)
Thanks.Can you tell me how Full Text Indexes are stored please? I know how clustered indexes are stored. How are Full Text Indexes stored in comparison?
Thanks
That will let you to start: http://msdn.microsoft.com/en-us/library/ms142571(v=sql.105).aspx
You will find that Full Text search is not the same as LIKE. It has very different use pattern and there are some limitations (search for words containing special characters eg. dashes would require some custom solution).
May 8, 2012 at 9:33 am
full text is stored outside the DB as full text catalogues - they have nothing to to with clustered or nonclustered indexes
see this article to get you started
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server
full text indexing in this instance proboably won't help you as you need to update the catalogues before the results appear in the search - ie the search isn't realtime.
it would be better to improve the quality of your SQL and make sure you can use indexes properly - ie do you really need to use LIKE '%JONES%' - can you use ='JONES'
MVDBA
May 8, 2012 at 9:40 am
Hi,
That article doesnt explain how the actual data is stored. I just need to know if using this type of index uses a large amount of disk space?
May 8, 2012 at 9:44 am
full text indexing in this instance proboably won't help you as you need to update the catalogues before the results appear in the search - ie the search isn't realtime.
If the catalog is automatically or manually updated when the data is added then it wont need to be updated when I query the data - Isn't this how it works? Surely it doesnt update the catalog every time a query it?
May 8, 2012 at 9:50 am
michael vessey (5/8/2012)
full text is stored outside the DB as full text catalogues - they have nothing to to with clustered or nonclustered indexessee this article to get you started
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server
full text indexing in this instance proboably won't help you as you need to update the catalogues before the results appear in the search - ie the search isn't realtime.
it would be better to improve the quality of your SQL and make sure you can use indexes properly - ie do you really need to use LIKE '%JONES%' - can you use ='JONES'
http://lmgtfy.com/?q=how+are+full+text+indexes+stored
MVDBA
May 8, 2012 at 9:51 am
... Surely it doesnt update the catalog every time a query it?
I cannot understand what are you up to exactly, but:
No, the FT catalog is not updated every time you query it
May 8, 2012 at 9:52 am
There are ways around this, so it shouldn't cause a problem.
May 8, 2012 at 9:54 am
Ah Ok, you dont know the answer. Could have just said.
Thanks for your help anyway.
May 8, 2012 at 9:55 am
here are the ways full text indexes are updated - typically they get updated on a schedule - therefore your results will not be realtime
http://msdn.microsoft.com/en-us/library/aa214782(v=sql.80).aspx
MVDBA
May 8, 2012 at 9:56 am
You can set your FT index population to CHANGE_TRACKING AUTO.
It will make FT index to be updated after data is modified, not immediately, but quite close to it.
May 8, 2012 at 9:57 am
They can be updated manually, using a trigger for example, which fires on the insert. Also, i dont need the catalog updates to be real time.
The issue was, I just wanted to know if a Full Text Index uses a considerable amount of space?
May 8, 2012 at 10:05 am
ggjjbb1983 (5/8/2012)
They can be updated manually, using a trigger for example, which fires on the insert. Also, i dont need the catalog updates to be real time.The issue was, I just wanted to know if a Full Text Index uses a considerable amount of space?
the answer depends on the size of your text/CLOB fields and the uniqueness of words in them
they are stored externally to SQL server and can be located on different disks to your data - i can't give you an exact size because i don't know your data, but all of this info is freely available via google
and your original question was
Is this a good option, and are there any disadvantages to Full Text Indexing please?
which is what we tried to answer
MVDBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply