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 |
|
cbeganesh
Posting Yak Master
105 Posts |
 Posted - 2008-11-18 : 13:09:38
|
| hii have 2 tables test1 and test2. test1 has 40 million rows and test2 has 70 milion rows. i want to update one flag in test1 based on accountnumber. i have index on accountnumber on both tables. the follwing query is taking long time. is there any way i can improve the performance of the update.update test1 set matchtype = 1from test1 a, test2 bwhere a.acno = b.acno |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-18 : 13:43:23
|
| did you have a look at execution plan to identify costly step? |
 |
|
|
cbeganesh
Posting Yak Master
105 Posts |
Posted - 2008-11-18 : 14:43:50
|
| i created clustered index on both tables on account tables. when i see the query plan it was still using the other indexes. I dont know why it does not use the clustered index, so i dropped the other index on both tables and now it uses the clusterted index. may be this will improve the performance, any suggestions |
|
|
|
Ceemack
Starting Member
3 Posts |
Posted - 2008-11-18 : 16:53:26
|
| A clustered index generally isn't useful in a join, if the values one which you're joining (like account number) are unique in each table. A clustered index helps when selecting a range of values (say, order entry date), or a bunch of recods with identical values (for example, an order detail table with a clustered index on order number, so the detail records for a particular order number are all together). A join looks at discrete values, so a clustered index has no advantage over a non-clustered index. In fact, a non-clustered index would probably be faster--which would be why the query optimizer is using other indexes. I would make sure there was a non-clustered index on "acno" in both tables. If the number of matches is relatively small, you could try creating a temp table to hold account numbers, and populate it by doing a select on account number with an inner join between both tables. In other words, create a temp table that holds only the account numbers that match. Then join to the temp table instead of test2 when you do your update. |
|
|
|
|
|
|
|
|
update statement takes long time