• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

ms sql Update date a table scanning all records

I have 2 tables.

t1 has 30,000,000 records
t2 has 20,000 records

I do this

update table1 set fieldx=1 from table2 where t1.email=t2.email

t2 is indexed on email
t1 is indexed on email

I realize that the process has to scan the 30,000,000 records to get the matches

I am sure there must be a way to run a query where I scan the 20,000 records of t2 and update t1. Which would be much faster.

In Foxpro I would run al loop for each record in t2 find if the record was in t1 and update it.

Any ideas?
0
robrodp
Asked:
robrodp
  • 3
  • 3
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
update table1 set fieldx=1 from table2 where t1.email=t2.email

t2 is indexed on email
t1 is indexed on email

I realize that the process has to scan the 30,000,000 records to get the matches
No, it'll use the index to only process the records matching.

You may check the execution plan in SSMS, but I'm quite confident it'll not show a table scan on t1.

Bye, Olaf.

PS: As you don't limit the query on t2, the execution plan will have a table scan on that, maybe also use the email index to get all email values only.
0
 
robrodpAuthor Commented:
Attached find the execution plan
sql.png
0
 
Olaf DoschkeSoftware DeveloperCommented:
Well, on the right hand side you see both indexes are used for an index scan and index seek.

carlosf is the smaller table, isn't it? it's email index is read in, and the prefix.email index is seeked in to find the records to update. There is no full scan of all prefix records.

Everythings OK. If this takes too long, check out the overall server load and resources. The query is not the problem.

Bye, Olaf.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
robrodpAuthor Commented:
thx... I will set this as a solution, though sometime I receive psosts saying that it is not. Well ist is good enough foe me.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Well, first of all an index scan is not a table scan. It loads the smaller index with email field only, so that is faster than a table scan. Then of course, what do you expect when not having any condition on the t2 (carlosf) table? It has to load all data from it, loading the email index instead of scanning the whole table already is the best SQL Server can do.

Bye, Olaf.
0
 
robrodpAuthor Commented:
Thx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now