Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 join for large tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapient007
Starting Member

5 Posts

Posted - 07/31/2007 :  14:39:11  Show Profile  Reply with Quote
joining 2 large tables (15million rows table A) with (2million rows Table B). getting new server is currently not an option. on top of that we are joining on ~30 columns with some of them with lots of NULLS.

this is actually a script to clean up the DB from duplicates and the smaller table is actually inserted with all duplicates from the larger table to be re-inserted back when it's cleaned up. The issue i'm having is that during the delete join statement it's taking almost 7hrs and it's a pain to wait for it to get it done.

anyway to shorten it?? i'm currently joining A to B. i'm under the impression that during inner join, the order/position of the table doesn't matter. am i wrong?

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/31/2007 :  14:41:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
A query would help.
A description of which indexes you have, would help too.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sapient007
Starting Member

5 Posts

Posted - 07/31/2007 :  14:49:00  Show Profile  Reply with Quote
aight, i did some quick global replace. B is the temp table to remove dupes. table A actually has more columns I'm only interested in those listed.

truncate table B

insert into B (AID, BG, DD, DT, ea, dcd, emct, GID, hle, hgs, hsid, [ID], AID, BG, DD, DT, ea, dcd, emct, GID, hle, hgs, hsid, [ID]) (select AID, BG, DD, DT, ea, dcd, emct, GID, hle, hgs, hsid, [ID], AID, BG, DD, DT, ea, dcd, emct, GID, hle, hgs, hsid, [ID] having count(*) >1 )

delete from A from B inner join A on
A.AID= B.AID AND
A.BG= B.BG AND
A.DD= B.DD AND
A.DT= B.DT AND
A.ea= B.ea AND
A.dcd= B.dcd AND
A.emct= B.emct AND
A.GID= B.GID AND
A.hle= B.hle AND
A.hgs= B.hgs AND
A.hsid= B.hsid AND
A.[ID]= B.[ID] AND
A.AID= B.AID AND
A.BG= B.BG AND
A.DD= B.DD AND
A.DT= B.DT AND
A.ea= B.ea AND
A.dcd= B.dcd AND
A.emct= B.emct AND
A.GID= B.GID AND
A.hle= B.hle AND
A.hgs= B.hgs AND
A.hsid= B.hsid


insert into A select * from B


Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 07/31/2007 :  16:26:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
delete that large will take a long time due to transaction log resizing.

try deleting this in batches of 10000.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

sapient007
Starting Member

5 Posts

Posted - 07/31/2007 :  16:38:12  Show Profile  Reply with Quote
how do i delete in batches of 1000??


do i cap dupe table size to 1000 rows with the insert into xx (select top 10000 ) caluse??

or is there a way to only join the first 10000 rows?
Go to Top of Page

sapient007
Starting Member

5 Posts

Posted - 07/31/2007 :  16:40:17  Show Profile  Reply with Quote
on a side note, is there a way to avoid transaction log all together on the delete/join statement since table restore will not ever be needed??
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 07/31/2007 :  16:44:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
1.
here's a noce thread covering that:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084

2.
no. you can't avoid tran log.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000