case expression limitations - 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 2005 Forums

Transact-SQL (2005)

case expression limitations

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2009-03-17 : 16:04:31

How many conditional whens can I use in one case expression? I need to update about 7,000 rows in a table, and each row is different:

update column
set column = x
where primary_key = 1
.
.
.
.
.
.
update column
set column = y
where primary_key = 7000

I am autogenerating these updates from the front end. It takes about 90 seconds to run all the updates, and since this is from a customer interface, 90 seconds is too long.

Is there a limit on the size of a case statement? I'd like to try generating the following case statement:

case
when primary_key = 1 then x
.
.
.
.
when primary_key = 7000 then y
end

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-17 : 16:08:58

quote:
I need to update about 7,000 rows in a table, and each row is different

Well then you're kinda stuck if they're all different. Do they follow some kind of formula or expression?

Another, more pertinent, question is: why is an end-user updating 7,000 rows at time?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-17 : 16:11:17

All good questions, Rob!

Assuming you know what each value should be why don't you create a table (or use an existing one) that has the [Primary_Key] and [DesiredColumnValue] columns. Then INNER JOIN that table to your target table and do one simple update statement:

update mt set
mt.[column] = st.[DesiredColumnValue]
from myTable mt
inner join sourceTable st on st.[primary_key] = mt.[primary_key]

Be One with the Optimizer
TG

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2009-03-18 : 10:42:34

quote:
Originally posted by TG

All good questions, Rob!

Assuming you know what each value should be why don't you create a table (or use an existing one) that has the [Primary_Key] and [DesiredColumnValue] columns. Then INNER JOIN that table to your target table and do one simple update statement:

update mt set
mt.[column] = st.[DesiredColumnValue]
from myTable mt
inner join sourceTable st on st.[primary_key] = mt.[primary_key]

Be One with the Optimizer
TG

That only adds a level of complexity - I'd still have to update myTable from the user interface.

Anyway, we've stumbled across a partial solution. By running a commit after every update statement, instead of waiting until after the batch is run, we've cut the time down to about 10 seconds. Still kind of slow, but at least within reason. We must have been writing to the page file on the disk.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-18 : 10:49:45

Where are the 7000 new values coming from? Are they stored in, or derived from, any database values? If so it should all be one statement as I suggested. I'm sure a user is not filling in 7000 values :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources