SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Text Search Query slow and Random


Full Text Search Query slow and Random

Author
Message
pamozer
pamozer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1259 Visits: 443
We have a full text Index set up on a table that has millions of records. The query that is run against it is always the same. With only certain parameters changing.


SELECT AuditLogID, Application, AuditActionTypeID, AuditTypeID, ChangeSet, EntityTypeID, EntityTypeText, SubEntityTypeID, SubEntityTypeText, CustomerID, PracticeID, UserID, UserName, CreatedDate
FROM AuditLog
WHERE (CreatedDate between '9/18/2012' and '9/21/2012')
AND (CONTAINS(([ChangeSet], [EntityTypeText], [SubEntityTypeText], [UserName]), 'Eduvigis'))
AND (CustomerID = 2046 and PracticeID = 1)
ORDER BY CreatedDate DESC


SELECT AuditLogID, Application, AuditActionTypeID, AuditTypeID, ChangeSet, EntityTypeID, EntityTypeText, SubEntityTypeID, SubEntityTypeText, CustomerID, PracticeID, UserID, UserName, CreatedDate
FROM AuditLog
WHERE (CreatedDate between '9/18/2012' and '9/21/2012')
AND (CONTAINS(([ChangeSet], [EntityTypeText], [SubEntityTypeText], [UserName]), 'Phillips'))
AND (CustomerID = 2046 and PracticeID = 1)
ORDER BY CreatedDate desc



The first query takes less than a second to run but the second query takes 1 min and 20 seconds. I have tried the second query with option(RECOMPILE) and nothing changes.

I have attached the execution plan.


Here is what the table looks like:


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AuditLog](
[AuditLogID] [bigint] IDENTITY(1,1) NOT NULL,
[Application] [varchar](50) NOT NULL,
[AuditActionTypeID] [int] NOT NULL,
[AuditTypeID] [int] NOT NULL,
[ChangeSet] [xml] NULL,
[EntityTypeId] [int] NOT NULL,
[EntityTypeText] [varchar](100) NULL,
[SubEntityTypeId] [int] NULL,
[SubEntityTypeText] [varchar](100) NULL,
[CustomerId] [int] NOT NULL,
[PracticeId] [int] NULL,
[UserId] [int] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED
(
[AuditLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


CREATE NONCLUSTERED INDEX [IX_AuditLog_CustomerID_PracticeID_CreatedDate] ON [dbo].[AuditLog]
(
[CustomerId] ASC,
[PracticeId] ASC,
[CreatedDate] 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) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AuditLog_EntityTypeID_CustomerID_PracticeID_AuditTypeID] ON [dbo].[AuditLog]
(
[EntityTypeId] ASC,
[CustomerId] ASC,
[PracticeId] ASC,
[AuditTypeID] ASC,
[CreatedDate] 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 = 90) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditLog] WITH CHECK ADD CONSTRAINT [FK_AuditLog_AuditActionType] FOREIGN KEY([AuditActionTypeID])
REFERENCES [dbo].[AuditActionType] ([AuditActionTypeID])
GO

ALTER TABLE [dbo].[AuditLog] CHECK CONSTRAINT [FK_AuditLog_AuditActionType]
GO

ALTER TABLE [dbo].[AuditLog] WITH CHECK ADD CONSTRAINT [FK_AuditLog_AuditType] FOREIGN KEY([AuditTypeID])
REFERENCES [dbo].[AuditType] ([AuditTypeID])
GO

ALTER TABLE [dbo].[AuditLog] CHECK CONSTRAINT [FK_AuditLog_AuditType]
GO




Any help would be greatly appreciated
Attachments
Execution plan.sqlplan (4 views, 72.00 KB)
FTS Image.JPG (8 views, 61.00 KB)
FTS Image2.JPG (6 views, 64.00 KB)
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 615
Can you try this and see what takes the longest?

I'm guessing it's the FT search. Maybe if it gets narrowed down before doing that it will help somehow.


declare @tbl1 table (id bigint primary key)
declare @tbl2 table (id bigint primary key)
declare @tbl3 table (id bigint primary key)

insert into @tbl1
SELECT AuditLogID
FROM AuditLog
WHERE (CreatedDate between '9/18/2012' and '9/21/2012')
AND (CustomerID = 2046 and PracticeID = 1)

--print time

insert into @tbl2 select auditlogid from
@tbl1 t inner join
auditlog a on a.auditlogid = t.id
where
CONTAINS(([ChangeSet], [EntityTypeText], [SubEntityTypeText], [UserName]), 'Eduvigis')

--print time

insert into @tbl3 select auditlogid from auditlog where
@tbl1 t inner join
auditlog a on a.auditlogid = t.id
where
CONTAINS(([ChangeSet], [EntityTypeText], [SubEntityTypeText], [UserName]), 'Phillips')

--print time

SELECT AuditLogID, Application, AuditActionTypeID, AuditTypeID, ChangeSet, EntityTypeID, EntityTypeText, SubEntityTypeID, SubEntityTypeText, CustomerID, PracticeID, UserID, UserName, CreatedDate
from @tbl2 t inner join
AuditLog a on a.auditlogid = t.id
ORDER BY CreatedDate desc

--print time

SELECT AuditLogID, Application, AuditActionTypeID, AuditTypeID, ChangeSet, EntityTypeID, EntityTypeText, SubEntityTypeID, SubEntityTypeText, CustomerID, PracticeID, UserID, UserName, CreatedDate
from @tbl3 t inner join
AuditLog a on a.auditlogid = t.id
ORDER BY CreatedDate desc

--print time



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search