Large table joins filling tempdb

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 2000 Forums

SQL Server Development (2000)

Large table joins filling tempdb

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-27 : 10:44:48

David McInally writes "I have a table with 3M rows and a table with 500K rows - I am performing a join to extract data - table A has 1.5M rows; table B has 3M rows - as follows : <SQL Server 2000>

INSERT INTO TABLE_C (contract_id, product_id, licensee_id,
territory_id, fees, right_code, start_date, end_date, runs, rerun, host_id, date_used, terr_rht_id, alloc, line, contract_date, revno, sectid, currcode, type)

SELECT DISTINCT a.mainno, a.product, a.lcensee, a.terrcode, null, r.rhtcode, r.lbegdate, r.lexpdate, a.rnsprflm, null, :gs_host_id, :ldt_now, r.terrcode, 1, a.slineno, a.contdate, a.revno,null, null, 'D'

FROM TABLE A,
TABLE B

WHERE A.mainno = B.mainno
and A.revno = B.revno
and A.sectid = B.sectid
and A.slineno = B.slineno
and B.status in ('2', '3')
and B.rhttype in ('RE', 'RN', 'RO')
and ((:ldt_end >= B.lbegdate
or B.lbegdate is null)
and (:ldt_start <= B.lexpdate
or B.lexpdate is null)) ;

every time I run this I run out of workspace in TempDB - 1.5G allocated. I cannot increase tempDB. Please Help?

Thanks"

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-05-27 : 17:44:46

try doing a select distinct top 50% then order by your contract_id. Do it again with contract_id desc.

Do you necessarily need to do a select distinct on 3 million rows?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-27 : 18:00:03

This might help things out:

Change this:


FROM TABLE A, 
TABLE B 

WHERE A.mainno = B.mainno 
and A.revno = B.revno 
and A.sectid = B.sectid 
and A.slineno = B.slineno

To this:


FROM Table a
INNER JOIN Table b ON A.mainno = B.mainno and A.revno = B.revno and A.sectid = B.sectid and A.slineno = B.slineno

That might make it join better and make it use less TempDB.
See what the estimated execution plan is compared to your original query.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-27 : 23:19:13

Just curious, where is the table and it's joins that is aliased as r in your select statement? It's not represented in the code you posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources