July 18, 2014 at 2:29 am
Hi
I have a single table which I populate periodically from other tables, and then I have a full text index on that single table for search queries.
More often than not, the search queries return a result in about 1 second which, although slow for my needs, is probably as good as I can expect given the 15 million rows of data.
But now and again the same search takes 15-20 seconds. It's taking that long right now, and it seems that a full population is in progress for the index.
Is it normal for full population to kick off randomly during the day and to take a long time to complete? Will the queries be slower while this is happening, and if so, what is the best workaround? Have I missed something basic here?
Here is the table I created the full text index on (the INDEX_TEXT and TABLE_ID fields are included in the full text index)
CREATE TABLE [dbo].[F_INDEX](
[INDEX_ID] [uniqueidentifier] NOT NULL,
[TABLE_ID] [sysname] NOT NULL,
[TABLE_PK] [nvarchar](50) NULL,
[INDEX_TEXT] [nvarchar](max) NOT NULL,
[INDEX_REFERENCE] [nvarchar](50) NULL,
[INDEX_DESC] [nvarchar](300) NULL,
[INDEX_DETAIL] [nvarchar](300) NULL,
[IS_ACTIVE] [bit] NULL,
[SEARCH_ID] [int] NULL,
[TABLE_ID_LINKED] [sysname] NULL,
[TABLE_PK_LINKED] [nvarchar](50) NULL,
[LINK_REFERENCE] [nvarchar](50) NULL,
[LINK_DESC] [nvarchar](300) NULL,
[LINK_DETAIL] [nvarchar](300) NULL,
CONSTRAINT [PK__F_INDEX__CB49C77A67A7FA75] PRIMARY KEY NONCLUSTERED
(
[INDEX_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[F_INDEX] ADD DEFAULT (newid()) FOR [INDEX_ID]
GO
July 20, 2014 at 5:31 pm
Hi
Some updates. I've worked out how population works and I am now doing incremental population periodically, triggered by a job I've added to sql agent so that I've control over it.
I've also changed the uniqueidentifier primary key to an int identity primary key, I've also added a timestamp column and indexed it for incremental population. I've an index on SEARCH_PRIORITY as certain types of result are more important than others. No, I can't use weighting because it's too damn slow. Once I add more than one weight, the query is always >3 seconds to return.
And the query in my stored proc looks like this :
SelectTop 20 F_INDEX_ID,SEARCH_ID,SEARCH_DESC,TABLE_ID,TABLE_PK,INDEX_TEXT,SEARCH_PRIORITY
From F_INDEX i
WhereContains(INDEX_TEXT,@SEARCH_TEXT)
Order By SEARCH_PRIORITY
Option (Optimize For (@SEARCH_TEXT = 'JONATHAN'))
I am aware that Top... Order By will be slower than Top... obviously, but that's the business requirement.
There are about 15 million rows in F_INDEX. Generally I get a result in about 1.5 - 3 seconds, which is too slow anyway. But now and again it will take 10 seconds or more.
Server has 16GB RAM, monitored closely and there are no problems with CPU / RAM when executing. No errors in the crawl logs.
What's going on here?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply