Full text search alternative without leading wildcard limitation

As has been mentioned several times in this group, SQL Server does not
support a leading wildcard in full text searches (i.e., CONTAINS(field,
'*tion')).
To get around this I have been looking at external full text search
engines that might not have this restriction but with little success:
most of the others seem to have the same restriction (e.g., Lucene,
swish-e, etc.). The one that did not was Namazu but it was slow.
So my question is: has anyone found or come up with a full text search
application that does not have this wildcard limitation and is fast
(faster than simply using LIKE)?
google@.macrotex.net wrote on 24 Mar 2006 06:32:06 -0800:

> As has been mentioned several times in this group, SQL Server does not
> support a leading wildcard in full text searches (i.e., CONTAINS(field,
> '*tion')).
> To get around this I have been looking at external full text search
> engines that might not have this restriction but with little success:
> most of the others seem to have the same restriction (e.g., Lucene,
> swish-e, etc.). The one that did not was Namazu but it was slow.
> So my question is: has anyone found or come up with a full text search
> application that does not have this wildcard limitation and is fast
> (faster than simply using LIKE)?
You could, if you have the space in your database, create a copy of the
columns you wish to search in this way in reverse, and use FTS to index
this. eg. add a column called fieldreverse, and do
UPDATE table SET fieldreverse = REVERSE(field)
then index that, and search it using
CONTAINS(field, 'noit*'))
You could automatic the creation of the reversed data using a trigger on the
table for the normal column. Not a pretty solution, but it'll work.
Dan

Full Text Search

Hi,

Would below mentioned thing is possible in sqlserver 2005 full text.

User Table - Table

Id Description - Column name

1 'Mission Impossible' - a row

I want to ge this row if the search criteria is 'pos'. it is simillar to ' %pos%' in like search

is it possible to get that row using Contains() or Freetext(). I tried the following, but it doesnt return any rows.

CONTAINS(Description, ' "*pos*" ')

~ Arjun

SQLServer 2005 does not support arbitrary substring or regular expression yet. Right now you can only do prefix using fulltext.

|||

have u enabled the full text search in your server.

if so then you need not give the % symbol in the text which is to be searched. There is another option in the CONTAINS function.checkt it out.

please try this.

Ravi.

Full text Pattern Search

Hi,
Would below mentioned thing is possible in sqlserver 2005 full text.
User Table - Table
Id Description - Column name
1 'Mission Impossible' - a row
I want to ge this row if the search criteria is 'pos'. it is simillar
to ' %pos%' in like search
is it possible to get that row using Contains() or Freetext(). I
tried the following, but it doesnt return any rows.
CONTAINS(Description, ' "*pos*" ')
~ Arjun
No, you can't do this directly. While you can do some suffix (end of word)
based searches, you can't do prefix (beginning of word) based searches.
It may be possible to use the thesaurus expansion feature if you know in
advance what all stem would be.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<priyananth@.gmail.com> wrote in message
news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
> Hi,
> Would below mentioned thing is possible in sqlserver 2005 full text.
> User Table - Table
> Id Description - Column name
> 1 'Mission Impossible' - a row
> I want to ge this row if the search criteria is 'pos'. it is simillar
> to ' %pos%' in like search
> is it possible to get that row using Contains() or Freetext(). I
> tried the following, but it doesnt return any rows.
> CONTAINS(Description, ' "*pos*" ')
> ~ Arjun
>
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||You would have to write your own word breaker to do this, and then write a
stemmer to stem for prefixes. I still don't think it can be done.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<priyananth@.gmail.com> wrote in message
news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
> Hi Hilary,
> Thank you for u r reply. Would it be possible to modify the indexing
> mechanism which does. or could you please explain how to do it in
> thesaurus.
> Thanks.
> ~Arjun.
>
> Hilary Cotter wrote:
>
|||I have the same problem.
It seems crippling that full text indexing does not support searching for
terms in the middle of token. Is there really that great of a demand for
searching the beginnings of words? Are there any third party products you
can recommend that will help me provide a search feature that will integrate
with SQL server?
CONTAINS term* works great but doesn't return the results I need and
'%term%' is far too slow to use in a production environment over 169000 rows
and multiple columns.
Your input is greatly appreciated.
"Hilary Cotter" wrote:

> You would have to write your own word breaker to do this, and then write a
> stemmer to stem for prefixes. I still don't think it can be done.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
>
>
|||You might want to look at Lucene. The story on its performance is complex.
Basically SQL FTS does a really good job and offers better performance than
Lucene on a single server when you get big. When you get big you start
needing many machines to scale using lucene. The other problem with lucence
is that when you want to do property searches you end up storing data in
your full-text index and you search response times decline radically. So you
need to partition which means more machines.
How about you return your results from sql fts and then trim them using the
like? You will need to use the thesaurus option to expand your search
criteria to all possible terms you might be looking for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...[vbcol=seagreen]
>I have the same problem.
> It seems crippling that full text indexing does not support searching for
> terms in the middle of token. Is there really that great of a demand for
> searching the beginnings of words? Are there any third party products you
> can recommend that will help me provide a search feature that will
> integrate
> with SQL server?
> CONTAINS term* works great but doesn't return the results I need and
> '%term%' is far too slow to use in a production environment over 169000
> rows
> and multiple columns.
> Your input is greatly appreciated.
> "Hilary Cotter" wrote:
|||My goal is to have a customer searching for parts over several fields and
finding any one of several tokens anywhere within the text (similar to the
LIKE '%term%' functionality). For example, they might want to search for
'56789' and get back the part with part number 'GAR56789'. I don't see how
it is possible to do that with FTS at all. If I knew what the search terms
were going to be I could certainly pre-add them to the thesaurus but the
point of the search is that I can't predict what terms they might enter.
Maybe this is where SQL server functionality breaks down and it's time to
move on to search appliance or software platform. I'll take a look at Lucene.
"Hilary Cotter" wrote:

> You might want to look at Lucene. The story on its performance is complex.
> Basically SQL FTS does a really good job and offers better performance than
> Lucene on a single server when you get big. When you get big you start
> needing many machines to scale using lucene. The other problem with lucence
> is that when you want to do property searches you end up storing data in
> your full-text index and you search response times decline radically. So you
> need to partition which means more machines.
> How about you return your results from sql fts and then trim them using the
> like? You will need to use the thesaurus option to expand your search
> criteria to all possible terms you might be looking for.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Uriah" <Uriah@.discussions.microsoft.com> wrote in message
> news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...
>
>
|||If it is always a prefix you might want to store your content in reverse and
then reverse your query and do a wildcard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:B695EBD3-11F8-4DEA-A81D-A1828178C577@.microsoft.com...[vbcol=seagreen]
> My goal is to have a customer searching for parts over several fields and
> finding any one of several tokens anywhere within the text (similar to the
> LIKE '%term%' functionality). For example, they might want to search for
> '56789' and get back the part with part number 'GAR56789'. I don't see
> how
> it is possible to do that with FTS at all. If I knew what the search
> terms
> were going to be I could certainly pre-add them to the thesaurus but the
> point of the search is that I can't predict what terms they might enter.
> Maybe this is where SQL server functionality breaks down and it's time to
> move on to search appliance or software platform. I'll take a look at
> Lucene.
> "Hilary Cotter" wrote:

FTS Performance in SQL 2005

I do not see any resolution to this problem mentioned, and have a similar
problem.
I have just implemented a database with 12 tables, one FT index on a text
column for each table. The unique key column is a uniqueidentifier.
This is under SQL Server 2005, SP1, Windows 2003 Server, on an x64 dual
processor system with 16 GB RAM. SQL Server is limited to 12 GB RAM and
nothing else runs on the box.
The query uses CONTAINSTABLE.
We have an automated process that feeds thousands of queries, one at a time,
to run FT searches.
It appears that the searches run fine for a while, then the searches take
longer and longer, until eventually the search never returns, for hours
anyway.
When we see this happening, in Windows Task Manager we see that process
msftesql shows PF Delta up over 50,000.
The Memory Usage and VM Size never increase over about 65 MB and 20MB.
Did you ever find a solution for this ?
Thanks.
Doug Funk
News Data Services
dfunk@.newsdataservice.com
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:c4366deffa728c87fe6f490db50@.msnews.microsoft. com...
> Hello KaMa,
> The maximum equates to process ~4.5GB/s thats a lot.
> Can you post you query plans and the output of statistics IO
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
Hi Hilary,
Your comment here is a bit scary. It sounds like the FTS capabilities
of Sql Server 2005 are not ready for production. Can you detail a bit
more the problems you encounter that force you to restart the sql fts
once a week? Is MS aware of that problem? What are their
recommendations? Do you know of any upcoming patch or SP that would fix
this?
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> We pound full-text search the same way you do. There are advantages to a
> multi-proc machine - a quad or eight way. We have to restart sql fts once a
> week. We find that smaller tables work better - where smaller is 50 million
> or so rows.
> We also found the following settings work well:
> setting a high resource usage to 5 and reorganize frequently.
> set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0,
> set max full-text crawl range to the number of cpu's on your system,
> index text only,
> put your catalogs on the fastest disk subsystem (RAID 10) possible
> preferrably with their own controller,
> and run 64 bit.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug Funk" <doug.funk@.infomax-systems.com> wrote in message
> news:lAN_g.60102$OI1.44332@.newsfe15.lga...
|||Basically we find that the queries start taking longer and a bounce seems to
improve performance.
I have not communicated this to MS. You might want to open a support
incident yourself.
When we had a single table of over 300 million rows and pushing 2 terabytes
we had no end of problems with SQL FTS. After breaking the table up into 50
million row partitions we have had no real problems, but still bounce fts
weekly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tony.newsgrps@.gmail.com> wrote in message
news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
> Hi Hilary,
> Your comment here is a bit scary. It sounds like the FTS capabilities
> of Sql Server 2005 are not ready for production. Can you detail a bit
> more the problems you encounter that force you to restart the sql fts
> once a week? Is MS aware of that problem? What are their
> recommendations? Do you know of any upcoming patch or SP that would fix
> this?
> Tony.
>
> Hilary Cotter wrote:
>
|||Thank you for your answer. We'll look at partitioning our table if we
get into similar problems.
One quick follow up:
We keep on growing our table and the performance keeps on dropping.
With 8 million rows we had about 15 queries/sec. With 20 million rows,
we dropped to about 2 queries per sec. The server seems completely
underused though. The CPU and memory usage are very low and we see a
lot of page faults. Any idea what happened and how we could get back to
15 queries/sec? Could it be that the index needs to be re-organized or
something like that?
Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
match only a very limited set of documents (100 matching records max
out of 20 millions).
Also, with your 300 millions/2TB table, what performance do you get on
queries (on average) and on what hardware?
Thanks a lot for your guidance.
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> Basically we find that the queries start taking longer and a bounce seems to
> improve performance.
> I have not communicated this to MS. You might want to open a support
> incident yourself.
> When we had a single table of over 300 million rows and pushing 2 terabytes
> we had no end of problems with SQL FTS. After breaking the table up into 50
> million row partitions we have had no real problems, but still bounce fts
> weekly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
|||can you do this for me and post the results back here
sp_configure 'max server memory (MB)'
I don't think you have left enough memory for the OS and MSSearch.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tony.newsgrps@.gmail.com> wrote in message
news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...
> Thank you for your answer. We'll look at partitioning our table if we
> get into similar problems.
> One quick follow up:
> We keep on growing our table and the performance keeps on dropping.
> With 8 million rows we had about 15 queries/sec. With 20 million rows,
> we dropped to about 2 queries per sec. The server seems completely
> underused though. The CPU and memory usage are very low and we see a
> lot of page faults. Any idea what happened and how we could get back to
> 15 queries/sec? Could it be that the index needs to be re-organized or
> something like that?
> Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
> match only a very limited set of documents (100 matching records max
> out of 20 millions).
> Also, with your 300 millions/2TB table, what performance do you get on
> queries (on average) and on what hardware?
> Thanks a lot for your guidance.
> Tony.
> Hilary Cotter wrote:
>
|||Hi Hilary,
thanks for the reply.
We have total 4gb ram on the server.
Currently SQLServer has max server memory (MB) set to 2048. I tried giving
it less (1024) but didn't notice any performance differences.
Any ideas?
When i allow SQLServer to use more memory (up to 3gb) i notice performance
downgrades with time probably because one SQLServer reaches its 3gb theres
not much left for OS/full-text engine.
What seems really strange is that full-text engine itself only uses about
7mb ram and has approx 100k page faults / sec.
Thanks,
Mikhail
"Hilary Cotter" wrote:
[vbcol=seagreen]
> can you do this for me and post the results back here
> sp_configure 'max server memory (MB)'
> I don't think you have left enough memory for the OS and MSSearch.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...