September 20, 2012 at 11:50 am
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
July 24, 2013 at 2:11 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply