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 |
|
VinnyPip
Starting Member
7 Posts |
 Posted - 2008-10-24 : 09:51:42
|
| i wrote the following query:DELETE FROM TABLE1WHERE TABLE2.CUST_NUM NOT IN (SELECT CUST_NUM FROM TABLE2)a vendor of my company is a bit behind the times and they are using SQL7 (no, i'm not kidding)... on their server there is TABLE1 which has 400,000 records and TABLE2 which has 1.1 million... both relatively small tables now-a-days but when i run this delete it takes over an hour before i hit the stop button... i never got to completion because it takes too long... i am assuming that all of TABLE2 is being read for each record of TABLE1... that's 1.1 million records being read 400,000 times... there has got to be an easier way...i built indexes on both files based on CUST_NUM but i still hit the stop button at the 1 hour mark... does anyone know how to delete records from one table when a field from that table exists in another table... but relatively faster than i do?Please Help!!!THANKS,Vinny |
|
|
VinnyPip
Starting Member
7 Posts |
Posted - 2008-10-24 : 09:52:50
|
| i think i put this post in the wrong section... sorry! |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-10-24 : 10:10:35
|
| moved from script library.Vinny for future info: you can move the topic by yourself into the appropriate forum._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-24 : 10:12:45
|
[code]DELETE t1FROM TABLE1 AS t1LEFT JOIN TABLE2 AS t2 ON t2.CUST_NUM = t1.CUST_NUMWHERE t2.CUST_NUM IS NULL[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
VinnyPip
Starting Member
7 Posts |
Posted - 2008-10-24 : 12:06:52
|
| thanks Peso... 3 minutes 11 seconds... right on the money! |
|
|
|
|
|
|
Please Help!