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)))
set @businessYearEnd = (select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))))
end
else
BEGIN
IF(@DateFilter = 'this week')
BEGIN
SELECT @businessYearStart = DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) ,
@businessYearEnd = DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))
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] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'either' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @businessYearEnd))
OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'target date' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @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] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'either' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @businessYearEnd))
OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'target date' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @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] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'either' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @businessYearEnd))
OR (@ObjectivePeriod = 'created' and t1.[TimeStamp] >= @businessYearStart AND t1.[TimeStamp] <= @businessYearEnd)
OR (@ObjectivePeriod = 'target date' and t1.CompetionDate >= @businessYearStart AND t1.CompetionDate <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @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) >= @businessYearStart and ISNULL(t1.CompetionDate, @businessYearStart) <= @businessYearEnd)
for XML PATH(''))
, 1, 1, '')
FROM Employee as e1 with (nolock) WHERE e1.Company_Id = @CompanyId
and ((@IncludeTestEmployeeInReport <> 1) and (e1.Id in (Select Id from Employee with (nolock) where isnull(IsTest,0) <> 1
and Company_Id=@CompanyId))
or @IncludeTestEmployeeInReport=1)
and ((@IncludeHRAdminInReport <> 1) and (e1.Id in (Select Id from Employee with (nolock) where Role_Id <> 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) <> 0)
begin
DELETE FROM @empObjectives
WHERE (((@ObjectivesStatus=1) and (TotalObjectivesLive <= @MoreThanLessThanValue))
OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft <= @MoreThanLessThanValue))
OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted <= @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
else
BEGIN
IF (@MoreThanLessThanStatus = 'less than')
BEGIN
if(isnull(@ObjectivesStatus,0) <> 0)
begin
DELETE FROM @empObjectives
WHERE (((@ObjectivesStatus=1) and (TotalObjectivesLive >= @MoreThanLessThanValue))
OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft >= @MoreThanLessThanValue))
OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted >= @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
else
BEGIN
if(isnull(@ObjectivesStatus,0) <> 0)
begin
DELETE FROM @empObjectives
WHERE (((@ObjectivesStatus=1) and (TotalObjectivesLive <> @MoreThanLessThanValue))
OR ((@ObjectivesStatus=2) and (TotalObjectivesDraft <> @MoreThanLessThanValue))
OR ((@ObjectivesStatus=3) and (TotalObjectivesCompleted <> @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>