April 17, 2013 at 7:32 am
Hi,
I have a standard update statement that updates one row at a time. The table size is 340,000 rows, so it's large but it's not huge.
The first time I run the query it takes a very long time considering its a simple update, around 40 seconds. However the second time I run the same update statement it takes less than a second.
I just wondered if anyone might be able to assist in letting me know where I might be able to start looking to find out why the update statement is so slow?
Any help would be appreciated.
Thanks,
Lewis
April 17, 2013 at 7:36 am
lewisdow123 (4/17/2013)
Hi,I have a standard update statement that updates one row at a time. The table size is 340,000 rows, so it's large but it's not huge.
The first time I run the query it takes a very long time considering its a simple update, around 40 seconds. However the second time I run the same update statement it takes less than a second.
I just wondered if anyone might be able to assist in letting me know where I might be able to start looking to find out why the update statement is so slow?
Any help would be appreciated.
Thanks,
Lewis
Hi there,
The part of your question that worries me is "updates one row at a time".
Your best bet is to follow the advise in this article about how to post a performance problem[/url]. It'd make it much easier to point you in the right direction, or perhaps even suggest improvements.
Thanks.
April 17, 2013 at 7:49 am
The update statement is as follows.
UPDATE Content SET ContentType =5,CaseId ='',Title ='XYZ',EventDate ='1980-01-01 00:00:00',Location ='',Address ='',FileName ='',URL ='',IsArchived =0,Author ='Magnus Dowson',LastUpdate ='2013-04-11 16:49:16',AlertSent ='2013-01-15 17:12:04',Published ='2013-01-15 17:08:22',IsPublic =0,BannerCode ='',IsDraft =0,IsAlertSent =1,NominatingEntity ='President',JudiciaryID =0,CourtID =0,BirthPlace ='',BirthYear =0,OfficePhone ='',CourtAddress ='',CourtLink ='',AdditionalCourtInfo ='',NominatingPresident ='',IssueInNomination ='',CommissionDate ='1900-01-01 00:00:00',ContentSubType =0,InternalNote ='',Picture ='',FirstName ='',Name ='',Ranking =5 WHERE ID=307183;
By one row at a time I mean it only updates one row, as you can see it updates the table using the primary ID column.
On my development machine I have restored the backup for testing purposes. The first time I run the above update statement it takes around 40 seconds, but then if I run it again it is very fast.
I am trying to find out why it is so slow the first time I run it but not the second time.
Would anyone be able to please help me?
Thanks
April 17, 2013 at 7:58 am
Lewis,
There are several different issues here. The first is caching. SQL Server caches query execution plans for queries it doesn't remember or hasn't seen before, which always causes first-time-run queries to run slower. The second is the update itself. If you have a WHERE clause limiting your updates to rows that haven't been updated, of course it will run faster the second time because it's looking at a smaller recordset.
Then we get into index issues, RBAR (what Cadavre is talking about - and he's right about this being bad), and a whole other host of possible problems that we cannot diagnose without seeing the code for the update, the DDL for the tables, and sample data to test against.
If you give us all that information, we can help you troubleshoot the query with a more accurate problem diagnosis.
April 17, 2013 at 8:01 am
Perhaps you're getting a page split? This can happen if you update a column with a variable length data type. How many indexes are there on your table?
John
April 17, 2013 at 8:20 am
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
April 17, 2013 at 8:22 am
I should also mention that both tables have full text indexes on them.
April 17, 2013 at 8:23 am
Lewis,
My first thought (without testing) is that trigger might be causing some of your problem. If you have a sandbox / dev server, try disabling the trigger then running your update statement to see if that makes a difference.
In the meantime, we'll all be diving into your code & execution plan to see what else we can find.
April 17, 2013 at 8:26 am
I'd wager that the trigger is your problem here. You've got functions within functions in it. You ought to try rewriting those functions as a very minimum. Changing scalar functions to table-value functions often brings large performance improvements.
John
April 17, 2013 at 8:50 am
/*
Missing Index Details from ExecutionPlan.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 99.8419%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [MLex_Editorial]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ContentSearchTable] ([ContentID])
GO
I'd ignore the above suggested missing index (it comes from the trigger, btw), because I fully agree with Brandie and John. Your issue is almost certainly in the trigger. If you look at the execution plan, 0.2% of the query cost is from the original update and 99.8% is from the trigger. That doesn't necessarily mean anything, but it certainly tells me that that's where you need to start looking.
I'd imagine that the biggest performance drains will be in the functions. I'd set this up in a development area (NOT PRODUCTION! :-P) and try a few things to prove it: -
1. Disable the trigger then run the update.
2. Enable the trigger, but strip out the function usage: -
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))), */ 'SomeValidDataHere',
ColumnContent25 = /*dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 25), */ 'SomeValidDataHere',
ColumnContent50 = /*dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 50), */ 'SomeValidDataHere',
ColumnContent100 = /*dbo.GetStringOnNumberOfWords(dbo.StripHTML(c.[FullText]), 100), */ 'SomeValidDataHere',
EstimatedWordCount = /*dbo.WordCount(dbo.StripHTML(FULLTEXT)), */ 'SomeValidDataHere',
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;
April 17, 2013 at 10:03 am
Hi,
Thanks all for your comments.
So I have disabled the trigger and you were right because that improved the performance. However the problem I have is that this problem is recent. The trigger never once caused this kind of delay. In fact it was always very well behaved.
Is there any advice you can provide in speeding up the trigger process?
Thank you,
Lewis
April 17, 2013 at 4:08 pm
lewisdow123 (4/17/2013)
So I have disabled the trigger and you were right because that improved the performance. However the problem I have is that this problem is recent. The trigger never once caused this kind of delay. In fact it was always very well behaved.
How do you know that? Do you have monitoring in place for the trigger?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2013 at 1:35 am
lewisdow123 (4/17/2013)
Hi,Thanks all for your comments.
So I have disabled the trigger and you were right because that improved the performance. However the problem I have is that this problem is recent. The trigger never once caused this kind of delay. In fact it was always very well behaved.
Is there any advice you can provide in speeding up the trigger process?
Thank you,
Lewis
Not without seeing the code for all of those functions, no.
April 18, 2013 at 2:18 am
Cadvre... thanks very much the issue was resolved by simply adding the index...
USE [MLex_Editorial]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ContentSearchTable] ([ContentID])
GO
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply