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 12»»

Update statement really slow but only on the first execution Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 7:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:34 AM
Points: 26, Visits: 110
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
Post #1443258
Posted Wednesday, April 17, 2013 7:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:35 AM
Points: 2,386, Visits: 7,610
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. It'd make it much easier to point you in the right direction, or perhaps even suggest improvements.

Thanks.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1443261
Posted Wednesday, April 17, 2013 7:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:34 AM
Points: 26, Visits: 110
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
Post #1443269
Posted Wednesday, April 17, 2013 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 5,615, Visits: 6,413
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1443272
Posted Wednesday, April 17, 2013 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1443275
Posted Wednesday, April 17, 2013 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:34 AM
Points: 26, Visits: 110
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,
[URL] [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




  Post Attachments 
ExecutionPlan.sqlplan (4 views, 218.91 KB)
Post #1443291
Posted Wednesday, April 17, 2013 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:34 AM
Points: 26, Visits: 110
I should also mention that both tables have full text indexes on them.

Post #1443294
Posted Wednesday, April 17, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 5,615, Visits: 6,413
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1443296
Posted Wednesday, April 17, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1443299
Posted Wednesday, April 17, 2013 8:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:35 AM
Points: 2,386, Visits: 7,610
/*
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! ) 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;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1443317
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse