How Can I Optimize My Complex Sp ?

Question

0.00/5 (No votes)

See more:

Hi ,

I am using one complex procedure to bind data to google chart , but when i have created this i was working with limited amount of data and had no issue . but now data has got increase and the sp has started taking so much time . alsmost taking 9 minutes to bind the data. I tied basic ways to optmize this query , perhaps its not working

the problem i am having is withn @ObjectivesStatus varchar(max),

this paramter , when i go for all status it works well but when i try to filter the statys it starts giving me trouble to load

please suggest

ALTER procedure [dbo].[usp_GetObjectivesDetailsForGoogleChart]     
(      
 @DataForYear    int,      
 @CompanyId     int,      
 @DateFilter     varchar(max),      
 @MoreThanLessThanStatus varchar(max),      
 @MoreThanLessThanValue   int,      
 @ObjectivesStatus   varchar(max),      
 @ObjectivePeriod   varchar(max)      
)      
As       
  SET NOCOUNT ON;    
Declare @IncludeTestEmployeeInReport bit          
 Declare @IncludeHRAdminInReport bit          
      
 set @IncludeTestEmployeeInReport=isnull((Select IncludeTestEmployeeInReport from Company where id=@CompanyId),0)          
 set @IncludeHRAdminInReport=isnull((Select IncludeHRAdminInReport from Company where id=@CompanyId),0)       
    
-- get period    
DECLARE @businessYearStart  AS DATE          
DECLARE @businessYearEnd AS DATE          
    
IF(@DateFilter = 'current business year')    
begin    
 EXEC usp_GetCompanyBusinessYearPeriod           
  @compID    = @CompanyId,          
  @DataForYear  = @DataForYear,          
  @businessYear_end = @businessYearEnd OUTPUT,          
  @businessYear_start = @businessYearStart OUTPUT    
end    
else    
begin    
 IF(@DateFilter = 'this month')    
 begin    
  DECLARE @Month int    
  DECLARE @Year int    
    
  set @Month = DATEPART(M, GETDATE())    
  set @Year = DATEPART(YYYY, GETDATE())    
  set @businessYearStart = (select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0))) /*First*/    
  set @businessYearEnd = (select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))) /*Last*/    
    
 end    
 else    
 BEGIN    
  IF(@DateFilter = 'this week')    
  BEGIN    
    
   SELECT  @businessYearStart =  DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) , /*[WeekStart]*/    
   @businessYearEnd = DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))  /* [WeekEnd] */    
    
  end    
 end    
end    
    
Declare @empAllStatusObjectives as Table    
(    
 empId int,    
 objectiveid varchar(max)    
)    
-- get employees objectives    
DECLARE @empObjectives as table    
(    
 empID        int,    
 TotalObjectivesLive    int,    
 TotalObjectivesDraft   int,    
 TotalObjectivesCompleted  int,    
 ObjectivesIDsLive    varchar(max),    
 ObjectivesIDsDraft    varchar(max),    
 ObjectivesIDsCompleted   varchar(max),    
 ObjectiveLiveStatusIds   varchar(max),    
 ObjectivesDraftStatusIds  varchar(max),    
 ObjectiveCompletedStatusIds varchar(max)    
)    
    
INSERT INTO @empObjectives    
SELECT     
 e1.Id,    
    
 (SELECT COUNT(*) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (1)) or (t1.Objective_Status=1))    
  AND (    
   ((@ObjectivePeriod = 'either' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    OR (@ObjectivePeriod = 'either' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd))    
    
   OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    
   OR (@ObjectivePeriod = 'target date' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd)    
   )    
  ),    
    
  (SELECT COUNT(*) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (2)) or (t1.Objective_Status=2))    
  AND (    
   ((@ObjectivePeriod = 'either' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    OR (@ObjectivePeriod = 'either' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd))    
    
   OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    
   OR (@ObjectivePeriod = 'target date' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd)    
   )    
  ),    
    
  (SELECT COUNT(*) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (3)) or (t1.Objective_Status=3))    
  AND (    
   ((@ObjectivePeriod = 'either' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    OR (@ObjectivePeriod = 'either' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd))    
    
   OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] &gt;= @businessYearStart AND t1.[TimeStamp] &lt;= @businessYearEnd)    
    
   OR (@ObjectivePeriod = 'target date' and t1.CompetionDate &gt;= @businessYearStart AND t1.CompetionDate &lt;= @businessYearEnd)    
   )    
  ),    
    
    
 STUFF(    
  (SELECT ',' + cast(t1.Id as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (1)) or (t1.Objective_Status=1))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, ''),    
    
  STUFF(    
  (SELECT ',' + cast(t1.Id as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (2)) or (t1.Objective_Status=2))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, ''),    
    
  STUFF(    
  (SELECT ',' + cast(t1.Id as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (3)) or (t1.Objective_Status=3))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, ''),    
    
  STUFF(    
  (SELECT ',' + cast(t1.Objective_Status as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (1)) or (t1.Objective_Status=1))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, ''),    
    
  STUFF(    
  (SELECT ',' + cast(t1.Objective_Status as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (2)) or (t1.Objective_Status=2))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, ''),    
    
  STUFF(    
  (SELECT ',' + cast(t1.Objective_Status as varchar(5)) from dbo.Objectives as t1 WITH (nolock) WHERE t1.Comp_Id = @CompanyId and t1.ObjAddedfor_Id = e1.Id    
  AND t1.IsDeleted = 0 AND t1.IsArchived = 0    
  and ((@ObjectivesStatus=0 and t1.Objective_Status in (3)) or (t1.Objective_Status=3))    
  AND (ISNULL(t1.CompetionDate, @businessYearStart) &gt;= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) &lt;= @businessYearEnd)    
  for XML PATH(''))    
  , 1, 1, '')    
    
FROM Employee as e1 with (nolock) WHERE e1.Company_Id = @CompanyId     
    
and ((@IncludeTestEmployeeInReport &lt;&gt; 1) and (e1.Id in (Select Id from Employee with (nolock) where isnull(IsTest,0) &lt;&gt; 1           
 and Company_Id=@CompanyId))          
 or @IncludeTestEmployeeInReport=1)          
      
 and ((@IncludeHRAdminInReport &lt;&gt; 1) and (e1.Id in (Select Id from Employee with (nolock) where Role_Id &lt;&gt; 4          
 and Company_Id=@CompanyId )) or @IncludeHRAdminInReport=1)       
        
 and e1.IsDeleted = 0      
 and e1.Status_Id=0          
    
-- delete not required records    
IF (@MoreThanLessThanStatus = 'more than')    
begin    
 if(isnull(@ObjectivesStatus,0) &lt;&gt; 0)    
 begin    
 DELETE FROM @empObjectives     
 WHERE  (((@ObjectivesStatus=1) and (TotalObjectivesLive &lt;= @MoreThanLessThanValue))     
 OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft &lt;= @MoreThanLessThanValue))    
 OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted &lt;= @MoreThanLessThanValue)))    
 end    
 else    
 begin    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsLive)    
 from @empObjectives where TotalObjectivesLive &gt; @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsDraft )    
 from @empObjectives where TotalObjectivesDraft &gt; @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsCompleted)    
 from @empObjectives where TotalObjectivesCompleted &gt; @MoreThanLessThanValue    
        
 end    
end    
else     
BEGIN    
 IF (@MoreThanLessThanStatus = 'less than')    
 BEGIN    
 if(isnull(@ObjectivesStatus,0) &lt;&gt; 0)    
 begin    
 DELETE FROM @empObjectives     
 WHERE  (((@ObjectivesStatus=1) and (TotalObjectivesLive &gt;= @MoreThanLessThanValue))     
 OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft &gt;= @MoreThanLessThanValue))    
 OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted &gt;= @MoreThanLessThanValue)))    
 end    
 else    
 begin    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsLive)       
 from @empObjectives where TotalObjectivesLive &lt; @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsDraft)  
 from @empObjectives where TotalObjectivesDraft &lt; @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsCompleted)    
 from @empObjectives where TotalObjectivesCompleted &lt; @MoreThanLessThanValue    
     
 end    
 end    
 else    
 BEGIN    
 if(isnull(@ObjectivesStatus,0) &lt;&gt; 0)    
 begin    
 DELETE FROM @empObjectives     
 WHERE  (((@ObjectivesStatus=1) and (TotalObjectivesLive &lt;&gt; @MoreThanLessThanValue))     
 OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft &lt;&gt; @MoreThanLessThanValue))    
 OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted &lt;&gt; @MoreThanLessThanValue)))    
 end    
 else    
 begin    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsLive)     
 from @empObjectives where TotalObjectivesLive = @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsDraft)     
 from @empObjectives where TotalObjectivesDraft = @MoreThanLessThanValue    
    
 insert into @empAllStatusObjectives     
 select empid , convert(varchar(max),ObjectivesIDsCompleted)    
 from @empObjectives where TotalObjectivesCompleted = @MoreThanLessThanValue    
 end    
 end    
end    
    
if(@ObjectivesStatus=0)    
begin    
select    
 (CASE obj.Objective_Status    
 WHEN 1 THEN 'Live'    
 WHEN 2 THEN 'Draft'    
 WHEN 3 THEN 'Completed'    
 END) AS 'Objective_Status',    
    
 COUNT(*) 'Percentage',    
    
 (CASE obj.Objective_Status WHEN 1 THEN 2 WHEN 2 THEN 1 When 3 then 3 END) as OrderOfObjectives    
     
FROM dbo.Objectives as obj with (nolock)     
WHERE obj.Id IN (SELECT * FROM dbo.Split(    
 (select STUFF((select ',' + objectiveid from @empAllStatusObjectives for xml path ('')), 1, 1, '')) ,','))    
 and obj.Objective_Status in (1, 2, 3)  
group by obj.Objective_Status    
order BY OrderOfObjectives    
end    
    
else    
begin    
SELECT    
    CASE obj.Objective_Status    
        WHEN 1 THEN 'Live'    
        WHEN 2 THEN 'Draft'    
        WHEN 3 THEN 'Completed'    
    END AS 'Objective_Status',    
    
    COUNT(*) 'Percentage',    
    
    CASE obj.Objective_Status     
        WHEN 1 THEN 2     
        WHEN 2 THEN 1     
        WHEN 3 THEN 3     
    END as OrderOfObjectives    
FROM     
    dbo.Objectives as obj with (nolock)    
WHERE     
    Exists    
    (    
        SELECT 1    
        FROM @empObjectives As EO    
        CROSS APPLY dbo.Split(ObjectivesIDsLive, ',') As ID    
        WHERE ID.Items = obj.Id    
  and obj.Objective_Status=@ObjectivesStatus    
    )    
Or    
    Exists    
    (    
        SELECT 1    
        FROM @empObjectives As EO    
        CROSS APPLY dbo.Split(ObjectivesIDsDraft, ',') As ID    
        WHERE ID.Items = obj.Id    
  and obj.Objective_Status=@ObjectivesStatus    
    )    
Or    
    Exists    
    (    
        SELECT 1    
        FROM @empObjectives As EO    
        CROSS APPLY dbo.Split(ObjectivesIDsCompleted, ',') As ID    
        WHERE ID.Items = obj.Id    
  and obj.Objective_Status=@ObjectivesStatus    
    )    
 and obj.Objective_Status=@ObjectivesStatus    
GROUP BY     
    obj.Objective_Status    
ORDER BY     
    OrderOfObjectives    
;    
end    

GO</pre>

Posted 17-Jun-15 23:32pm

Torakami

Updated 17-Jun-15 23:33pm

v2

Add a Solution

Comments

John C Rayan 18-Jun-15 7:33am

To test , remove the following bit and run & see how it responds. I think this is your bottleneck, you have to write this better without lot of subqueries,

if(@ObjectivesStatus=0)
begin
select
(CASE obj.Objective_Status
WHEN 1 THEN 'Live'
WHEN 2 THEN 'Draft'
WHEN 3 THEN 'Completed'
END) AS 'Objective_Status',

COUNT(*) 'Percentage',

(CASE obj.Objective_Status WHEN 1 THEN 2 WHEN 2 THEN 1 When 3 then 3 END) as OrderOfObjectives

FROM dbo.Objectives as obj with (nolock)
WHERE obj.Id IN (SELECT * FROM dbo.Split(
(select STUFF((select ',' + objectiveid from @empAllStatusObjectives for xml path ('')), 1, 1, '')) ,','))
and obj.Objective_Status in (1, 2, 3)
group by obj.Objective_Status
order BY OrderOfObjectives
end

Also without this bit

begin
SELECT
CASE obj.Objective_Status
WHEN 1 THEN 'Live'
WHEN 2 THEN 'Draft'
WHEN 3 THEN 'Completed'
END AS 'Objective_Status',

COUNT(*) 'Percentage',

CASE obj.Objective_Status
WHEN 1 THEN 2
WHEN 2 THEN 1
WHEN 3 THEN 3
END as OrderOfObjectives
FROM
dbo.Objectives as obj with (nolock)
WHERE
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsLive, ',') As ID
WHERE ID.Items = obj.Id
and obj.Objective_Status=@ObjectivesStatus
)
Or
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsDraft, ',') As ID
WHERE ID.Items = obj.Id
and obj.Objective_Status=@ObjectivesStatus
)
Or
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsCompleted, ',') As ID
WHERE ID.Items = obj.Id
and obj.Objective_Status=@ObjectivesStatus
)
and obj.Objective_Status=@ObjectivesStatus
GROUP BY
obj.Objective_Status
ORDER BY
OrderOfObjectives
;
end

Sreekanth Mothukuru 18-Jun-15 8:58am

I see there are lot of string comparisons in the sub query where clause. You need to eliminate string comparison with some integer values to improve the performance.

@ObjectivePeriod = 'either'
@ObjectivePeriod = 'either'
@ObjectivePeriod = 'created'
@ObjectivePeriod = 'target date'

Also, there are separate queries for each column that would execute for each different row which will degrade the query performance. Try to isolate column retrieval logic.

Torakami 18-Jun-15 11:36am

I tried other things as much as i can , set searching on integer insted of string and all ...

but now

--WHERE
-- Exists
-- (
-- SELECT 1
-- FROM @empObjectives As EO
-- CROSS APPLY dbo.Split(ObjectivesIDsLive, ',') As ID
-- WHERE ID.Items = obj.Id
-- and obj.Objective_Status=@ObjectivesStatus
-- )
--Or
-- Exists
-- (
-- SELECT 1
-- FROM @empObjectives As EO
-- CROSS APPLY dbo.Split(ObjectivesIDsDraft, ',') As ID
-- WHERE ID.Items = obj.Id
-- and obj.Objective_Status=@ObjectivesStatus
-- )
--Or
-- Exists
-- (
-- SELECT 1
-- FROM @empObjectives As EO
-- CROSS APPLY dbo.Split(ObjectivesIDsCompleted, ',') As ID
-- WHERE ID.Items = obj.Id
-- and obj.Objective_Status=@ObjectivesStatus
-- )
-- and obj.Objective_Status=@ObjectivesStatus

this part of code is causing an issue .. this is taking so much time .. and not getting what will b b the best way to achive this by also optimizing the performance ..

please suggest

Sreekanth Mothukuru 19-Jun-15 2:46am

Try to split the ObjectivesIDsLive, ObjectivesIDsDraft and ObjectivesIDsCompleted before doing cross apply.

Also if possible create a cluster or non cluster index on the table type empObjectives

VishwaKL 24-Jun-15 0:19am

Check which part is taking more execution time using SQL Execution plan option, just try to figure out for the solution to avoid more time.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)