Post reply

Update Query Issue

  • December 27, 2008 at 12:46 am

    #197966

    I Have A Simple Update Query But It Takes A Lot Of Time,How Can I Reduce Its Time ?

    I Have A Table With 10 Lakh Records.

    I Want To Update A Field With A Fix Value,But It Takes About 5 Mins For The Same. Please Help Me.

  • December 27, 2008 at 1:28 am

    #917984

    ok..be more specific post the table structure and some sample data ... and someone will help you on it!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • December 27, 2008 at 7:05 am

    #918018

    Also, the query that u're using to update plus index structures on your base table.


    Pradeep Singh

  • December 27, 2008 at 10:46 am

    #918050

    Jay Sapani (12/27/2008)

    I Have A Simple Update Query But It Takes A Lot Of Time,How Can I Reduce Its Time ?

    I Have A Table With 10 Lakh Records.

    I Want To Update A Field With A Fix Value,But It Takes About 5 Mins For The Same. Please Help Me.

    That should take something less than 7 seconds if done correctly. The killer is going to be...

    1. Is the column you're updating part of the clustered index (usually, the PK by default)? You may be causing rampant page splits if so.

    2. Is the column you're updating part of other indexes? That will slow things down, as well.

    3. Do you have triggers on the table being updated? Those can be a huge drain on performance if they're not correctly written and will cause the code to run about twice as slow even if they are.

    4. Do you have DRI ("Declared Refererential Integrity", also known as Foreign Keys) in place on the column you're updating? That will also provide a bit of a slowdown.

    5. Does the column have a UNIQUE index on it? That will also provide a bit of a slowdown.

    6. What kind of constraints do you have on the column you're updating? A boat load of constraints on the column you're updating will also provide a bit of a slowdown.

    7. Are there any indexed views that use the column you're updating? If so, that will also provide a bit of a slowdown.

    8. Is the update query you're using updating the same column it uses for criteria? Depending on how parallelism is spawned, that can sometimes cause a recompile of the update query for every row updated. It's a very rare thing, but it sometimes happens.

    I'm probably forgetting something, but those are what I'd check first... 😉

    If you want more help than that, you' need to post the Update query, the create statement for the table, all it's indexes/keys, and the code for any related triggers or indexed view and any indexes there may be on those views.

    --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)
    Intro to Tally Tables and Functions

  • December 27, 2008 at 11:10 am

    #918053

    Next time i come up with a problem like that i'd rather use a checklist of all the points mentioned by Jeff and then try to strike a balance...

    All major root causes of slowness of a query mentioned there:)


    Pradeep Singh

  • December 27, 2008 at 11:34 am

    #918061

    Oh yeah... I knew I was forgetting something...

    You're trying to update the whole table, right? Instead of pussy footing around with row locks changing to page lock changing to extent locks and finally a tab lock, add the hint WITH (TABLOCKX) to the query... it may take a second or two for other people's locks to drop off, but once they do, the update should run very fast because once you've established that tab lock, there's no chance of anyone blocking your update. If it works correctly as I stated before, no one should be locked out for more than about 7 seconds. Run through my previous checklist if they are.

    --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)
    Intro to Tally Tables and Functions

  • December 28, 2008 at 9:44 pm

    #918244

    SSChampion I Got The Point The Field I Am Updating Does Have Index On It.

  • December 28, 2008 at 9:52 pm

    #918248

    Here Is My Table -

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[MyTemp]

    GO

    CREATE TABLE [dbo].[MyTemp] (

    [TRKEY] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRDIVCOD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRBOKCOD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRCURTP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRCURNO] [float] NULL ,

    [TRSRNO] [float] NULL ,

    [TRBSCOD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRCURDT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRSUBTP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRPRDCOD] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRPTYCOD] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRPRDNAM] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRPRDQTY] [float] NULL ,

    [TRPRDRAT] [float] NULL ,

    [TRPTYAMT] [float] NULL ,

    [TRBROK] [float] NULL ,

    [TRDRCRCD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRREFTP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRREFNO] [float] NULL ,

    [TRCHLNO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRCHLDT] [datetime] NULL ,

    [TRVALNO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TREXPVLNO] [float] NULL ,

    [TREXPDAY] [float] NULL ,

    [TREXPDT] [datetime] NULL ,

    [TRSUBBRK] [float] NULL ,

    [TREXCHNG] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRSEGMNT] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRINSTYP] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRSCPSYM] [nchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TROPTTYP] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRSTRRAT] [float] NULL ,

    [TRLOCID] [nchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRMKTLOT] [float] NULL ,

    [TRMKTUM] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRMKTRTFOR] [nchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRMKTMP] [float] NULL ,

    [TRSTAT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRADJST] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [id] [int] NULL ,

    [dbffilename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRUPLOAD] [tinyint] NULL ,

    [TRUPLOADED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [TRMYPARTY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[MyTemp] ADD

    CONSTRAINT [DF_MyTemp_TRUPLOAD] DEFAULT (0) FOR [TRUPLOAD]

    GO

    CREATE INDEX [IX_MyTemp_1] ON [dbo].[MyTemp]([TRKEY]) ON [PRIMARY]

    GO

    CREATE INDEX [IX_MyTemp_2] ON [dbo].[MyTemp]([TRUPLOADED]) ON [PRIMARY]

    GO

    CREATE INDEX [IX_MyTemp_3] ON [dbo].[MyTemp]([TRUPLOAD]) ON [PRIMARY]

    GO

    CREATE INDEX [IX_MyTemp] ON [dbo].[MyTemp]([dbffilename]) ON [PRIMARY]

    GO

    The Fields I Am Trying To Update Are -TrKey,TrUpload,DbfFileName,TrExchng

  • December 29, 2008 at 5:05 am

    #918311

    Ok... there's a couple of problems with that... first, you're updating 3 of the 4 indexes on the table. Like I said, that's gonna slow things down quite a bit.

    The other problem is that there's no clustered index on the table which makes it a heap. There's also no primary key, so behind the scenes, it has to generate internal row numbers in order to be able to find each row.

    And, when is the last time the indexes were defragged?

    --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)
    Intro to Tally Tables and Functions

  • December 29, 2008 at 9:42 pm

    #918747

    My table Is A Temp Table.After Updating the Fields,I Check The Data With Main Table And Then Delete Duplicate Data From The Temp Table.Transfer New Data From Temp To Main And Then I Truncate The Temp Table.Please Give Me U R Suggestion As What Should I Do To Make It Fast.

  • December 29, 2008 at 11:30 pm

    #918768

    With the limited information I have from you, the best I can do is what I've already done. Perhaps it's time for you to post the actual update query.

    --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)
    Intro to Tally Tables and Functions

  • December 30, 2008 at 9:41 pm

    #919429

    SSChampion The Update Quries Are

    1. Update MyTemp Set DbfFileName = '2005.Dbf' ,TrExchng = 51

    2. Update MyTemp Set MyKey = Str(TrExchng,2) + TrValNo + TrPtyCod + TrCurDt + TrPrdCod

    These Are The Quries Which Take A Lot Of Time

  • December 30, 2008 at 10:01 pm

    #919434

    Jay Sapani (12/30/2008)

    SSChampion The Update Quries Are

    1. Update MyTemp Set DbfFileName = '2005.Dbf' ,TrExchng = 51

    2. Update MyTemp Set MyKey = Str(TrExchng,2) + TrValNo + TrPtyCod + TrCurDt + TrPrdCod

    These Are The Quries Which Take A Lot Of Time

    Ok... I'm starting to feel like you're pulling my chain... :pinch:

    First, I think you mean "TrKey" instead of "MyKey". Second, the first update updates [font="Arial Black"]all [/font]10 lakhs (1 Million rows) in the table with constants... what makes you think an index on those columns is going to do you or your queries any good at all? :blink:

    --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)
    Intro to Tally Tables and Functions

  • December 31, 2008 at 12:11 am

    #919463

    I Use The Index On The MyKey Or TrKey Field And DbfFileName Field For Comparing It With My Main Table (Which Has Same Structure As Temp)

    For Finding Data In Temp Which Is Present In Main.

    I Then Mark The New Data In Temp And Transfer The Same From Temp To Main

    The Only Change In Main Table Is That MyKey Or TrKey Field Is A Primary Key Field

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply