Need help with a slow update - 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)

Need help with a slow update

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2009-02-09 : 13:01:35

Hi folks,

I've got a table full of "items" called ITM_ITEM. There is an identity column (Rec_Num) that serves as the PK and is used in all FKs. There is also a unique value called Item_Number that is displayed to the user, and is used in sorting. When a new item is inserted between two others, the Item_Number values need to be updated so as to remain unique and sequential.

For example, we might have the following five items:


Rec_Num  Item_Number  Name
=======  ===========  ====
154      1            Item A
9945     2            Item B
406      3            Item C
2112     4            Item D
63       5            Item E

When I insert a new item between Item B and Item C, I want to end up with...


Rec_Num  Item_Number  Name
=======  ===========  ====
154      1            Item A
9945     2            Item B
11998    3            NEW ITEM
406      4            Item C
2112     5            Item D
63       6            Item E

So I have to increment the Item_Number of Item C and every record whose Item_Number follows it. The problem is, this table usually contains over 100,000 items, Causing this update to take almost five seconds, which is a considerable amount of time to insert a record. (Our legacy app, which ran off an Access back-end, did it in just under one second.)

So my question is, what adjustments can I make to speed the update across this many records? Currently I'm doing the following:


UPDATE ITM_ITEM
SET Item_Number = Item_Number + 1
WHERE Item_Number >= @Target;

FYI: There is a unique, non-clustered index on the Item_Number field. No triggers are firing.

Thanks
Ron Moses

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-09 : 13:25:26

IMO updating (potentially) the entire table when a row is inserted just to maintain an order is not a good design.

Since [Item_Number] can change I will assume that your users are not treating this as an identifier but rather as a row number. So all this is really being used for is to SORT by, correct? And this order cannot be derived by any data other than Item_Number?

If that is so then assuming you had a column to sort by could you use ROW_NUMBER() as [Item_Number] to present to the user? Perhaps you can use a decimal datatype to sort by - that way you could have 10s of thousands of values that could be inserted between any two existing values without needing to update other rows.

Be One with the Optimizer
TG

ron2112
Starting Member

44 Posts

Posted - 2009-02-09 : 13:56:29

quote:
IMO updating (potentially) the entire table when a row is inserted just to maintain an order is not a good design.

I agree. Unfortunately I'm not at liberty to change this aspect of the design. I'm required to emulate the behavior of their legacy application as closely as possible.

quote:
Since [Item_Number] can change I will assume that your users are not treating this as an identifier but rather as a row number.

You would think so, but strangely enough, they actually do use it as an identifier. Even though it can change.

quote:
So all this is really being used for is to SORT by, correct?

That is all the app uses it for.

quote:
And this order cannot be derived by any data other than Item_Number?

There is no other column that can be used for sorting, no.

I've received some further information from the previous developer that might shed some light on this. I'm going to go through his notes and hopefully find something useful.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-09 : 14:39:06

I believe my suggestion will still leave the application behaving exactly as it was. It would still return this:


Rec_Num  Item_Number  Name
=======  ===========  ====
154      1            Item A
9945     2            Item B
11998    3            NEW ITEM
406      4            Item C
2112     5            Item D
63       6            Item E

but Item_number would be the result of a ROW_NUMBER() fuction

and the ORDER BY will be based on a new column like:


sortCol
=======
1.000000
2.000000
3.000000
3.500000
4.000000
5.000000

So INSERTs will not need to update other rows. You would just need to derive the sortCol value based on values that are before and after the new position.

Be One with the Optimizer
TG

ron2112
Starting Member

44 Posts

Posted - 2009-02-09 : 14:43:37

I'll give that a try, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources