Update Query Issue

Question

Update Query Issue

Jay Sapani

Say Hey Kid

Points: 683
More actions
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.

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

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

Dugi SSCoach Points: 17998 More actions · Answer 1

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]

ps. One Orange Chip Points: 29423 More actions · Answer 2

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

Pradeep Singh

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

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)

ps. One Orange Chip Points: 29423 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

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)

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 6

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

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

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)

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

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)

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 12

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)

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 13

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