Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full Text Search Query slow and Random Expand / Collapse
Author
Message
Posted Thursday, September 20, 2012 11:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
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


  Post Attachments 
Execution plan.sqlplan (1 view, 72.25 KB)
FTS Image.JPG (3 views, 61.63 KB)
FTS Image2.JPG (3 views, 64.40 KB)
Post #1362202
Posted Wednesday, July 24, 2013 2:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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


Post #1477260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse