May 5, 2016 at 2:37 pm
Hi All,
We recently enabled Full text search in SQL server 2014.
We have an address table with AddressName column. Full text enabled on AddressName.
AddressName sample data : 1234 N Main St Chicago IL 60001-2233
When we search for 1234 N Main without quotes, it takes approximately 20 seconds.
When we search for "1234 N Main" with quotes, it returns in 1 second.
Not only above address any address that contains N at any place in search takes above 20 seconds.
When I search for 1234 S Main without quotes, it also returns 1234 N Main and executes in less than 1 sec.
Is N is used for any internal purpose in FullText ?
May 5, 2016 at 8:18 pm
There's the old joke, "guy goes to the doctor and says, 'Doc, it hurts when I do this...' so the doctor says, 'don't do that'."
I digress, can you post the execution plan for both queries?
-- Itzik Ben-Gan 2001
May 26, 2016 at 8:23 am
The execution plan is same for all searches.
I created a table with one row.
create table dbo.Addr
(
AddressID int identity primary key,
AddressLine varchar(100)
)
insert into dbo.Addr values ('123 W Main St APT 666')
Fulltext index enabled on table. Then looking for fulltext index keywords.
SELECT display_term, column_id, document_count
FROM sys.dm_fts_index_keywords(DB_ID('FTDB'), OBJECT_ID('Addr'))
The data looks like below. (Note: W is a stopword. so there is no keyword for W)
display_termcolumn_iddocument_count
12321
66621
apt21
main21
nn12321
nn66621
st21
END OF FILE21
surprisingly when i search for n*, i am getting the address which do not have n*.
select a.*
from Addr a
join (
SELECT a.[Key] AS AddressID
FROM ContainsTable([Addr], (AddressLine), '"n*"') a
) z on a.AddressID = z.AddressID
GO
why these nn rows are created internally ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply