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.
| Author |
Topic |
|
socnob
Starting Member
10 Posts |
 Posted - 2007-03-09 : 16:51:57
|
| This is similar to the previous topic named "Can I improve this?".Situation/Goal:I have around 1million contact records in a table called "contact". This table is plagued with duplicate entries (i.e. the same person was recorded/created in 2 or more records). These contact records link to other tables. I am trying to update the "contact" table and all tables that link to the "contact" table such that duplicates are deleted (after pertinent info. is copied if need be) and those records in other tables that are linking to the duplicate "contact" records are updated to link to only one "contact" record. Issue:The issue is that this is taking a really long time (weeks). My guess is the reason for the extreme time is due to the large quantity of records (1 million in the "contact" and about 1.5 million records in at least one of the linked tables), but more specifically with all the indexes that need to be updated for each table.Tables:The "contact" table is defined as:================ link (int, Not Null) - primary key fname (char(30)) lname (char(50)) iname (char(1)) cont_id (char(15), Not Null) email (char(100), Not Null) phone (char(14), Not Null) city (char(30), Not Null) state (char(2), Not Null) zip (char(10), Not Null) etc....================One of the other tables that link to the "contact" table is "contInter" (contact interaction) defined as:================ link (int, Not Null) - primary key date (datetime(8)) subject (char(50)) notes: (varchar(255)) l_contact (int, Not Null) - link to the contact record================When I run "Display Estimate Execution Plan" for an update similar to:===========UPDATE contInter set l_contact = @keepLinkWHERE l_contact = @dupLink===========the biggest cost looks to be associated with the "Table Spool/Eager Spools" (during index updates it looks like). The contInter table has around 1.5Million records.Questions:1.) What exactly is a "Table/Eager Spool"?2.) How is a "Table/Eager Spool" used in an Update/Delete?3.) Is there a way speed up "Updates" or speed up "Table/Eager Spools" (some command to help the indexes get updated quicker maybe?)?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-09 : 17:04:31
|
| In order to help you improve it, we will need to see your entire script.Tara Kizer |
 |
|
|
socnob
Starting Member
10 Posts |
Posted - 2007-03-15 : 19:45:59
|
| Ok. In the mean time, could someone answer those 3 questions I had posted in my original entry?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Speed up Updates and Deletes?