CODEWITHSUNDEEP
sqlsql-server
JTouchLogic
JTouchLogic

Reputation: 57

Optimize this query using a Join and Group By?

Can I optimize this query using a Join and Group By?

 SELECT PM.Program_ID, 
                PM.[Program_Name], 
                ISNULL(
         (
             SELECT TOP 1 VF.ReadBy
             FROM VVF_ScriptFlow VF
             WHERE VF.ProgramId = PM.Program_ID
             ORDER BY VF.Id DESC
         ), 'NA') AS CurrentOwner
         FROM Program_Master PM
         WHERE PM.Department_ID = @DepartmentId;
     END;

Upvotes: 0

Views: 66

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Not much to go on here since we don't have table definitions, indexes, row counts or an execution plan. All of which are instrumental when dealing with performance problems.

My blind guess is to try an OUTER APPLY. It may or may not be anything different but I can't actually test anything. I would also urge you to always specify which table a column comes from. It is really hard to decipher when you don't know.

SELECT 
    pm.Program_ID, 
    pm.[Program_Name],
    ISNULL(x.ReadBy, 'NA') AS CurrentOwner
FROM Program_Master pm
outer apply
(
    SELECT TOP 1 sf.ReadBy 
    FROM VVF_ScriptFlow sf
    WHERE sf.ProgramId = pm.Program_ID 
    ORDER BY sf.Id DESC
) x
WHERE pm.Department_ID = @DepartmentId;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

For this query:

SELECT pm.Program_ID, pm.[Program_Name],
       isnull((select top 1 s.ReadBy
               from VVF_ScriptFlow s
               where s.ProgramId = pm.Program_ID  -- hope I got the table alisses right
               order by Id desc
              ), 'NA') as CurrentOwner
FROM Program_Master pm
WHERE Department_ID = @DepartmentId;

You want two indexes:

  • Program_Master(Department_Id, Program_ID, [Program Name]
  • VVF_ScriptFlow(ProgramId, id desc, ReadBy)

I doubt you will get better performance with these indexes.

Note that this is one of the cases where you want to use ISNULL() rather than COALESCE(). SQL Server is rather stupid about how it treats COALESCE() (well, I might say malicious in the interpretation of the standard); with COALESCE() it will evaluate the subquery twice when the value is not NULL.

Upvotes: 1

Related Questions