TempDB is gwoing out of control

[charvett]

I am running the following query and it is causing the tempdb to grow to over 100GB and run our of disk space. I'm stumped as to make it more effecient so it will not cause this behavior.

Thanks for any help! Here's the query:

CREATE TABLE #ACMESites (site_id VARCHAR (120) COLLATE Latin1_General_BIN PRIMARY KEY)

INSERT INTO #ACMESites
SELECT site_id FROM ACMESites
WHERE ACMESites.site_id IN (SELECT DISTINCT site_id FROM ACMEGroup_Members)

UPDATE ACMEJournalPage SET SiteID = ACMERange.site_id
FROM ACMEJournalPage
INNER JOIN ACMEHostID ON ACMEJournalPage.HostID = ACMEHostID.HostID
INNER JOIN ACMERange ON ACMEHostID.HostNum BETWEEN
ACMERange.low_address AND ACMERange.high_address
INNER JOIN #ACMESites ON ACMERange.site_id = #ACMESites.site_id
INNER JOIN ACMESite_Product ON ACMERange.site_id = ACMESite_Product.site_id AND
ACMESite_Product.product_id = ACMEJournalPage.Journal

 

[maswien]

To rewrite this query, you need post size for all the tables in the query and the relationships between these tables, for example,

1. ACMEHostID <-> ACMEJournalPage

relationship: 1 to many (or whatever)

join columns: HostID

2. ...

 

[maswien]

The only part can be optimized at this point is as following:

INSERT INTO #ACMESites
SELECT site_id FROM ACMESites
WHERE ACMESites.site_id IN
(SELECT DISTINCT site_id FROM ACMEGroup_Members)

=>

INSERT INTO #ACMESites
SELECT site_id FROM ACMESites
WHERE ACMESites.site_id IN
(SELECT site_id FROM ACMEGroup_Members)


The DISTINCT is not neccesary and only increase overhead.