SQL Server Performance

Sub Query Help

Discussion in 'General Developer Questions' started by vimalpercy, Dec 21, 2009.

  1. vimalpercy New Member

    Hello Experts
    My requirment is this.
    I have a master table say (MasterID, Recname1,RecName2...)
    I have a child table that looks like this (ChildID,MasterID,DateStamp,Description)
    My requirement is get all the master records and get the latest child record with datestamp and description.
    What is the best way to get this done.
  2. Adriaan New Member

    To get the combined information, obviously you would use an INNER JOIN:
    SELECT m.RecName1, c.Description
    FROM MasterTable m INNER JOIN ChildTable c
    Now to get only the latest child row, you add a correlated subquery:
    WHERE c.ChildID = (SELECT TOP 1 x.ChildID FROM ChildTable x WHERE x.MasterID = c.MasterID ORDER BY x.DateStamp DESC)
  3. vimalpercy New Member

    Thanks Adriaan
    I have tried this query but it takes too long to process the query.
    I have created a Temp Table and loaded the records and filtered it into another temp table.
    This method improved the query process time.
    Yours took 1 min 45 secs and the Temp table method took 30 secs but I am looking for ways to even speed up the query. The child table has some million records.
    Thanks for your quick reply
  4. Adriaan New Member

    Sounds like your child table doesn't have appropriate indexes. For instance PK clustered on (ChildID) and non-clustered on (MasterId, DateStamp).
  5. balaganapathy.n New Member

    Hi,Please try this and let me know if it helps.

    SELECT *FROM (SELECT M.*, C.ChildID, C.DateStamp, C.Description FROM MasterTable MINNER JOIN ChildTable CON M.MasterID = C.MasterID) AS SET1INNER JOIN (SELECT ChildID, MAX(DateStamp) AS MaxDateStamp FROM ChildTable GROUP BY ChildID) AS SET2ON SET1.ChildID = SET2.ChildIDAND SET1.DateStamp = SET2.MaxDateStamp

  6. charlii New Member

    By joining inner you can get combined data.This will help you to achieve targeted tasks.

Share This Page