Solved

ms sql Update date a table scanning all records

Posted on 2014-10-12
6
150 Views
Last Modified: 2014-10-17
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
Comment
Question by:robrodp
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40375721
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
 

Author Comment

by:robrodp
ID: 40375737
Attached find the execution plan
sql.png
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40375742
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Accepted Solution

by:
robrodp earned 0 total points
ID: 40375760
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40375787
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
 

Author Closing Comment

by:robrodp
ID: 40386220
Thx
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Update Query Streamline 4 29
SQL Server 2005 - Comparing Fields' Contents 6 34
sql server concatenate fields 10 35
Help Required 3 96
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question