Deteting from 50M database - 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)

Deteting from 50M database

Author

Topic

DBTEAM
Starting Member

4 Posts

Posted - 2009-11-24 : 05:08:10

Hi All,

Which one is the efficient Way from the below two for 50M database
having 30+ fields

with EmailRecords as (
select
row_number() over (partition by Email order by rowid desc) as RowNumber ,
[Company],[webAddress] ,[Prefix] ,[Contactname] ,[FirstName] ,[MiddleName] ,
[LastName] ,[Title] ,[Address] ,[Address1] ,[Address2] ,[Address3] ,[City] ,
[State] ,[Pincode] ,[STDcode] ,[Phone] ,[Phone1] ,[Phone2] ,[Phone3] ,
[FaxNumber] ,[Mobile] ,[Email] ,[Industry] ,[Product Code] ,[Revenue] ,
[Experience] ,[Dateofbirth] ,[dob] ,[age] ,[martialstatus] ,[Keyskills] ,
[education] ,[category] ,[Dealer]

from dbo.table2

)
insert into dbo.[NewTable]
select [Company],[webAddress] , [Prefix] , [Contactname] , [FirstName] ,[MiddleName] ,
[LastName] ,[Title] ,[Address] ,[Address1] ,[Address2] ,[Address3] ,[City] ,
[State] ,[Pincode] ,[STDcode] ,[Phone] ,[Phone1] ,[Phone2] ,[Phone3] ,
[FaxNumber] ,[Mobile] ,[Email] ,[Industry] ,[Product Code] ,[Revenue] ,
[Experience] ,[Dateofbirth] ,[dob] ,[age] ,[martialstatus] ,[Keyskills] ,
[education] ,[category] ,[Dealer]
from EmailRecords
where RowNumber = 1;

---------------------------------------------------------
DELETE FROM table2
WHERE rowid IN
(
SELECT a.rowid
FROM table2 a,table2 b
WHERE a.rowid!= b.rowid
and a.rowid< b.rowid
and a.[Email]= b.[Email]
)

Awaiting ur reply...

DBTEAM
Starting Member

4 Posts

Posted - 2009-11-24 : 06:00:58

Any body please suggest me regarding this

thnx in advance....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-24 : 07:02:56

Have patience....Most of the answers here may come from the USA...which is still asleep.

I'd favour #2...but your example code is a bit-discjointed in that you insert into Newtable, but delete from table1 joined to table2.
Speed will be affected by good indices on both tables. And also the amount of other database activity within the database.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-24 : 10:03:00

Can you use Truncate? This may or may not help you and you may or may not already know this but, Truncate will delete the entire table like a lightning bolt because/but it does not log the deletes.

If the table you are deleting from has FKs, it will be slower if the FKs are not indexed in the parent tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources