title: How do I speed up this multi-statement tabled valued function?
body: <p>I wrote this function that feeds into an XSLT tool for reporting.  It's incredibly slow, taking about 1.5-2 seconds on average to return 100 rows.  This gets worse as this query can return up to 2000 rows.  Any sort of excess load is causing the query to time out, so now I need to optimize it.</p>
  
  <pre><code>USE [ININ_SID]
  
  GO
  
  /****** Object:  UserDefinedFunction [dbo].[GetProjectData]    Script Date: 12/30/2011 10:27:22 ******/
  SET ANSI_NULLS ON
  
  GO
  
  SET QUOTED_IDENTIFIER ON
  
  GO
  
  ALTER FUNCTION [dbo].[GetProjectData] (
  -- Add the parameters for the function here
  @ProjectNo VARCHAR(100))
  RETURNS @Final TABLE (
    Customer             VARCHAR(250),
    ProjectNo            VARCHAR(50),
    ProjectName          VARCHAR(512),
    ProjectType          VARCHAR(100),
    PhaseID              INT,
    Phase                VARCHAR(50),
    NameField1           VARCHAR(100),
    IDField1             INT,
    IDField2             INT,
    ItemDescription      VARCHAR(100),
    Health               VARCHAR(6),
    CompletionPercentage INT,
    Status               VARCHAR(16),
    Owner                VARCHAR(64),
    ItemNotes            VARCHAR(140),
    ProjectNotes         VARCHAR(140))
  AS
    BEGIN
        DECLARE @Health TABLE (
          ProjectID    INT,
          ProjectNotes VARCHAR(140))
        DECLARE @Projects TABLE (
          ProjectID     INT PRIMARY KEY,
          ProjectSiteId INT,
          ProjectNo     VARCHAR(50),
          ProjectType   VARCHAR(100),
          ProjectName   VARCHAR(512),
          Customer      VARCHAR(250),
          UNIQUE (ProjectID))
  
        INSERT INTO @Projects
        SELECT PSO_Projects.ProjectID,
               ProjectSiteID,
               ProjectNo,
               ProjectType,
               ProjectName,
               Customers.Name AS Customer
        FROM   PSO_Projects
               INNER JOIN Customers
                 ON Customers.CustomerID = PSO_Projects.CustomerID
        WHERE  PSO_Projects.ProjectNo = @ProjectNo
               AND CompletionDate IS NULL
  
        -- Get the latest health/notes for the projects
        -- This will be used in an inner join later
        INSERT INTO @Health
        SELECT ProjectID,
               ProjectNotes
        FROM   (SELECT ProjectID,
                       Notes                                                              AS ProjectNotes,
                       Rank() OVER (Partition BY ProjectID ORDER BY LastUpdatedDate DESC) AS Rank
                FROM   PSO_ProjectHealthEntries) tmp
        WHERE  Rank = 1
               AND ProjectID IN (SELECT ProjectID
                                 FROM   @Projects)
  
        -- PROJECT DELIVERABLES
        INSERT INTO @Final
                    (Customer,
                     ProjectNo,
                     ProjectName,
                     ProjectType,
                     PhaseID,
                     Phase,
                     NameField1,
                     IDField1,
                     IDField2,
                     ItemDescription,
                     Health,
                     CompletionPercentage,
                     Status,
                     Owner,
                     ItemNotes,
                     ProjectNotes)
        SELECT Customer,
               ProjectNo,
               ProjectName,
               ProjectType,
               PSO_phases.PhaseID,
               PSO_Phases.Name,
               PSO_Phases.PhaseType,
               PSO_Deliverables.DeliverableID,
               PSO_Deliverables.MasterID,
               PSO_Deliverables.Name,
               PSO_Deliverables.Health,
               PSO_Deliverables.CompletionPercentage,
               pso_deliverables.Status,
               DeliverableContacts.Name,
               PSO_Deliverables.Notes,
               Health.ProjectNotes
        FROM   @Projects Projects
               INNER JOIN @Health Health
                 ON Health.ProjectID = Projects.ProjectID
               INNER JOIN PSO_Phases
                 ON PSO_Phases.ProjectSiteID = Projects.ProjectSiteID
               LEFT JOIN PSO_Deliverables
                 ON PSO_Deliverables.PhaseID = PSO_Phases.PhaseID
               LEFT JOIN PSO_DeliverableAssociations
                 ON PSO_DeliverableAssociations.DeliverableID = PSO_Deliverables.DeliverableID
                    AND PSO_DeliverableAssociations.Active = 1
               LEFT JOIN PSO_ProjectContacts DeliverableContacts
                 ON DeliverableContacts.ContactID = PSO_DeliverableAssociations.ContactID
        WHERE  PSO_Phases.Name  'System Development & Deployment'
               AND PSO_Deliverables.Type NOT IN (SELECT DISTINCT ManagementGroup
                                                 FROM   PSO_DeliverableOwnership)
               AND Projects.ProjectID IN (SELECT ProjectID
                                          FROM   @Projects)
  
        -- ADDON DELIVERABLES
        INSERT INTO @Final
                    (Customer,
                     ProjectNo,
                     ProjectName,
                     ProjectType,
                     PhaseID,
                     Phase,
                     NameField1,
                     IDField1,
                     IDField2,
                     ItemDescription,
                     Health,
                     CompletionPercentage,
                     Status,
                     Owner,
                     ItemNotes,
                     ProjectNotes)
        SELECT Customer,
               ProjectNo,
               ProjectName,
               ProjectType,
               PSO_Phases.PhaseID,
               'Add-On Deliverables',
               PSO_Deliverables.SubType,
               PSO_Deliverables.DeliverableID,
               PSO_Deliverables.MasterID,
               PSO_Deliverables.Name + ' (' + PSO_Deliverables.SubType + ')',
               PSO_Deliverables.Health,
               PSO_Deliverables.CompletionPercentage,
               PSO_Deliverables.Status,
               PSO_ProjectContacts.Name,
               PSO_Deliverables.Notes,
               Health.ProjectNotes
        FROM   @Projects Projects
               INNER JOIN @Health Health
                 ON Health.ProjectID = Projects.ProjectID
               INNER JOIN PSO_Phases
                 ON PSO_Phases.ProjectSiteID = Projects.ProjectSiteID
               INNER JOIN PSO_Deliverables
                 ON PSO_Deliverables.PhaseID = PSO_Phases.PhaseID
               LEFT JOIN PSO_DeliverableAssociations
                 ON PSO_DeliverableAssociations.DeliverableID = PSO_Deliverables.DeliverableID
                    AND PSO_DeliverableAssociations.Active = 1
               LEFT JOIN PSO_ProjectContacts
                 ON PSO_ProjectContacts.ContactID = PSO_DeliverableAssociations.ContactID
        WHERE  Projects.ProjectID IN (SELECT ProjectID
                                      FROM   @Projects)
               AND ( PSO_Deliverables.Type IN (SELECT DISTINCT ManagementGroup
                                               FROM   PSO_DeliverableOwnership) )
  
        -- PROJECT  SITE NAMES ONLY
        INSERT INTO @Final
                    (Customer,
                     ProjectNo,
                     ProjectName,
                     ProjectType,
                     PhaseID,
                     Phase,
                     NameField1,
                     IDField1,
                     IDField2,
                     ItemDescription,
                     Health,
                     CompletionPercentage,
                     Status,
                     Owner,
                     ItemNotes,
                     ProjectNotes)
        SELECT Customer,
               ProjectNo,
               ProjectName,
               ProjectType,
               0,
               'Systems Development and Deployment',
               PSO_Sites.Name,
               PSO_Sites.SiteID,
               PSO_Sites.SiteID,
               PSO_Sites.Name,
               '' AS Health,
               '' AS CompletionPercentage,
               '' AS Status,
               '' AS Owner,
               '' AS Notes,
               Health.ProjectNotes
        FROM   @Projects Projects
               INNER JOIN @Health Health
                 ON Health.ProjectID = Projects.ProjectID
               INNER JOIN PSO_Sites
                 ON PSO_Sites.ProjectID = Projects.ProjectID
        WHERE  Projects.ProjectID IN (SELECT ProjectID
                                      FROM   @Projects)
  
        RETURN
    END 
  </code>
  </pre>
  
  <p>And on top of this, order is extremely important.  This is how it's called:</p>
  
  <pre>
  <code>
  SELECT Data.*
  FROM   PSO_Projects
         INNER JOIN PSO_ProjectAssociations
           ON PSO_ProjectAssociations.ProjectID = PSO_Projects.ProjectID
              AND PSO_ProjectAssociations.Active = 1
         INNER JOIN PSO_ProjectContacts
           ON PSO_ProjectContacts.ContactID = PSO_ProjectAssociations.ContactID
         CROSS apply Getprojectdata(ProjectNo) Data
  WHERE  PSO_ProjectContacts.Name = 'John.Smith'
         AND PSO_Projects.CompletionDate IS NULL
  ORDER  BY Customer,
            ProjectNo,
            CASE
              WHEN Phase = 'Initiation' THEN PhaseID
              WHEN Phase = 'Planning' THEN PhaseID + 2000
              WHEN Phase = 'Requirements & Design' THEN PhaseID + 4000
              WHEN Phase = 'Systems Development and Deployment' THEN PhaseID + 6000
              WHEN Phase = 'Add-On Deliverables' THEN PhaseID + 8000
              WHEN Phase = 'Closing' THEN PhaseID + 10000
              ELSE PhaseID
            END,
            IDField2,
            IDField1 
  </code>
  </pre>
  
  <p>Is there anything I can do in the query to optimize this?  The only other real option I have is to find a way to refactor the report to be able to handle fewer columns (such as returning the project name/customer in a single row instead of including it with every row)  but I'm not sure how feasible this is.</p>