Full Text Search with Empty Search String

Hi,

I have implemented full text search in one of my applications and i am using FREETEXTTABLE to perform the search. Actually i wanna to show all rows from the table whenever search string is empty,

Although i am currently using Dynamic sql to do this task, but i just wanna to know best practices to handle the situation ? Is there any way that i could use WildCard to return all rows?

Please suggest me a better way of doing it.

Thanks

/* You will need to adapt these examples */

/*This is the general pattern for returning the unfiltered record set

when the parameter is NULL. */

WHERE MyColumn = ISNULL(@.myVariable, MyColumn)

/*The following code snippets are taken from a production sproc that performs a complex search that includes a FULL-TEXT search. The input parameter is @.Keywords, it's DEFAULT = '' and the WHERE clause works if @.Keywords is NULL or not.

*/

--First

DECLARE @.KeywordNull1int

DECLARE @.KeywordNull2int

--second

IF @.Keywords = ''

BEGIN

SET @.KeywordNull1 = 1

SET @.KeywordNull2 = 1

END

ELSE IF @.Keywords <> ''

BEGIN

SET @.KeywordNull1 = 1

SET @.KeywordNull2 = 2

END

--third

--this is part of a complex WHERE clause

AND((CONTAINS(f.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(fl.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(FFT.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)))

ORDER BY fl.FolderPath,[FileName],FileExtension

|||

Hi,

Thanks very much for your reply it will be very helpful for me in future projects, but i am using FREETEXTTABLE for search purposes not CONTAINS,

i am even not sure which one is better, Do you have any idea which one actually is ?

|||Since I have no idea what your SELECT statement that contains FREETEXTTABLE looks like or what you are trying to do I have no way to answer your question.|||

RE general question of comparison of CONTAINS/CONTAINSTABLE to FREETEXT/FREETEXTTABLE - none is better or worse, they are intended for different scenarios.

1) CONTAINS and CONTAINSTABLE have a simpler ranking function (called TF/IDF in Information Retrieval) and support a mini-query language that allows precise specification of the user's intent - is supports operators like AND, OR, NEAR, FORMSOF(INFLECTIONAL,...)/FORMSOF(THESAURUS,...) etc.

2) FREETEXT and FREETEXTTABLE are have more complex ranking (OKAPI BM 25 in IR terms) and are intended to more closely resemble Web search experience over full-text-indexed data in your database.

You may want to start with http://msdn2.microsoft.com/en-us/library/ms142494.aspx or more generally, at http://msdn2.microsoft.com/en-us/library/ms142547.aspx to get more details.

Best regards,

|||Thanks Denistch , it really helped me!!!|||

Hi Denistch,

Problem is still the same, let say if i use query select tblID from CONTAINSTABLE(Table1,*,'') as keytable where ....

so whatever the query is, if search string is empty, how can i use wildcard to return all the rows from Table1. e.g if i use something like CONTAINSTABLE(Table1,*,"a*") it will return all the matching words starting with 'a', but i dont want just 'a', i want all rows!!!

Any help will be highly appreciated .

I know i can use CONTAINS or LIKE, but i have got very long dynamic query, if change to CONTAINS it does'nt give me rank, so i would be changing the whole query just for one scenario....which i dont wanna do!

Thanx!

sql

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