|
Home > Archive > MS SQL Server > November 2005 > massive update on a table of user
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
massive update on a table of user
|
|
| hongo32 2005-11-10, 8:23 pm |
| hi, i'm train to find the best way to write a sp that update a table in
a transactional system.... the table is goin to be consulted many time
for second... while this happens, I need to update a lot of rows of the
table... please someone can tell me whats the best way to do this?
| |
| David Gugick 2005-11-10, 8:23 pm |
| hongo32 wrote:
> hi, i'm train to find the best way to write a sp that update a table
> in a transactional system.... the table is goin to be consulted many
> time for second... while this happens, I need to update a lot of rows
> of the table... please someone can tell me whats the best way to do
> this?
While you are updating rows, SQL Server will put exclusive locks on the
affected pages. If you need to perform massive updates while other users
are accessing the same table, you have a few choices:
1- Perform the update in small transactional batches of, say,
1,000 -10,000 rows. Try and keep the row updates in clustered index key
order to minimze updating pages all over the table. The problem with
this approach is that if one of the updates fails, you cannot roll back
the entire set of updates because you are batching them. But it will
keep your transaction log from possibly auto-growing and it will limit
blocking problems by keeping the transactions short.
2- Users could query the table using the READ UNCOMMITTED isolation
level (or NOLOCK table hint). This will prevent those queries from
acknowledging the exclusive locks and allow those users to read the
uncommitted data. The problem here is that if the user reads uncommitted
data and the update routine then rolls back a transaction, the user ends
up with dirty data that no longer really exists in the table.
3- For SQL Server 2005, you could use a new isolation level called
SNAPSHOT ISOLATION which uses tempdb to log original row data so users
can read information that is currently locked because of DML operations
by other users. The issue here is that it places additional burden on
tempdb and may require tempdb be managed by its own disk array to
prevent disk related performance issues.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
| hongo32 2005-11-10, 8:23 pm |
| thanks David, i can create a temporary table that stores the rows that
fulfill a criterion and later update top 1000.... wait... update next
1000 is this more efficient? that update all the records in one time?
| |
| David Gugick 2005-11-10, 8:23 pm |
| hongo32 wrote:
> thanks David, i can create a temporary table that stores the rows that
> fulfill a criterion and later update top 1000.... wait... update next
> 1000 is this more efficient? that update all the records in one time?
I don't think a temp table is the way to go, given the size you are
likely dealing with. Maybe you can explain the current process now.
Normally, it's something as simple as "Update dbo.MyTable Set MyCol =
MyCol + 1" that can be changed to be contained in a looping structure
with a transaction / commit and adding a WHERE clause to limit the rows
updated each iteration.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
| beginthreadex 2005-11-10, 8:23 pm |
| Here are some options for your review:
DTS
BCP
enterprise manager use the import process
use a temp table then insert from temp table
bulk import
insert xml
> hi, i'm train to find the best way to write a sp that update a table in
> a transactional system.... the table is goin to be consulted many time
> for second... while this happens, I need to update a lot of rows of the
> table... please someone can tell me whats the best way to do this?
| |
| hongo32 2005-11-11, 9:23 am |
| Hi David, thats the problem... really, i don't know how to make a
looping structure with a transaction / commit and limit the rows
updated in each iteration... that's why a think that the solution is
the temporary table.
i have a table user and a table profiles... the user can have many
profiles... the user select a profile and say: "update the fieldX of
the table user whose profiles is X".... the solution that i think is
create a temporary table that holds all the user id that match in the
filter and later in a loop take 1000 update... make a wait... update
other 1000...
exist another form to do this?... very thanks David
| |
| hongo32 2005-11-11, 9:23 am |
| Hi beginthreadex, the updates is going to do in a web based application
| |
| David Gugick 2005-11-11, 9:23 am |
| hongo32 wrote:
> Hi David, thats the problem... really, i don't know how to make a
> looping structure with a transaction / commit and limit the rows
> updated in each iteration... that's why a think that the solution is
> the temporary table.
>
> i have a table user and a table profiles... the user can have many
> profiles... the user select a profile and say: "update the fieldX of
> the table user whose profiles is X".... the solution that i think is
> create a temporary table that holds all the user id that match in the
> filter and later in a loop take 1000 update... make a wait... update
> other 1000...
>
> exist another form to do this?... very thanks David
You don't have to "make a wait", which I assume you mean pause
execution. Having separate transactions does this for you. As far as not
using a temp table and not really knowing any detail about your tables
and update requirements, you could possibly use something like this
(assuming the proper indexes are in place and no table scans / clustered
index scans occur). Also, I would like to no more detail about what a
"massive update" is according to your data. What percentage and how many
rows are we dealing with? The more details you provide, the better the
answers will be.
Set @RowCount = 1
Set Rowcount 10000
While @RowCount > 0
Begin
Begin Tran
Update dbo.MyTable Set MyCol = @NewValue Where MyProfiler =
@MyProfile and MyCol <> @NewValue and MyUser = @MyUser
Set @RowCount = @@ROWCOUNT
Commit Tran
End
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
| hongo32 2005-11-11, 11:23 am |
| thanks david (again), david, i don't know if i have the proper index, I
always worry to standardize the data, and the indices that handling are
the primary keys... nothing else.
i need update 3000 rows of a tabla of 5000 rows.... but exists another
process that are always read information of that table for validate
data of users in terminals... and the idea is that the update affects
the users.... for example... to increase the gain in each sale...
the structure of the tables is:
user
--------------------
user_id char (PK non clustered)
network_id char (PK non clustered)
(name, address, telephone)
atributes of user: seller, payer, status (active, inactive)
and:
user_criteria
--------------------
criteria_id int (PK non clustered)
user_id char (PK non clustered) (FK to user non clustered)
network_id char (PK non clustered) (FK to user non clustered)
value.
i need to change the user_criteria.value field or user.seller , payer,
status and other fields of user table. do you think that i should
change the pk index of user table for clustered index?
nonwise I who attribute rowcount works in updates .... thanks....
| |
| David Gugick 2005-11-11, 11:23 am |
| hongo32 wrote:
> thanks david (again), david, i don't know if i have the proper index,
> I always worry to standardize the data, and the indices that handling
> are the primary keys... nothing else.
>
> i need update 3000 rows of a tabla of 5000 rows.... but exists another
> process that are always read information of that table for validate
> data of users in terminals... and the idea is that the update affects
> the users.... for example... to increase the gain in each sale...
>
> the structure of the tables is:
>
> user
> --------------------
> user_id char (PK non clustered)
> network_id char (PK non clustered)
> (name, address, telephone)
> atributes of user: seller, payer, status (active, inactive)
>
> and:
>
> user_criteria
> --------------------
> criteria_id int (PK non clustered)
> user_id char (PK non clustered) (FK to user non clustered)
> network_id char (PK non clustered) (FK to user non clustered)
> value.
>
> i need to change the user_criteria.value field or user.seller , payer,
> status and other fields of user table. do you think that i should
> change the pk index of user table for clustered index?
>
> nonwise I who attribute rowcount works in updates .... thanks....
I think, based on what I see, that both PK constraints should be
clustered, but based on your update it still may not help. What has your
testing shown in terms of performance? I think you are going to lock the
entire table during the update based on the percentage of rows you are
updating. It still may happen fast, but could also cause deadlock
situations with users reading from that table. Try to update in batches
if you are concerned.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
|
|
|
|
|