Fulltext searching very slow
-
Hi,
I have transactional table (tblDocument). I've create a full text catalog on column [docSubject] in a separated partition.(with manual tracking and it's not sensitive).I've increase my server virtual memory for fdhost. My search should be like this :
SELECT docId,DocSubject,docDate
FROM tblDocument WITH (NOLOCK)
WHERE docDate BETWEEN 13880101 AND 13880901 AND CONTAINS(docSubject , '"آقای صادقی*"')
(combination my criteria and "contains" like that )
this query takes about 1 min to execute !!!!
I'm wonder that when I dont use my criteria query executes in 0 sec !!!
and the same will occurs when I use my criteria only !!!
use full text search only :
SELECT docId,DocSubject,docDate
FROM tblDocument WITH (NOLOCK)
WHERE CONTAINS(docSubject , '"آقای صادقی*"')
use my criteria only :
SELECT docId,DocSubject,docDate
FROM tblDocument WITH (NOLOCK)
WHERE docDate BETWEEN 13880101 AND 13880901
what should I do now?
-
anyone cant help me!!! :crying:
my boss will kill me :crying::crying:
-
Try tuning you indexes, NOLOCK is guaranteed to read uncommitted transactions but not necessarily guaranteed to improve performance.
----------------------------------------------------
-
In my experience (and many others as you'll find if you Google your problem), full-text indexing performance in SQL 2008 is pretty bad. Not only is the performance of the FT searches bad, but the new architecture causes locking and performance issues with everything else on the server as well. One of the best posts I've found about the problem, with a lot of suggestions about how to resolve it, is at http://blog.stackoverflow.com/2008/11/sql-2008-full-text-search-problems/.
Personally, none of them worked for us. We've temporarily moved the full-text indexes off to a linked server (running SQL 2005) as a band-aid and are in the process of re-architecting our full-text search facilities with Lucene.NET since SQL 2008 just doesn't appear to be capable of doing the job.
-
I changed my search criteria (about wildcard ) and rebuild fts indexes every night. it works better but its not acceptable yet !
my sample query :
SELECT docId,DocSubject,docDateFROM tblDocument WITH (NOLOCK)
WHERE docDate BETWEEN 13880101 AND 13880901 AND CONTAINS(docSubject , '"???? ?????"')
-
mmartin1 (12/1/2009)
Try tuning you indexes, NOLOCK is guaranteed to read uncommitted transactions but not necessarily guaranteed to improve performance.Not to mention the other problems that NOLOCK can cause (double reads or miss reads).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
farax_x (12/9/2009)
I changed my search criteria (about wildcard ) and rebuild fts indexes every night. it works better but its not acceptable yet !my sample query :
SELECT docId,DocSubject,docDateFROM tblDocument WITH (NOLOCK)
WHERE docDate BETWEEN 13880101 AND 13880901 AND CONTAINS(docSubject , '"???? ?????"')
A little more information could prove useful. Please provide table structure, index structure, number of records in the table, table size (MB) and index size (mb), as well as the full execution plan.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (12/11/2009)
farax_x (12/9/2009)
I changed my search criteria (about wildcard ) and rebuild fts indexes every night. it works better but its not acceptable yet !my sample query :
SELECT docId,DocSubject,docDateFROM tblDocument WITH (NOLOCK)
WHERE docDate BETWEEN 13880101 AND 13880901 AND CONTAINS(docSubject , '"???? ?????"')
A little more information could prove useful. Please provide table structure, index structure, number of records in the table, table size (MB) and index size (mb), as well as the full execution plan.
table structure :
CREATE TABLE [dbo].[tblDocument]([docId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[docOwnerDepartmentId] [uniqueidentifier] NOT NULL,
[docOwnerPersonnelId] [uniqueidentifier] NOT NULL,
[docOwnerName] [nvarchar](150) NOT NULL,
[docDescription] [nvarchar](max) NOT NULL,
[docType] [smallint] NOT NULL,
[docNo] [nvarchar](40) NOT NULL,
[docDate] [int] NULL,
[docSubject] [nvarchar](370) NULL,
[docIntId] [int] IDENTITY(1,1) NOT NULL,
[docDeleteOk] [bit] NULL,
CONSTRAINT [PK_tblDocument] PRIMARY KEY NONCLUSTERED
(
[docIntId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [PRIMARY]
)
indexes :
CREATE NONCLUSTERED INDEX [DocDate] ON [dbo].[tblDocument]
(
[docDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [docId] ON [dbo].[tblDocument]
(
[docId] ASC
)
INCLUDE ( [docType],
[docRegNo],
[docRegDate],
[docNo],
[docDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [FGIndexingDoc]
GO
CREATE UNIQUE NONCLUSTERED INDEX [docIntId] ON [dbo].[tblDocument]
(
[docIntId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [DocNo] ON [dbo].[tblDocument]
(
[docNo] ASC
)
INCLUDE ( [docType],
[docDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [DocOrg] ON [dbo].[tblDocument]
(
[docOrganizationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [docSecINcluded] ON [dbo].[tblDocument]
(
[docSecurityId] ASC
)
INCLUDE ( [docId],
[docNo],
[docDate],
[docSubject],
[docAttachTotal],
[docIntId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [OwnerDep] ON [dbo].[tblDocument]
(
[docOwnerDepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [OwnerName] ON [dbo].[tblDocument]
(
[docOwnerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [OwnerPrs] ON [dbo].[tblDocument]
(
[docOwnerPersonnelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [RegDat] ON [dbo].[tblDocument]
(
[docRegDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [RegDate] ON [dbo].[tblDocument]
(
[docRegisterDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [RegNo] ON [dbo].[tblDocument]
(
[docRegNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [Subject] ON [dbo].[tblDocument]
(
[docSubject] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [Type] ON [dbo].[tblDocument]
(
[docType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [FGIndexingDoc]
GO
CREATE NONCLUSTERED INDEX [Type_date_Inc] ON [dbo].[tblDocument]
(
[docType] ASC,
[docDate] ASC
)
INCLUDE ( [docId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [FGIndexingDoc]
full text indexes :
CREATE FULLTEXT INDEX ON [dbo].[tblDocument]([docDescription] LANGUAGE [English],
[docKeyWord] LANGUAGE [English],
[docOwnerName] LANGUAGE [English],
[docSubject] LANGUAGE [English])
KEY INDEX [docIntId]ON ([IstgDocFTS], FILEGROUP [FTS])
WITH (CHANGE_TRACKING = MANUAL, STOPLIST = SYSTEM)
GO
number of records :4,571,927
data space: 8,059.547 MB
index space : 3,822.008 MB
execution plan :
-
Please attach the actual execution plan, filegroup structure, and hard disk configs (Raid level)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
[/url]Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply