Update Efficiency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Efficiency

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 11:11:45

Which is more efficient...

Multiple set items within an update?

UPDATE [Xerxes].[dbo].[look_PLANTYPE]
SET STAMPDATE = GETDATE(),
STAMPUSER = SUSER_SNAME()

or Multiple Updates?

UPDATE [Xerxes].[dbo].[look_PLANTYPE]
SET STAMPDATE = GETDATE()

UPDATE [Xerxes].[dbo].[look_PLANTYPE]
SET STAMPUSER = SUSER_SNAME()

Granted these are small examples, but when the tables grow larger wouldn't multiple UPDATE cost more?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 11:12:47

one update, multi items.

Go with the flow & have fun! Else fight the flow

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-02-21 : 11:14:56

^^ What he said
For two updates, you've gotta find the right row to update twice, and then update it twice.

For a small example, that data will still probably be cached, so you'll not notice any gains. In a larger system that's doing a lot more transactions and has a lot more rows in that table, you probably would notice the difference.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 11:18:18

Thanks!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 11:27:39

But what if you have extended SET items that contain WHERE clauses...like:

CREATE PROCEDURE [px_Error_NULLtoBLANK] AS

UPDATE Tab_NewFile
SET [Tab_NewFile].[PAYOR] = ' ' WHERE PAYOR is NULL
,[Tab_NewFile].[MemberNum] = ' ' WHERE LEN(MemberNum) < 1 OR MemberNum is NULL
, [Tab_NewFile].[Name] = ' ' WHERE Name is NULL
, [Tab_NewFile].[SEX] = ' ' WHERE SEX is NULL
, [Tab_NewFile].[Subscr_MNo] = ' ' WHERE LEN(Subscr_MNo) < 1 OR Subscr_MNo is NULL
, [Tab_NewFile].[PCPNum] = ' ' WHERE LEN(PCPNum) < 1 or PCPNum is NULL
, [Tab_NewFile].[PCPName] = ' ' WHERE PCPName is NULL
;
GO

won't that effect UPDATE efficiency?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 11:33:42

if that update runs then i'm the pope....
of course update is conditioned by the where.
so if you want to update diffrenet columns based on different where conditions,
then you have to do a few updates instead of one.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 11:38:03

Mladen,

If you're the Pope

, I'm the President

.

That was a hastily written example. NOT a real update. I just wanted clarification on the efficiency issue. Personally, I detest Update, but sometimes there is no other way.

Thanks!

Sincerely,

President Xerxes

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-21 : 12:28:40

Quote of the week on SqlTeam:

quote:
Personally, I detest Update, but sometimes there is no other way.

That was quite funny

Xerxes if You don't like updates, then do them in batches.
That will be
1. More Efficient
2. Less to type

cheerio

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 13:46:25

Thanks, Moose!

Actually, I reaaly do wish there was a better way to modify some of my large tables. It just seems that update can be awfully cumbersome.

Plus....as anyone knows by now, I can deadpan

with the best!

Take care!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-21 : 14:03:02

use CASE

update someTable
set Col1 = CASE WHEN x > 0 THEN 0 ELSE Col1 END,
Col2 = CASE WHEN x < 0 THEN 0 ELSE Col2 END

That does two updates, with different conditions, at the same time. just update the value to itself if you don't need to change it.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 14:23:08

So you're advocating that CASE is more efficient than a WHERE within an UPDATE statement with multiple SET entries. Did I get that right?

So instead of this

CREATE PROCEDURE [px_Error_NULLtoBLANK] AS

UPDATE Tab_NewFile
SET [Tab_NewFile].[PAYOR] = ' ' WHERE PAYOR is NULL

UPDATE Tab_NewFile
SET [Tab_NewFile].[MemberNum] = ' ' WHERE LEN(MemberNum) < 1 OR MemberNum is NULL
;
UPDATE Tab_NewFile
SET [Tab_NewFile].[Name] = ' ' WHERE Name is NULL
;
UPDATE Tab_NewFile
SET [Tab_NewFile].[SEX] = ' ' WHERE SEX is NULL
;
UPDATE Tab_NewFile
SET [Tab_NewFile].[Subscr_MNo] = ' ' WHERE LEN(Subscr_MNo) < 1 OR Subscr_MNo is NULL
;
UPDATE Tab_NewFile
SET [Tab_NewFile].[PCPNum] = ' ' WHERE LEN(PCPNum) < 1 or PCPNum is NULL
;
UPDATE Tab_NewFile
SET [Tab_NewFile].[PCPName] = ' ' WHERE PCPName is NULL
;

I should pursue this?

CREATE PROCEDURE [px_Error_NULLtoBLANK] AS

UPDATE Tab_NewFile
SET
[Tab_NewFile].[PAYOR] =
CASE WHEN [PAYOR] is NULL THEN [PAYOR] = ' ' END

, [Tab_NewFile].[MemberNum] =
CASE WHEN LEN(MemberNum) < 1 THEN [MemberNum ] = ' ' END

, [Tab_NewFile].[MemberNum] =
CASE WHEN [MemberNum] is NULL THEN [MemberNum ] = ' ' END

, [Tab_NewFile].[Name] =
CASE WHEN [Name] is NULL THEN [Name] = ' ' END

, [Tab_NewFile].[SEX] =
CASE WHEN [SEX] is NULL THEN [SEX] = ' ' END

, [Tab_NewFile].[ Subscr_MNo] =
CASE WHEN LEN(Subscr_MNo ) < 1 THEN [Subscr_MNo] = ' ' END

, [Tab_NewFile].[ Subscr_MNo] =
CASE WHEN [Subscr_Mno] is NULL THEN [Subscr_MNo] = ' ' END

, [Tab_NewFile].[ PCPNum] =
CASE WHEN LEN(PCPNum) < 1 THEN [PCPNum] = ' ' END

, [Tab_NewFile].[ PCPNum] =
CASE WHEN [PCPNum ] is NULL THEN [PCPNum] = ' ' END

, [Tab_NewFile].[PCPName] =
CASE WHEN [PCPName] is NULL THEN [PCPName] = ' ' END

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-21 : 14:37:24

close. Your syntax is wrong and you must put an ELSE in to each of those case statments to update the value to it's original value if the case is FALSE. Otherwise, they will be set to NULL.

Make sure you are 100% sure of how CASE works -- it is a FUNCTION that always simply returns a value, and w/o an ELSE clause, if the condition is FALSE it will return NULL. As you posted your statement, it is not legal. As I often say, create a small table of sample data and experiment and make sure everything works exactly as you expect.

It all depends on how many conditions you are updating and what you are doing, as well. Some conditions you will want in a WHERE clause, others when calculating what values to set the various columns to.

To be honest, as posted, I have no idea why are you trying to run an update of that type. Why are you replacing NULLS with empty strings? Why not leave them as Nulls? You are making things much more complicated on yourself by trying to do this most likely.

Also read up on the ISNULL() and COALESCE() functions, they do what you want much easier, w/o a CASE at all.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-21 : 14:53:42

Jeff,

The '

code' was simply a rough sketch of what you had advised. I listed the way I generally employ an UPDATE statement. The SET items were fabricated to represent significantly more complex instances than are described. I guess using 1s and 0s would have been preferable to using NULLS and ' ' as examples which was what I concocted on the fly

.

I had considered CASE vice WHERE, but was looking for a less cumbersome process; I'm simply looking into the best and most efficient use of UPDATE (which was my topic) to handle my huge tables.

Thanks for the suggestion regarding COALESCE, I'll check it out.

Thanks

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-21 : 21:29:29

Following Jeff's advice regarding IsNull and COALESCE, your code becomes:

CREATE PROCEDURE [px_Error_NULLtoBLANK] AS
UPDATE Tab_NewFile
SET PAYOR = IsNull(Payor,' '), MemberNum = IsNull(MemberNum,' '), Name = IsNull(Name,' '), SEX = IsNull(Sex, ' '),
Subscr_MNo = IsNull(Subscr_Mno, ' '), PCPNum = IsNull(PCPNum, ' '), PCPName = IsNull(PCPName , ' ')

One UPDATE statement, all columns covered. Can't get more efficient than that.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 10:25:48

quote:
Originally posted by robvolk

Following Jeff's advice regarding IsNull and COALESCE, your code becomes:

CREATE PROCEDURE [px_Error_NULLtoBLANK] AS
UPDATE Tab_NewFile
SET PAYOR = IsNull(Payor,' '), MemberNum = IsNull(MemberNum,' '), Name = IsNull(Name,' '), SEX = IsNull(Sex, ' '),
Subscr_MNo = IsNull(Subscr_Mno, ' '), PCPNum = IsNull(PCPNum, ' '), PCPName = IsNull(PCPName , ' ')

One UPDATE statement, all columns covered. Can't get more efficient than that.

Rob,

Did anyone ever tell you that you're a genius

? Well I'm telling you

! Thanks a million! (That's how many hours you saved me playing with COALESCE!

)

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-22 : 11:19:39

FYI, in this instance, IsNull can be replaced with Coalesce and it will provide the same results. IsNull only works with two arguments, Coalesce can use more.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources