delete records - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete records

Author

Topic

anandbohra
Starting Member

4 Posts

Posted - 2007-06-19 : 03:39:05

I have one SQL table say tableall containing 25 million records
scrip - date - price
now when i import data it imported for say 185 scrips
now i have another table say tblrequired which contains only 120 scrips for which i want the data

now my query is how to delete records from tablall for scrips which are not in tblrequired

pl give syntax

Anand Manmohan Bohra

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 03:45:00

[code]
DELETE D
FROM tableall AS D
LEFT OUTER JOIN tblrequired AS R
ON R.scrip = D.scrip
WHERE R.scrip IS NULL
[/code]
However, for 25 million rows this will run like a dog

Its fine for a one off, but for a production system using this (scheduled, whatever) you need to loop round and process the data in batches, and if your Recovery model is FULL you will also need to make sure that you are performing TLog backups frequently during the delete process)

If you are doing this as a one off, without a loop and thus in a single transaction, make sure you have plenty of disk space for the LDF file, and plan to Shrink it (back to the size it is now) after the Delete operation is done.

Kristen

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-06-19 : 03:46:33

I can't resist the urge to tell you to read books online and learn SQL before you go mucking around deleting 25 million rows! (What's the betting I get this wrong now)

delete from tablall where scrip not in (select scrip from tblrequired).

In actual fact though you would most likely be better off selecting what you do want into a table with a different name, truncate tableall and then put it back again/rename the other table.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-19 : 05:20:35

I always had better results using a exists statment over a left join. Definantly don't use a IN operator, it's not meant for large comparisons.


DELETE D
FROM tableall AS D
     LEFT OUTER JOIN tblrequired AS R
         ON R.scrip = D.scrip
WHERE not exists (Select * from Tbl1Requered R where R.script = D.Scrip)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 05:43:45

It's worthwhile testing - I've seen better performance using the in operator on v2005.
that left join is redundant
DELETE D
FROM tableall AS D
WHERE not exists (Select * from Tbl1Requered R where R.script = D.Scrip)

Do you have an index on scrip?

Might be better to bcp the data you need to a file then bcp it in.
Or you could import the data you need in the first place - depends how you are importing it though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 07:54:20

I don't think that EXISTS / IN / LEFT JOIN matter overly in this case. Trying to delete a massive number of records in a single-batch is going to cause serious problems if the ramifications have not been considered.

I would wager that the PKs have got to be put in a temporary table first, and then deleted in batches.

Plus there is the issue that Nigel raises that if the records to be KEPT are much fewer than those to be deleted, then planning to keep the "good" records (Export / copy to other table / whatever) is probably going to be the best strategy.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources