Sorry for the lack of information, my bad.
The DDL of the table is as follows.
CREATE TABLE [dbo].[Content](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[LegacyID] [int] NOT NULL,
[ContentType] [int] NOT NULL,
[CaseId] [nchar](50) NOT NULL,
[Title] [nvarchar](1000) NOT NULL,
[Introduction] [ntext] NOT NULL,
[FullText] [ntext] NOT NULL,
[EventDate] [datetime] NOT NULL,
[Location] [nvarchar](1000) NOT NULL,
[Address] [nvarchar](1000) NOT NULL,
[FileName] [nvarchar](1000) NOT NULL,
[IsArchived] [tinyint] NOT NULL,
[Author] [nvarchar](1000) NOT NULL,
[Created] [datetime] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[AlertSent] [datetime] NOT NULL,
[Published] [datetime] NOT NULL,
[IsPublic] [bit] NOT NULL,
[BannerCode] [nvarchar](1000) NOT NULL,
[IsDealReporter] [bit] NOT NULL,
[EditLogDate] [datetime] NULL,
[IsDraft] [tinyint] NOT NULL,
[IsAlertSent] [tinyint] NOT NULL,
[NominatingEntity] [nvarchar](50) NOT NULL,
[JudiciaryID] [int] NOT NULL,
[CourtID] [int] NOT NULL,
[BirthPlace] [nvarchar](50) NOT NULL,
[BirthYear] [int] NOT NULL,
[OfficePhone] [nvarchar](50) NOT NULL,
[CourtAddress] [nvarchar](500) NULL,
[CourtLink] [nvarchar](1000) NULL,
[AdditionalCourtInfo] [nvarchar](max) NOT NULL,
[NominatingPresident] [nvarchar](50) NOT NULL,
[IssueInNomination] [nvarchar](max) NOT NULL,
[CommissionDate] [datetime] NOT NULL,
[ContentSubType] [int] NOT NULL,
[InternalNote] [nvarchar](150) NOT NULL,
[Picture] [nvarchar](500) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[Ranking] [int] NOT NULL,
CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
There are two indexes on this table as follows:
CREATE TABLE [dbo].[Content] ADD CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
There is also a trigger on the table:
CREATE TRIGGER [dbo].[trg_Content_update]
ON [dbo].[Content]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
-- Must perform an exec to the stored procedure because the datatype text used
-- for content table is not permitted in inserted table of trigger
declare @ContentID int
select @ContentID = ID from inserted
update ContentSearchTable
set
ContentType = c.ContentType,
ColumnAll = c.Author + ' ' + c.Title + ' ' + cast(c.FullText as nvarchar(max)),
ColumnTitle = c.Title,
ColumnAuthor =
case
when c.Author = '' then NULL
else c.Author
end,
ColumnContent = dbo.StripHTML(cast(c.FullText as nvarchar(max))),
ColumnContent25 = dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 25),
ColumnContent50 = dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 50),
ColumnContent100 = dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 100),
EstimatedWordCount = dbo.WordCount(dbo.StripHTML(FullText)),
LastUpdated = c.LastUpdate,
Published = c.Published
FROM ContentSearchTable cst
INNER JOIN [Content] c ON c.ID = cst.ContentID
WHERE
c.ContentType IN (6,7,1,4,5,3,2)
AND cast(c.FullText as varchar(max)) <> ''
AND c.ID = @contentID
AND cst.ContentID = @ContentID
END
I have attached the query plan to this post.
I can see from the query plan that the main cost of the query is on the object "PK_ContentSearchTableID"
This is the primary key on the second table, once an update occurs on the first table "Content" the trigger is executed which then updates the second table "ContentSearchTable".
The second table is as follows.
CREATE TABLE [dbo].[ContentSearchTable](
[ContentSearchTableID] [int] IDENTITY(1,1) NOT NULL,
[ContentID] [int] NULL,
[ContentType] [int] NULL,
[ColumnAll] [nvarchar](max) NULL,
[ColumnTitle] [nvarchar](1000) NULL,
[ColumnAuthor] [nvarchar](1000) NULL,
[ColumnContent] [nvarchar](max) NULL,
[ColumnContent25] [nvarchar](max) NULL,
[ColumnContent50] [nvarchar](max) NULL,
[ColumnContent100] [nvarchar](max) NULL,
[EstimatedWordCount] [int] NULL,
[LastUpdated] [datetime] NULL,
[Published] [datetime] NULL,
CONSTRAINT [PK_ContentSearchTableID] PRIMARY KEY CLUSTERED
(
[ContentSearchTableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Please let me know if I am missing any more information required.
Many, many thanks,
Lewis