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 |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2007-08-06 : 23:15:28
|
| Guys, I really need your expert advice.I have one table which holds 1 million records, every that tables get updated 3-5 TIMES with Bulk insertion process.Normally 1000 times this table used WITHIN SELECT.Property tableField1 (varchar) (Primary Key) Clustered IndexField2 (Varchar) (Primary Key) Clustered IndexFeild3Feild4I have created a Covering index (Field1,Field2) to get rid of Table scan on clustered indexes.Now Here are problemsI have two servers, one is test and one is production. I created linked server for test into Production server, BOTH SERVERS HAS SAME TABLE. Select Query works fines in resonable amount of time.Now when I am trying to delete some rows from Test server (Linked) using folloiwng queryDelete from TestServer(Linked).testcoredata.dbo.property Where roundid in (select roundid from cur_round where rhd = @mRHD AND CODE = Code)It performs Remote scan with CPU cost over 50%, whihc some time took over 5-6 minutes.Any idea how to decrease that time?Thanks in advance.SKR |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-06 : 23:36:22
|
| Is roundid part of clustered index? |
 |
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2007-08-07 : 00:20:10
|
| Sorry I should have given complete info.yes it is clustered at both ends with covering index.ALSO fIELD2 in Round table is Clustered index with covering index.Delete from TestServer(Linked).testcoredata.dbo.property Where field2 in (select field2 from round (Production tbale) where rhd = @mRHD AND CODE ode)Thanks in advanceSKR |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-07 : 13:13:22
|
| I think it's because optimizer doesn't know how many rows involved in this case. |
|
|
|
|
|
|
Linked Server and Remote Scan