Full Text Search Query slow and Random

  • 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

  • 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