Slow Query Help Please

Hi All,
I'm trying to insert some records from one table where they have corresponding rows in another. I have tried a couple of approaches.
Please bear in mind we are talking about 27m rows.SELECT
* FROM DealAttribute WHERE DealID IN (SELECT DealID FROM DM_Temp_Table_DEL_DEAL) this takes around 4 hrs. 27104902 rowsSELECT DA.* FROM DealAttribute DA INNER JOIN DM_Temp_Table_DEL_DEAL temp ON DA.DealID = temp.DealID this takes around 30mins. 1893757367rows (CONFUSED)SELECT DA.* FROM DealAttribute DA RIGHT JOIN DM_Temp_Table_DEL_DEAL temp ON DA.DealID = temp.DealID this takes around 30mins. 1893757367rows (CONFUSED)
SELECT
DA.* FROM DealAttribute DA WHERE EXISTS(SELECT DealID FROM DM_Temp_Table_DEL_DEAL AS temp WHERE DA.DealID = temp.DealID) this takes around 4 hrs. 27104902 rows
As you might expect the plans are different.
Am I missing something obvious here?
Thanks for taking time to read. Hopefully someone can suggest somthing.
Jamie.

That you're getting 100x as many rows with both INNER and RIGHT JOIN suggests that you have duplicate matches on DealID.
The name DealAttribute sounds a little ominous: suggests you might need to read multiple rows to get the details for a single DealID - which again would explain the duplicate matches.
Is there a parent table for DealAttribute? That should give you a single match for DealID.
DM_Temp_Table_DEL_DEAL needs at least an index on DealID in order for the subquery to perform better.

Hi Jamie

Try this out. I believe it should take less time


Select D.* From DealAttribute D (NoLock), DM_Temp_Table_DEL_DEAL T (NoLock) Where D.DealID=T.DealID


OR

Insert those records in a tmp table & run the select query from fetch records from tmp table by using below queries


Select D.* into TmpDealID From DealAttribute D (NoLock), DM_Temp_Table_DEL_DEAL T (NoLock) Where D.DealID=T.DealID

Select * From TmpDealID



Regards
Subbu

Hi Jamie,
How's going at Msft?
If the replica is not too busy, you should get back 27mil rows in about 5 to 20 minutes
SELECT da.*
FROM (SELECT DISTINCT dealId FROM DealAttribute(nolock)) sda
INNER JOIN (SELECT DISTINCT dealId FROM DM_Temp_Table_DEL_DEAL(nolock)) std ON sda.dealId = std.dealId
INNER JOIN DealAttribute da ON sda.dealId=da.dealId
* you can also use GROUP BY instead of DISTINCT

runtime wise - I expect:
sda - a small resultset but from a large table > 30mil, so it would takes a few minutes
std - also a small resultset, very fast if temp table is small, otherwise again takes a few minutes
da.* - a few minutes since there probably an index on DealAttribute.dealId
Let's know if this help or how you solved this perf problem...
Flexdog.