Update statement really slow but only on the first execution

  • 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

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    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/

  • 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

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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,

    [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

  • I should also mention that both tables have full text indexes on them.

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • /*

    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;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    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/

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    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/

  • 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