November 18, 2012 at 5:07 am
Hi,
I am quering a big table (about 150,000 rows) with a big varchar field (size 2000) which can't be indexed (and there's no point even if it could be). I am using Sql Server 2008.
The query I used till now was:
select *
from tbl_name
where field_name like '%bla bla%'
("bla bla" is according to what the user searched for)
In order to improve performence, I wann'a start using the Full-Text Search feature (already defined a catalog and a text index on this field).
I am a bit confused from what I read about quering with this option.
what query should I use in order to get exactly the same results as the query I used to use before?
* Comments:
1. I would like to get results which are not case sensative, as it worked before (meaning if the user searches for "LG" he will also get results that contains "Lg").
2. If user enters "Sams" he will also get "Samsung".
Thanks!
Eran.
November 18, 2012 at 3:51 pm
eranzo111 (11/18/2012)
Hi,I am quering a big table (about 150,000 rows) with a big varchar field (size 2000) which can't be indexed (and there's no point even if it could be). I am using Sql Server 2008.
The query I used till now was:
select *
from tbl_name
where field_name like '%bla bla%'
("bla bla" is according to what the user searched for)
In order to improve performence, I wann'a start using the Full-Text Search feature (already defined a catalog and a text index on this field).
I am a bit confused from what I read about quering with this option.
what query should I use in order to get exactly the same results as the query I used to use before?
* Comments:
1. I would like to get results which are not case sensative, as it worked before (meaning if the user searches for "LG" he will also get results that contains "Lg").
2. If user enters "Sams" he will also get "Samsung".
Thanks!
Eran.
Hi Eran,
Try the following, because you are using %bla bla% i think it might be this, i havent tested it so it might not return exactly what you were after.
-- Try first
SELECT *
FROM tbl_name
WHERE FREETEXT(*, 'bla bla');
-- Also try this as another option incase the top fails
SELECT col1,col2
FROM tbl_name
WHERE CONTAINS(field_name, 'bla bla');
November 19, 2012 at 1:21 am
Hi Tava,
With those 2 queries, I get partial results.
Let say the the text I query by is "eran":
SELECT *
FROM table_name
WHERE FREETEXT(*, 'eran');
SELECT *
FROM table_name
WHERE CONTAINS(field_name, 'eran');
And the DB has 4 rows:
1. ggg eran fff
2. hhheranttt
3. eranggg
4. hhhhh eran
The only rows I get as results are 1 and 4.
I Wish to get them all, as I did when I used the "like" keyword (I am doing that just for improving performence)
Thanks,
Eran.
November 19, 2012 at 1:54 am
eranzo111 (11/19/2012)
Hi Tava,With those 2 queries, I get partial results.
Let say the the text I query by is "eran":
SELECT *
FROM table_name
WHERE FREETEXT(*, 'eran');
SELECT *
FROM table_name
WHERE CONTAINS(field_name, 'eran');
And the DB has 4 rows:
1. ggg eran fff
2. hhheranttt
3. eranggg
4. hhhhh eran
The only rows I get as results are 1 and 4.
I Wish to get them all, as I did when I used the "like" keyword (I am doing that just for improving performence)
Thanks,
Eran.
Sorry Eran, just working off the top of my head so wont know till i get a chance to test but Try this:
SELECT *
FROM table_name
WHERE CONTAINS(*, '"eran"');
Hope that works
November 19, 2012 at 2:55 am
When working with full-text, you need to remember that it's behaviour is not the same as LIKE.
You will need to do additional work (in T-SQL or/and application) in order to make search closer to user expected behaviour, as the one of the most known issues with using full-text is a search for words containing special characters. While most of the special characters may not be of concerns, however there are two, which are quite common in people and company names: hyphen and ampersand.
Vanilla Full-Text search functionality ignores all of them...
November 19, 2012 at 3:22 am
Tava (11/19/2012)
eranzo111 (11/19/2012)
Hi Tava,With those 2 queries, I get partial results.
Let say the the text I query by is "eran":
SELECT *
FROM table_name
WHERE FREETEXT(*, 'eran');
SELECT *
FROM table_name
WHERE CONTAINS(field_name, 'eran');
And the DB has 4 rows:
1. ggg eran fff
2. hhheranttt
3. eranggg
4. hhhhh eran
The only rows I get as results are 1 and 4.
I Wish to get them all, as I did when I used the "like" keyword (I am doing that just for improving performence)
Thanks,
Eran.
Sorry Eran, just working off the top of my head so wont know till i get a chance to test but Try this:
SELECT *
FROM table_name
WHERE CONTAINS(*, '"eran"');
Hope that works
Hi Tava,
This query:
SELECT *
FROM table_name
WHERE CONTAINS(*, '"eran"');
Returns the same results - only rows number 1,4 are returned.
Thanks,
Eran
November 19, 2012 at 3:29 am
You need to use free-text wildcard "*" in your search string!
SELECT *
FROM table_name
WHERE CONTAINS(*, '"*eran*"');
Also, read my previous post regarding hyphens and ampersands... It will make it much more "interesting"... 😉
Actually you better to use column name instead of * in CONTAINS:
SELECT *
FROM table_name
WHERE CONTAINS([YourSearchColumn], '"*eran*"');
November 19, 2012 at 3:50 am
Eugene Elutin (11/19/2012)
You need to use free-text wildcard "*" in your search string!
SELECT *
FROM table_name
WHERE CONTAINS(*, '"*eran*"');
Also, read my previous post regarding hyphens and ampersands... It will make it much more "interesting"... 😉
Actually you better to use column name instead of * in CONTAINS:
SELECT *
FROM table_name
WHERE CONTAINS([YourSearchColumn], '"*eran*"');
Ah so it's "*string*" with the * I knew I was close just couldn't remember exact syntax. Thanks for jumping in -
November 19, 2012 at 5:51 am
Full text querying only does prefix matching, not postfix
'erin*' matches 'erinblah'
'*erin' doesn't match 'blaherin'
There is no way to match the last half of a word.
November 19, 2012 at 6:46 am
SpringTownDBA (11/19/2012)
Full text querying only does prefix matching, not postfix'erin*' matches 'erinblah'
'*erin' doesn't match 'blaherin'
There is no way to match the last half of a word.
Ough, YES! And there is a good reason for that:
There is no way to build an index in SQL which can help in such search.
So, partial and suffix match in T-SQL can be only done using LIKE, which will do full table scan to find relevant rows.
Yes, Google can do it , but it doesn't use RDBMS for this...;-)
November 19, 2012 at 7:31 am
A dead end I guess... 🙁
I was so happy when I saw the significant performence improvement when I used the full text search instead of the LIKE query, and now I unserstand that it can't logically do what I need...
🙁
November 19, 2012 at 9:48 am
eranzo111 (11/19/2012)
A dead end I guess... 🙁I was so happy when I saw the significant performence improvement when I used the full text search instead of the LIKE query, and now I unserstand that it can't logically do what I need...
🙁
Why so? Just use it for most common searches!
Make "prefix" based search default option.
When user will need to do "word contains" type of search, use LIKE.
Most of people when do search for something, type the beginning of the word not how the word ends.
November 19, 2012 at 2:38 pm
SpringTownDBA (11/19/2012)
Full text querying only does prefix matching, not postfix'erin*' matches 'erinblah'
'*erin' doesn't match 'blaherin'
There is no way to match the last half of a word.
I just learnt something new 🙂 , i always thought it allowed for postfix but i was wrong (not the first time) ... main thing is OP has an answer and people learnt something new.
November 20, 2012 at 7:36 am
Eugene Elutin (11/19/2012)
eranzo111 (11/19/2012)
A dead end I guess... 🙁I was so happy when I saw the significant performence improvement when I used the full text search instead of the LIKE query, and now I unserstand that it can't logically do what I need...
🙁
Why so? Just use it for most common searches!
Make "prefix" based search default option.
When user will need to do "word contains" type of search, use LIKE.
Most of people when do search for something, type the beginning of the word not how the word ends.
Thanks Eugene.
The bussiness requirment was to make the performence improvement changes transparent to the user, and keep the website with only the simple search text box (google style) as it was till now. And now I understand that it is not possible unless I'll start using a different search engine, such as google.
But I'm not sure if I'll be able to customize the results with such kind of a search engine, becuase I don't need to just show a links list like google results, but also a list of summery counters (which is also a link...), like in this web site for example, at the left of the page:
http://shopper.cnet.com/1770-5_9-0.html?query=lg&tag=srch
TVs (87) .
Flat-panel TVs (88)
etc...
By the way, they do show "Panasonic" at the results, when searcing by "anasonic", while the user doesn't have to choose which kind of search he wants. And the query time is not bad at all.
So I guess they are not quering a RDBMS.
Eran.
November 20, 2012 at 7:50 am
...
By the way, they do show "Panasonic" at the results, when searcing by "anasonic", while the user doesn't have to choose which kind of search he wants. And the query time is not bad at all.
So I guess they are not quering a RDBMS.
Eran.
They may well use RDBMS and LIKE, but they don't do it on a large dataset.
For example, they could do separate LIKE searches on their Brand and/or Product tables. They are no going to be too large. I guess, they do not search their "Customer" table in the same way. It all depends on what real requirements are. Google uses completely different fizzy-search techniques...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply