how to get responce within 5 sec from below sp

To optimize the performance of your stored procedure and reduce the response time, you can consider the following modifications:

1. Avoid Using Temporary Tables: Instead of using temporary tables, consider using table variables. Table variables are often more efficient for smaller datasets.

2. Avoid Using NOLOCK Everywhere: While using NOLOCK can improve performance, it might also lead to dirty reads. Only use it where necessary, and be cautious of potential data inconsistencies.

3. Simplify the Date Calculation: Instead of calculating the age in multiple places, calculate it once and use the result throughout the query. This can reduce redundancy and improve readability.

4. Use CASE Statement Directly in GROUP BY and SELECT: Instead of using a separate subquery to create the AgeGroup and Count, use the CASE statement directly in the GROUP BY and SELECT clauses.

Here's an optimized version incorporating these suggestions:

ALTER PROCEDURE usp_AssociateAgeingDashBoard   
  @intMappingId AS BIGINT,   
  @intClientId  BIGINT = 0,   
  @intFacilityId  BIGINT = 0  
AS          
BEGIN          
  SET NOCOUNT ON                                      
  
  DECLARE @Ageing TABLE (
    AgeGroup VARCHAR(10),
    Count INT,
    Sequence INT
  )
  
  DECLARE @ClientIds TABLE (
    ClientId INT
  )
  
  DECLARE @FacilityIds TABLE (
    FacilityId INT
  )
  
  IF @intClientId = 0
  BEGIN
    INSERT INTO @ClientIds (ClientId)
    SELECT ClientID FROM EmployeeClientMapping WHERE EmployeeId = @intMappingId AND IsActive = 1
  END
  ELSE
  BEGIN
    INSERT INTO @ClientIds (ClientId) VALUES (@intClientId)
  END
  
  IF @intFacilityId = 0
  BEGIN
    INSERT INTO @FacilityIds (FacilityId)
    SELECT FacilityId FROM EmployeeFacilityMapping WHERE EmployeeId = @intMappingId AND IsActive = 1
  END
  ELSE
  BEGIN
    INSERT INTO @FacilityIds (FacilityId) VALUES (@intFacilityId)
  END
  
  INSERT INTO @Ageing (AgeGroup, Count, Sequence)
  SELECT
    CASE 
      WHEN Age >= 18 AND Age <= 20 THEN '18-20'
      WHEN Age > 20 AND Age <= 25 THEN '21-25'
      -- Add more cases for other age groups
    END AS AgeGroup,
    COUNT(*) AS Count,
    ROW_NUMBER() OVER (ORDER BY Age) AS Sequence
  FROM (
    SELECT DATEDIFF(YEAR, DOB, GETDATE()) AS Age
    FROM vwAssociateDetailsBasic AM WITH (NOLOCK)
    INNER JOIN @ClientIds Client ON Client.ClientId = AM.ClientID
    INNER JOIN @FacilityIds Facility ON Facility.FacilityId = AM.FacilityID
    WHERE ISACTIVE = 1
  ) AS A
  WHERE AgeGroup IS NOT NULL
  GROUP BY AgeGroup, Age
  ORDER BY Sequence
  
END

This version should perform better, but you may need to adjust it based on your specific requirements and data characteristics.

Another version of modified query is below:

ALTER PROCEDURE usp_AssociateAgeingDashBoard
    @intMappingId AS BIGINT,
    @intClientId BIGINT = 0,
    @intFacilityId BIGINT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ClientId TABLE (ClientId INT);
    DECLARE @FacilityId TABLE (FacilityId INT);

    IF @intClientId = 0
    BEGIN
        INSERT INTO @ClientId (ClientId)
        SELECT ClientID
        FROM EmployeeClientMapping WITH (NOLOCK)
        WHERE EmployeeId = @intMappingId AND IsActive = 1;
    END
    ELSE
    BEGIN
        INSERT INTO @ClientId (ClientId) VALUES (@intClientId);
    END

    IF @intFacilityId = 0
    BEGIN
        INSERT INTO @FacilityId (FacilityId)
        SELECT FacilityId
        FROM EmployeeFacilityMapping WITH (NOLOCK)
        WHERE EmployeeId = @intMappingId AND IsActive = 1;
    END
    ELSE
    BEGIN
        INSERT INTO @FacilityId (FacilityId) VALUES (@intFacilityId);
    END

    WITH AgeCalculation AS (
        SELECT
            CASE
                WHEN DATEDIFF(YY, DOB, GETDATE()) BETWEEN 18 AND 20 THEN '18-20'
                WHEN DATEDIFF(YY, DOB, GETDATE()) BETWEEN 21 AND 25 THEN '21-25'
                -- Add more age groups as needed
            END AS AgeGroup,
            DATEDIFF(YY, DOB, GETDATE()) AS Age
        FROM
            [vwAssociateDetailsBasic] AM WITH (NOLOCK)
            INNER JOIN @ClientId Client ON Client.ClientId = AM.ClientID
            INNER JOIN @FacilityId Facility ON Facility.FacilityId = AM.FacilityID
        WHERE
            ISACTIVE = 1
    )

    SELECT
        AgeGroup,
        SUM(1) AS [Count],
        ROW_NUMBER() OVER (ORDER BY Age) AS [Sequence]
    FROM
        AgeCalculation
    WHERE
        AgeGroup IS NOT NULL
    GROUP BY
        AgeGroup
    ORDER BY
        [Sequence];
END;

i modified this sp but its taking 13 sec to execute 

please i want to execute within 5 sec

ALTER PROCEDURE usp_AssociateAgeingDashBoard2 4002189,0,0
    @intMappingId AS BIGINT,
    @intClientId BIGINT = 0,
    @intFacilityId BIGINT = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#ClientId') IS NOT NULL DROP TABLE #ClientId;
    CREATE TABLE #ClientId
    (
        ClientId INT
    );

    IF @intClientId = 0
    BEGIN
        INSERT INTO #ClientId(ClientId)
        SELECT ClientID FROM EmployeeClientMapping WITH (NOLOCK) WHERE EmployeeId = @intMappingId AND IsActive = 1;
    END
    ELSE
    BEGIN
        INSERT INTO #ClientId(ClientId) VALUES (@intClientId);
    END

    IF OBJECT_ID('tempdb..#FacilityId') IS NOT NULL DROP TABLE #FacilityId;
    CREATE TABLE #FacilityId
    (
        FacilityId INT
    );

    IF @intFacilityId = 0
    BEGIN
        INSERT INTO #FacilityId(FacilityId)
        SELECT FacilityId FROM EmployeeFacilityMapping WITH (NOLOCK) WHERE EmployeeId = @intMappingId AND IsActive = 1;
    END
    ELSE
    BEGIN
        INSERT INTO #FacilityId(FacilityId) VALUES (@intFacilityId);
    END

    ;WITH AgeCalculation AS (
        SELECT
            CASE
                WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN '18-20'
                WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN '21-25'
                WHEN DATEDIFF(YY,DOB,GETDATE()) >25 AND DATEDIFF(YY,DOB,GETDATE()) <=30 THEN '26-30'            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >30 AND DATEDIFF(YY,DOB,GETDATE()) <=35 THEN '31-35'            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >35 AND DATEDIFF(YY,DOB,GETDATE()) <=40 THEN '35-40'            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >40 AND DATEDIFF(YY,DOB,GETDATE()) <=45 THEN '41-45'            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >45 AND DATEDIFF(YY,DOB,GETDATE()) <=50 THEN '46-50'             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >50 AND DATEDIFF(YY,DOB,GETDATE()) <=55 THEN '51-55'             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >55 AND DATEDIFF(YY,DOB,GETDATE()) <=60 THEN '55-60'
                -- Add more age groups as needed
            END AS AgeGroup,
            --DATEDIFF(YY, DOB, GETDATE()) AS Age
            COUNT(            
                CASE WHEN DATEDIFF(YY,DOB,GETDATE()) >=18 AND DATEDIFF(YY,DOB,GETDATE()) <=20 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >20 AND DATEDIFF(YY,DOB,GETDATE()) <=25 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >25 AND DATEDIFF(YY,DOB,GETDATE()) <=30 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >30 AND DATEDIFF(YY,DOB,GETDATE()) <=35 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >35 AND DATEDIFF(YY,DOB,GETDATE()) <=40 THEN 1            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >40 AND DATEDIFF(YY,DOB,GETDATE()) <=45 THEN 1            
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >45 AND DATEDIFF(YY,DOB,GETDATE()) <=50 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >50 AND DATEDIFF(YY,DOB,GETDATE()) <=55 THEN 1             
                 WHEN DATEDIFF(YY,DOB,GETDATE()) >55 AND DATEDIFF(YY,DOB,GETDATE()) <=60 THEN 1             
            
            
            END) AS 'Count' 
        FROM
            [vwAssociateDetailsBasic] AM WITH (NOLOCK)
            INNER JOIN #ClientId Client ON Client.ClientId = AM.ClientID
            INNER JOIN #FacilityId Facility ON Facility.FacilityId = AM.FacilityID
        WHERE
            ISACTIVE = 1
            GROUP BY DOB
    )

    SELECT AgeGroup, SUM(Count) AS 'Count'
    FROM AgeCalculation
    GROUP BY AgeGroup;
    --WHERE AgeCalculation IS NOT NULL          
    --GROUP BY AgeGroup,Sequence            

    -- Add your SELECT statement or additional processing logic here
    --SELECT * FROM AgeCalculation; -- Adjust as needed
END
 

showing error - Invalid object name '#ClientId'.

while executing this procedure ,getting above error

Test this, if this serves the purpose

ALTER PROCEDURE usp_AssociateAgeingDashBoard
    @intMappingId AS BIGINT,
    @intClientId BIGINT = 0,
    @intFacilityId BIGINT = 0
AS
BEGIN
    SET NOCOUNT ON;
IF Object_id('tempdb..#ClientId') is not null drop table #ClientId
CREATE TABLE #ClientId                                       
(                                      
ClientId int                                      
)                                      
IF @intClientId=0                                      
BEGIN                                       
 INSERT INTO #ClientId(ClientId)                                      
 SELECT ClientID FROM EmployeeClientMapping WITH (NOLOCK) WHERE EmployeeId =@intMappingId AND IsActive =1                                      
END                                       
ELSE                                      
BEGIN                                       
 INSERT INTO #ClientId(ClientId) VALUES (@intClientId)                                      
END                                      
     IF Object_id('tempdb..#FacilityId') is not null drop table #FacilityId                               
CREATE TABLE #FacilityId                                       
(                                      
FacilityId int                                      
)                                      
IF @intFacilityId=0                                      
BEGIN                                       
 INSERT INTO #FacilityId(FacilityId)                                      
 SELECT FacilityId FROM EmployeeFacilityMapping WITH (NOLOCK) WHERE EmployeeId =@intMappingId AND IsActive =1                                      
END                                       
ELSE                                      
BEGIN                                       
 INSERT INTO #FacilityId(FacilityId) VALUES (@intFacilityId)                                      
END    
    WITH AgeCalculation AS (
        SELECT
            CASE
                WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN '18-20'
                WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN '21-25'
                -- Add more age groups as needed
            END AS AgeGroup,
            DATEDIFF(YY, DOB, GETDATE()) AS Age
        FROM
            [vwAssociateDetailsBasic] AM WITH (NOLOCK)
            INNER JOIN #ClientId Client ON Client.ClientId = AM.ClientID
            INNER JOIN #FacilityId Facility ON Facility.FacilityId = AM.FacilityID
        WHERE
            ISACTIVE = 1
    )

    SELECT
        AgeGroup,
        SUM(1) AS [Count],
        ROW_NUMBER() OVER (ORDER BY Age) AS [Sequence]
    FROM
        AgeCalculation
    WHERE
        AgeGroup IS NOT NULL
    GROUP BY
        AgeGroup, [Sequence]
    ORDER BY
        [Sequence];
END;

SELECT
    CASE
        WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN '18-20'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN '21-25'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 25 AND DATEDIFF(YY, DOB, GETDATE()) <= 30 THEN '26-30'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 30 AND DATEDIFF(YY, DOB, GETDATE()) <= 35 THEN '31-35'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 35 AND DATEDIFF(YY, DOB, GETDATE()) <= 40 THEN '36-40'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 40 AND DATEDIFF(YY, DOB, GETDATE()) <= 45 THEN '41-45'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 45 AND DATEDIFF(YY, DOB, GETDATE()) <= 50 THEN '46-50'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 50 AND DATEDIFF(YY, DOB, GETDATE()) <= 55 THEN '51-55'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 55 AND DATEDIFF(YY, DOB, GETDATE()) <= 60 THEN '56-60'
    END AS 'AgeGroup',
    COUNT(*) AS 'Count',
    CASE
        WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN 1
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN 2
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 25 AND DATEDIFF(YY, DOB, GETDATE()) <= 30 THEN 3
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 30 AND DATEDIFF(YY, DOB, GETDATE()) <= 35 THEN 4
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 35 AND DATEDIFF(YY, DOB, GETDATE()) <= 40 THEN 5
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 40 AND DATEDIFF(YY, DOB, GETDATE()) <= 45 THEN 6
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 45 AND DATEDIFF(YY, DOB, GETDATE()) <= 50 THEN 7
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 50 AND DATEDIFF(YY, DOB, GETDATE()) <= 55 THEN 8
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 55 AND DATEDIFF(YY, DOB, GETDATE()) <= 60 THEN 9
    END AS 'Sequence'
FROM
    [vwAssociateDetailsBasic] AM WITH (NOLOCK)
    INNER JOIN #ClientId Client ON Client.ClientId = AM.ClientID
    INNER JOIN #FacilityId Facility ON Facility.FacilityId = AM.FacilityID
WHERE
    ISACTIVE = 1
GROUP BY
    CASE
        WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN '18-20'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN '21-25'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 25 AND DATEDIFF(YY, DOB, GETDATE()) <= 30 THEN '26-30'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 30 AND DATEDIFF(YY, DOB, GETDATE()) <= 35 THEN '31-35'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 35 AND DATEDIFF(YY, DOB, GETDATE()) <= 40 THEN '36-40'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 40 AND DATEDIFF(YY, DOB, GETDATE()) <= 45 THEN '41-45'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 45 AND DATEDIFF(YY, DOB, GETDATE()) <= 50 THEN '46-50'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 50 AND DATEDIFF(YY, DOB, GETDATE()) <= 55 THEN '51-55'
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 55 AND DATEDIFF(YY, DOB, GETDATE()) <= 60 THEN '56-60'
    END,
    CASE
        WHEN DATEDIFF(YY, DOB, GETDATE()) >= 18 AND DATEDIFF(YY, DOB, GETDATE()) <= 20 THEN 1
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 20 AND DATEDIFF(YY, DOB, GETDATE()) <= 25 THEN 2
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 25 AND DATEDIFF(YY, DOB, GETDATE()) <= 30 THEN 3
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 30 AND DATEDIFF(YY, DOB, GETDATE()) <= 35 THEN 4
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 35 AND DATEDIFF(YY, DOB, GETDATE()) <= 40 THEN 5
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 40 AND DATEDIFF(YY, DOB, GETDATE()) <= 45 THEN 6
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 45 AND DATEDIFF(YY, DOB, GETDATE()) <= 50 THEN 7
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 50 AND DATEDIFF(YY, DOB, GETDATE()) <= 55 THEN 8
        WHEN DATEDIFF(YY, DOB, GETDATE()) > 55 AND DATEDIFF(YY, DOB, GETDATE()) <= 60 THEN 9
    END
ORDER BY
    Sequence