August 28, 2006 at 2:05 pm
I have a simple update statement that set sthe student's password to their id number:
update studemo set passwd = ident
ident is not part of the primary key but there is a unique, non-clustered index using that column
This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?
August 28, 2006 at 3:29 pm
I think Query is perfect ( I assume passwd as char type and ident as integer)
Tested with the same scenario explained by you, my machine is taking less than minute to update 25K records
I think there will be some external problem rather than in query
August 28, 2006 at 3:43 pm
Both are varchar (passwd is varchar(11) & ident is varchar (10)). Can you explain how I can monitor this and see where the bottle neck is located?
August 28, 2006 at 3:44 pm
Do you have any foreign kaeys that reference this table, are they indexed correctly. Can you paste the update into another instance of QA and display the estimated execution plan and see where it is doing most of its work. Are there any triggers on or referencing this table.
August 28, 2006 at 4:09 pm
One trigger does fire (9tr_slvswstuU fired, no change to address fields). I see this when I limit the update to only 1600 row (16 sec execution). There is one FK but it's not either of the columns I'm using.
Execution plan: Index Scan (suniq, ident) cost 2%, compute scalar 0%, top cost 0%, cluster index update (primary key) cost 98%, update cost 0%
I have test in two differnt instances on SQL Server with the same results.
August 28, 2006 at 4:18 pm
most of the time is spent clustered index update (98%)
If you want to make the updates work fast ..
delete the clustered index . run the update query then create clustered index on the table
August 28, 2006 at 5:40 pm
25k rows is not much. You shouldnt have to delete indexes and re-create for that count. You could however disable triggers and see if it helps.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply