SQL Server Performance

Slow Query Help Please

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by jamie.downs, Oct 12, 2009.

  1. jamie.downs Member

    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.
  2. Adriaan New Member

    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.
  3. subramanivg New Member

    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
  4. Flexdog New Member

    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.

Share This Page