Update Performance

Question

Update Performance

rob jones-315277

SSC Journeyman

Points: 91
More actions
January 16, 2009 at 8:24 am

#127057

In short, i have a customers table that is fed by an interface. There could be possibly 3000+ updates to this table each working day, each update is a single update and they arent done in bulk as all update are done in 'real time'
Currently, i select top 1 to see if the customer exists to decide if its an insert or an update.
However somone has suggested running it as an update statment and then depending on whats returned i can A) Do nothing as the record was there and is updated. B)Run an insert statement as the record doesnt exist.
Which method is better, or is it swings and roundabouts?
Regards
Rob

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Update, and if @@Rowcount = 0, then insert. That's more efficient.

The difference is that, if it's an update, you only access the data once that way, but twice if you check for existence first.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

rob jones-315277 SSC Journeyman Points: 91 More actions · Answer 2

Currently i have 'set nocount on'.

Will i have to remove that in the "Update, and if @@Rowcount = 0, then insert. " method so it returns a rowcount?

If so isn't each method then doing the same ammount of process's?

Thanks for your help

regards

Rob

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 3

Like the way you're thinking Rob, but @@RowCount isn't that resource intensive and it's probably set regardless whether you query it or not.

Could always do a dirty read (NOLOCK) for the client first, then it wouldn't matter that much, it depends on the resources available.

UPDATE() could also be of assistance in terms of raising an error if an update fails (therefore implying an insert - please be careful in not ignoring valid errors like constraint violations). An INSTEAD OF trigger could possibly provide 'the forum' to enable you to determine if you need to insert or update.

Could you put all the client updates\inserts into a holding table and at the end of the day do a bulk insert or update, once again that depends on the scenario and availability of data.

Max

rob jones-315277 SSC Journeyman Points: 91 More actions · Answer 4

Thanks for the Reply,

There are prob 10+ systems that feed the main database, rather that users going to lots of different systems we try and pull all the info into a 'data repository' which need the data real time. The database is also heavily used for our own in house web applications.

We do use (nolock) on some of our sp's but because of the importance of this data and ensuring that whats returned is 100% correct.

Regards

Rob

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

rob jones (1/16/2009)
Currently i have 'set nocount on'.
Will i have to remove that in the "Update, and if @@Rowcount = 0, then insert. " method so it returns a rowcount?
If so isn't each method then doing the same ammount of process's?
Thanks for your help
regards
Rob

No... SET NOCOUNT ON has no effect over @@RowCount... all SET NOCOUNT ON does is prevent the display/return of rowcounts to whatever output device or result set path that may be available during the 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)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

One additional thought... people always seem to fall into the trap of saying "this will never be for more than 1 row". You don't actually know how someone else may use your code or for what. One of the secrets to preventing performance problems is to write all code as if it were going to process many rows. All such code will also handle a single row just fine. Yes, yes, I understand that you're going to pass parameters for just one customer... that shouldn't change the way you write the code to do the UPSERT that you're talking about.

--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)

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 7

Np, that's a busy set-up that you have there, have you considered (don't let your eyes glaze over) replication or some sort of pipe to a sub-repository that'll manage the UPDATE\INSERT conundrum to your repository?

Prudent call on the dirty data dealings but sooner or later you're going to hit a ceiling. The only other suggestion is to double check the index and constraints to make sure they're not inhibiting performance, 3000+ "UPSERTS" a day shouldn't be causing massive performance issues.

Just thought, have you checked if there's a performance gain in using IF EXISTS( SELECT blah FROM tblBlah WHERE Client_ID\Name = [@Client_ID\Name]) THEN UPDATE... ELSE INSERT...

Max

GSquared SSC Guru Points: 260824 More actions · Answer 8

Max (1/16/2009)
Just thought, have you checked if there's a performance gain in using IF EXISTS( SELECT blah FROM tblBlah WHERE Client_ID\Name = [@Client_ID\Name]) THEN UPDATE... ELSE INSERT...

The problem with that is it automatically has to access the data twice to do an update. It's actually slower (in my tests and tests by others) than checking @@Rowcount after an update, and doing an insert if it doesn't get any rows.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 9

An update would be quicker checking @@rowcount. Ultimately, 2 io's to insert a record vs. 3 to check first also doesn't sound like a bad deal. Regardless, my questions were focussed on the reasons for the performance issues given the number of transactions.

Thank heavens for MERGE.

Max

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

I just inherited a couple of databases that use a huge amount of replication. Heh... try making a table change or copying a database to Dev sometime without disturbing the replication.

--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)

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 11

Yip, I remember a while back having to look into a situation where transactional replication was causing some performance issues exacerbated by the server being used for some production processing. Sadly, it's often the situation and resources that influence the solution. Hopefully the MERGE transact statement will assist people in Rob's situation in the future. Just need to get my hands on a 2008 server, rofl! - it really is funny, really - maybe in 2 years...

Max

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 12

Hi

I will go with first "INSERT THE DATA" and then based on the results I will issue "UPDATE". because Insert is always faster compared to UPDATE.

Thanks -- Vj

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers