Performance Tuning for the procedure
-
Hi All,
I need your urgent help regarding this procedure performance tuning so that the execution time become very less.
Below is the procedure :
--PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II '12/13/2012','12/15/2012','%','%','%','%',1,1,1,1,1,1,1,0
--sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS '10/01/2012','10/20/2012','All','all','%','%',1,0,1,1,1,1,1,0
CREATE PROCEDURE [dbo].[PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II](
@pStartDate VARCHAR(10),
@pEndDate VARCHAR(10),
@pRequestorsPool VARCHAR(255),
@pRegion VARCHAR(255),
@pPC VARCHAR(255),
@pProjectPool VARCHAR(255),
@pRequestStatusAccepted int,
@pRequestStatusCancelled int,
@pRequestStatusDeclined int,
@pRequestStatusDenied int,
@pRequestStatusTimeout int,
@pRequestStatusWaitingConfirm int,
@pRequestStatusWaitingReply int,
@pinternalRegion int
)
as
BEGIN
SET NOCOUNT ON
create table #internal
(
RequestOID varchar (50)
)
create table #regiontable
(
requestoid numeric(19, 0),
ReceivedDatetime datetime,
Region varchar (500),
recipientName varchar(500)
)
create table #pctable
(
requestoid numeric(19, 0),
ReceivedDatetime datetime,
personname varchar (500),
recipientsPoolName varchar(800),
inbox varchar(200)
)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rrm_temp]') AND type in (N'U'))
DROP TABLE [dbo].[rrm_temp]
CREATE TABLE #results (
RequestOID numeric(19, 0),
ProjectPool varchar (500),
Region varchar (500),
PC varchar (500),
RequestID varchar (50),
Creator varchar (500),
CreatorsPool varchar (500),
CreateDate datetime,
Client varchar (500),
Project varchar (500),
Position varchar (500),
PositionOID numeric(19, 0),
FunctionalRole varchar (500),
RequestStatus varchar (100),
PositionCreated datetime,
Initiation datetime,
Completed datetime,
ResourceSuggested datetime,
StaffingAssignmentStatus varchar(100),
StaffingAssignmentResource varchar(500),
CommitmentStartDate datetime,
DaysToSuggested int,
SuggestedInTime int,
DaysToAccepted int,
DaysToCommitment int,
DaysToSuggestedIndicator int,
DaysToAcceptedIndicator int,
RequestCreatedInTime int,
RecipientName varchar (500),
Recipient_First_Action VARCHAR(500),
Recipient_Last_Action VARCHAR(500),
DaysSince_First_Action int,
DaysSince_Last_Action int,
Request_First_Action VARCHAR(500),
DaysSince_Request_First_Action int,
OwnerPoolsName varchar (500),
RecipientDaysToSuggested int,
RecipientDaysToAccepted int,
RecipientStatus varchar (500),
ResipientCreateDate datetime,
RecipientRespondedDate datetime,
RequestTimedoutDate DATETIME,
ReceivedDatetime DATETIME
)
CREATE TABLE #temp_results (
RequestOID numeric(19, 0),
ProjectPool varchar (500),
Region varchar (500),
PC varchar (500),
RequestID varchar (50),
Creator varchar (500),
CreatorsPool varchar (500),
CreateDate datetime,
Client varchar (500),
Project varchar (500),
Position varchar (500),
PositionOID numeric(19, 0),
FunctionalRole varchar (500),
RequestStatus varchar (100),
PositionCreated datetime,
Initiation datetime,
Completed datetime,
ResourceSuggested datetime,
StaffingAssignmentStatus varchar(100),
StaffingAssignmentResource varchar(500),
CommitmentStartDate datetime,
DaysToSuggested int,
SuggestedInTime int,
DaysToAccepted int,
DaysToCommitment int,
DaysToSuggestedIndicator int,
DaysToAcceptedIndicator int,
RequestCreatedInTime int,
RecipientName varchar (500),
Recipient_First_Action VARCHAR(500),
Recipient_Last_Action VARCHAR(500),
DaysSince_First_Action int,
DaysSince_Last_Action int,
Request_First_Action VARCHAR(500),
DaysSince_Request_First_Action int,
OwnerPoolsName varchar (500),
RecipientDaysToSuggested int,
RecipientDaysToAccepted int,
RecipientStatus varchar (500),
ResipientCreateDate datetime,
RecipientRespondedDate datetime,
RequestTimedoutDate DATETIME,
ReceivedDatetime DATETIME
)
CREATE TABLE #leadtime (
RequestOID numeric(19, 0),
ProjectPool varchar (500),
Region varchar (500),
PC varchar (500),
RequestID varchar (50),
Creator varchar (500),
CreatorsPool varchar (500),
CreateDate datetime,
Client varchar (500),
Project varchar (500),
Position varchar (500),
PositionOID numeric(19, 0),
FunctionalRole varchar (500),
RequestStatus varchar (100),
PositionCreated datetime,
Initiation datetime,
Completed datetime,
ResourceSuggested datetime,
StaffingAssignmentStatus varchar(100),
StaffingAssignmentResource varchar(500),
CommitmentStartDate datetime,
DaysToSuggested int,
SuggestedInTime int,
DaysToAccepted int,
DaysToCommitment int,
DaysToSuggestedIndicator int,
DaysToAcceptedIndicator int,
RequestCreatedInTime int,
RecipientName varchar (500),
Recipient_First_Action VARCHAR(500),
Recipient_Last_Action VARCHAR(500),
DaysSince_First_Action int,
DaysSince_Last_Action int,
Request_First_Action VARCHAR(500),
DaysSince_Request_First_Action int,
OwnerPoolsName varchar (500),
RecipientDaysToSuggested int,
RecipientDaysToAccepted int,
RecipientStatus varchar (500),
ResipientCreateDate datetime,
RecipientRespondedDate datetime,
RequestTimedoutDate DATETIME,
ReceivedDatetime DATETIME
)
declare @vRequestStatusAccepted VARCHAR(20),
@vRequestStatusCancelled VARCHAR(20),
@vRequestStatusDeclined VARCHAR(20),
@vRequestStatusDenied VARCHAR(20),
@vRequestStatusTimeout VARCHAR(20),
@vRequestStatusWaitingConfirm VARCHAR(20),
@vRequestStatusWaitingReply VARCHAR(20),
@vRequestCreateTime int,
@vGSRfunctionvalue VARCHAR(20)
set @vRequestCreateTime = 28
if @pRequestStatusAccepted = 1
set @vRequestStatusAccepted = 'COMPLETED_ACCEPTED'
else
set @vRequestStatusAccepted = ''
if @pRequestStatusCancelled = 1
set @vRequestStatusCancelled = 'COMPLETED_CANCELLED'
else
set @vRequestStatusCancelled = ''
if @pRequestStatusDeclined = 1
set @vRequestStatusDeclined = 'COMPLETED_DECLINED'
else
set @vRequestStatusDeclined = ''
if @pRequestStatusDenied = 1
set @vRequestStatusDenied = 'COMPLETED_DENIED'
else
set @vRequestStatusDenied = ''
if @pRequestStatusTimeout = 1
set @vRequestStatusTimeout = 'COMPLETED_TIMEDOUT'
else
set @vRequestStatusTimeout = ''
if @pRequestStatusWaitingConfirm = 1
set @vRequestStatusWaitingConfirm = 'WAITING_FOR_CONFIRM'
else
set @vRequestStatusWaitingConfirm = ''
if @pRequestStatusWaitingReply = 1
set @vRequestStatusWaitingReply = 'WAITING_FOR_REPLY'
else
set @vRequestStatusWaitingReply = ''
set @pRequestorsPool = ltrim(rtrim(@pRequestorsPool))
if @pRequestorsPool like ''
set @pRequestorsPool = '%'
set @pProjectPool = ltrim(rtrim(@pProjectPool))
if @pProjectPool like ''
set @pProjectPool = '%'
set @pPC = ltrim(rtrim(@pPC))
if @pPC like ''
set @pPC = '%'
set @pRegion = ltrim(rtrim(@pRegion))
CREATE CLUSTERED INDEX IX_temp_results ON #temp_results
( RequestOID )
CREATE NONCLUSTERED INDEX IX_temp_results2 ON #temp_results
(RequestOID,recipientName) --,Region)
CREATE NONCLUSTERED INDEX IX_leadtime ON #leadtime
(
RequestOID,ProjectPool)
CREATE NONCLUSTERED INDEX IX_results ON #results
(
requestid,projectpool)
CREATE NONCLUSTERED INDEX IX_region ON #regiontable
(
requestoid,receiveddatetime)
CREATE NONCLUSTERED INDEX IX_region ON #pctable
(
requestoid,recipientsPoolName,ReceivedDatetime
)
select * into #StaffingRequest from STAFFINGREQUEST sr WITH (NOLOCK)
WHERE sr.requestedDatetime >= @pStartDate
AND sr.requestedDatetime <= dateadd(day,1,@pEndDate) -- add one day so that the day is included (12 am is default)
AND sr.overallstatus IN (@vRequestStatusAccepted,@vRequestStatusCancelled,@vRequestStatusDeclined,@vRequestStatusDenied,@vRequestStatusTimeout,@vRequestStatusWaitingConfirm,@vRequestStatusWaitingReply)
select * into #ProjectTeamSlot from ProjectTeamSlot WITH (NOLOCK) where oid in
(select positionOid from StaffingAssignment where Oid in
( select requestedAssignmentOid from #StaffingRequest))
----***** Hanamesh
select Oid
,(Select appidentity from project where oid = projectoid) ProjectId
,Appidentity PositionID
,name PositionName
,sdr_StartDate PositionStartDate
,sdr_EndDate PositionEndDate
,PA.OrgName OrgName
,financialRulesOid
,projectoid
into #ProjectPosition_JR_CD
from #ProjectTeamSlot PTS --(Position Details)
,(select W.Oid FinancialOid,O.name OrgName,OrgWunitOid from OrganizationalWUnit O, CommonProjectFinancials W
where O.oid = W.OrgWunitOid)PA
where PA.FinancialOid = financialRulesOid
---- *** Hanamesh
alter table #ProjectPosition_JR_CD add CDName varchar(max)
alter table #ProjectPosition_JR_CD add JRName varchar(max)
create table #TempCD (OID varchar(19),CDName varchar(max))
---- ***** CD Hanamesh ***** -----
truncate table #TEMPCD
Declare @Cnt as Bigint
Declare @PTS_OID varchar(19)
Declare @Name varchar(max)
set @Cnt=9999
while @cnt<>0
Begin
--insert into #Temp
select top 1 @PTS_OID = PT_S.OID from assignedskill ASK,CCMAllChild CD,#ProjectTeamSlot PT_S
where ASK.Skilloid = CD.Oid and ccmflag=4
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid not in (select OID from #TempCD)
insert into #TempCD (OID) select @PTS_OID
select top 1 @Cnt = count(PT_S.OID) from assignedskill ASK,CCMAllChild CD,#ProjectTeamSlot PT_S
where ASK.Skilloid = CD.Oid and ccmflag=4
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid not in (select OID from #TempCD)
select /* distinct */
@Name = replace( replace(
(
SELECT
distinct CD.Name
from assignedskill ASK,CCMAllChild CD,#ProjectTeamSlot PT_S
where ASK.Skilloid = CD.Oid and ccmflag=4 --CD
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid = @PTS_OID
FOR XML PATH ('')
), '</Name>', char(13)), '<Name>','')
update #TempCD set CDName = replace (@Name, '&', '&') where OID = @PTS_OID
if @Cnt=0
BREAK;
End
update #ProjectPosition_JR_CD
set #ProjectPosition_JR_CD.CDName = TCD.CDName
from #TempCD TCD where TCD.OID = #ProjectPosition_JR_CD.OID
create table #TempJR (OID varchar(19),JRName nvarchar(max))
---- ***** CD Hanamesh ***** -----
truncate table #TEMPJR
set @Cnt=9999
while @cnt<>0
Begin
select top 1 @PTS_OID = PT_S.OID from assignedskill ASK,CCMAllChild JR,#ProjectTeamSlot PT_S
where ASK.Skilloid = JR.Oid and ccmflag=5
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid not in (select OID from #TempJR)
insert into #TempJR (OID) select @PTS_OID
select top 1 @Cnt = count(PT_S.OID) from assignedskill ASK,CCMAllChild JR,#ProjectTeamSlot PT_S
where ASK.Skilloid = JR.Oid and ccmflag=5
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid not in (select OID from #TempJR)
select /* distinct */
@Name = replace( replace(
(
SELECT
distinct JR.Name
from assignedskill ASK,CCMAllChild JR,#ProjectTeamSlot PT_S
where ASK.Skilloid = JR.Oid and ccmflag=5
and PT_S.Oid = ASK.EntityOwnerOid and PT_S.Oid = @PTS_OID
FOR XML PATH ('')
), '</Name>', Char(13)), '<Name>','')
update #TempJR set JRName = replace (@Name, '&', '&') where OID = @PTS_OID
if @Cnt=0
BREAK;
End
update #ProjectPosition_JR_CD
set #ProjectPosition_JR_CD.JRName = TJR.JRName
from #TempJR TJR where TJR.OID = #ProjectPosition_JR_CD.OID
insert into #results
SELECT
sr.oid as RequestOID,
MU.name as ProjectPool,
null as Region,
null as PC,
sr.appidentity as RequestID,
Requestor.personName as Creator,
RequestorsPool.name as CreatorsPool,
sr.creationDateTime as CreateDate,
Client.name as Client,
Project.name as Project,
position.name as Position,
position.oid as PositionOID,
null as FunctionalRole,
sr.overallstatus as RequestStatus,
position.creationDateTime as PositionCreated,
sr.requestedDatetime as Initiation,
sr.completedDatetime as Completed,
null as ResourceSuggested,
case
when (employee.name is not null and pc.ischangerequested=1) then 'Change Requested'
when employee.name is not null then 'Specific'
else 'General'
end as StaffingAssignmentStatus,
employee.name as StaffingAssignmentResource,
sa.dateRange_StartDate as CommitmentStartDate,
null as DaysToSuggested,
null as SuggestedInTime,
null as DaysToAccepted,
null as DaysToCommitment,
null as DaysToSuggestedIndicator,
null as DaysToAcceptedIndicator,
null as RequestCreatedInTime,
Recipient.personName as RecipientName,
ISNULL((select first_action FROM sr_first_action(sr.oid,Recipient.oid)),'No Action') Recipient_First_Action,
ISNULL((select last_action FROM sr_last_action(sr.oid,Recipient.oid)),'No Action') Recipient_Last_Action,
ISNULL((select DATEDIFF(day, sr.requestedDatetime, historydatetime) FROM sr_first_action(sr.oid,Recipient.oid)),0) DaysSince_First_Action,
ISNULL((select DATEDIFF(day, sr.requestedDatetime, historydatetime) FROM sr_last_action(sr.oid,Recipient.oid)),0) DaysSince_Last_Action,
(select first_action FROM sr_first_action_Request(sr.oid)) Request_First_Action,
(select DATEDIFF(day, sr.requestedDatetime, historydatetime) FROM sr_first_action_Request(sr.oid)) DaysSince_Request_First_Action,
pool.Name as OwnerPoolsName,
null as RecipientDaysToSuggested,
null as RecipientDaysToAccepted,
CASE srt.Status
WHEN 'ACCEPTED' THEN 'Accepted'
WHEN 'APPROVED' THEN 'Approved'
WHEN 'CANCELLED' THEN 'Cancelled'
WHEN 'CONFIRMATION_TIMEDOUT' THEN 'Confirmation_Timedout'
WHEN 'DECLINED' THEN 'Declined'
WHEN 'DENIED' THEN 'Denied'
WHEN 'REQUEST_TIMEDOUT' THEN 'Request_Timedout'
WHEN 'REQUESTED' THEN 'Requested'
WHEN 'SUGGESTED' THEN 'Suggested'
END RecipientStatus,
srt.creationDateTime as ResipientCreateDate,
srt.respondedDateTime as RecipientRespondedDate,
sr.timedoutDateTime as RequestTimedoutDate,
srt.ReceivedDatetime as ReceivedDatetime
FROM #STAFFINGREQUEST sr WITH (NOLOCK)
JOIN STAFFINGASSIGNMENT sa WITH (NOLOCK) ON
sa.oid = sr.requestedAssignmentOID
and sa.status in (0,2,4,6,8,10) -- resource requests
JOIN #ProjectTeamSlot position WITH (NOLOCK) ON
sa.positionOID = position.oid
JOIN PROJECT Project WITH (NOLOCK) ON
Project.OID = position.ProjectOID
LEFT OUTER JOIN PROJECTCOMMITMENT pc WITH (NOLOCK) on
sa.commitmentoid=pc.oid
LEFT OUTER JOIN CLIENT Client WITH (NOLOCK) ON
Project.clientOID = Client.OID
JOIN POOL ProjectPool WITH (NOLOCK) ON
Project.parentPoolOID = ProjectPool.OID
JOIN POOL MU WITH (NOLOCK) ON
ProjectPool.poolid like MU.Poolid + '%'
and MU.poollevel = 1
JOIN evuser Requestor WITH (NOLOCK) ON
sr.requesterOID = Requestor.oid
JOIN Pool RequestorsPool WITH (NOLOCK) ON
Requestor.parentPoolOID = RequestorsPool.oid
left join employee WITH (NOLOCK) on
sa.resourceoid = employee.oid
left outer join STAFFINGREQUESTTHREAD srt WITH (NOLOCK) on
sr.OID = srt.parentOID
LEFT OUTER JOIN StaffingRequestThreadHL srthl WITH (NOLOCK)
ON srthl.StaffingRequestThreadOID=srt.oid
left outer join evuser Recipient WITH (NOLOCK) on
srt.recipientOID = Recipient.OID
left outer join OwnerEntryDLView OwnerPool WITH (NOLOCK) on
Recipient.OID = OwnerPool.entityOID
left outer join Pool pool WITH (NOLOCK) on
OwnerPool.poolID = pool.poolID
Where RequestorsPool.name like @pRequestorsPool
AND MU.name like @pProjectPool
and pool.pooltype not in (1,6)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RRMFix1]') AND type in (N'U'))
DROP TABLE [dbo].[RRMFix1]
select * into RRMFix1 from #results
/* Remove Duplicate records from #results table and loading into temp_Results*/
/* Delete Project/Opportunity Domain Pools*/
delete from #results where OwnerPoolsName not like '%_Project%Resources%' and
(OwnerPoolsName like '%_Projects_%' or OwnerPoolsName like '%_Projects')
or (OwnerPoolsName like '%_Opportunities_%' or OwnerPoolsName like '%_Opportunities')
insert into #temp_results
select distinct
RequestOID ,
ProjectPool ,
Region ,
PC ,
RequestID,
Creator ,
CreatorsPool ,
CreateDate ,
Client ,
Project ,
Position,
PositionOID ,
FunctionalRole ,
RequestStatus ,
PositionCreated ,
Initiation ,
Completed ,
ResourceSuggested ,
StaffingAssignmentStatus ,
StaffingAssignmentResource ,
CommitmentStartDate ,
DaysToSuggested ,
SuggestedInTime ,
DaysToAccepted ,
DaysToCommitment ,
DaysToSuggestedIndicator ,
DaysToAcceptedIndicator ,
RequestCreatedInTime ,
RecipientName ,
Recipient_First_Action ,
Recipient_Last_Action ,
DaysSince_First_Action ,
DaysSince_Last_Action ,
Request_First_Action ,
DaysSince_Request_First_Action ,
OwnerPoolsName ,
RecipientDaysToSuggested ,
RecipientDaysToAccepted ,
RecipientStatus ,
ResipientCreateDate ,
RecipientRespondedDate ,
RequestTimedoutDate,
ReceivedDatetime
from #results
insert into #regiontable
(requestoid,
ReceivedDatetime,
Region,
recipientName)
select
srt.parentoid as RequestOID,
srt.receivedDatetime as ReceivedDatetime,
case when (select name from pool where oid = recipient.parentpooloid) = 'Request Flows' then
(select rtrim(ltrim(substring (PERSONNAME, charindex(',', PERSONNAME)+1,len(PERSONNAME))))
from evuser where oid = srt.recipientoid) ELSE
(select name from pool where oid = recipient.parentpooloid)
end as Region,
recipient.personName as recipientName
from
STAFFINGREQUESTTHREAD srt with (nolock)
join evuser recipient with (nolock)
on recipient.oid = srt.recipientOID
join #STAFFINGREQUEST sr WITH (NOLOCK)
on sr.OID = srt.parentOID
WHERE sr.requestedDatetime >= @pStartDate AND sr.requestedDatetime <= dateadd(day,1,@pEndDate)
update #temp_results
set Region = d.Region
from
#temp_results r
join #regiontable d
on r.RequestOID = d.RequestOID and r.recipientName=d.RecipientName
/*Added by Gopi Krishna Namana for displaying results when the requests sent from Region to GSRR Function Inbox on 20111115'*/
update #temp_results
set Region = 'GSRR Function' where Region in ('Americas','APAC','EMEA')
if (@pinternalRegion=1)
begin
insert into #internal
select distinct t.requestoid from #temp_results t
join PVC_DateRequestReceivedByRegion a on
t.requestoid=a.requestoid
and a.region<>t.region
end
if (@pinternalRegion=0)
begin
delete from #temp_results
where Region not like '%' + @pRegion + '%'
end
insert into #pctable
(
requestoid ,
ReceivedDatetime ,
personname ,
recipientsPoolName ,
inbox )
select
srt.parentoid as RequestOID,
srt.receivedDatetime as ReceivedDatetime,
(select personname from evuser where oid = poolownerentryRecipient.useroid) personname,
recipientsPool.name recipientsPoolName,
case
when substring(recipient.personname ,0, charindex(',',recipient.personname)) = 'Radio Access Networks' Then 'RAN'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Media and Applications' Then 'MA'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Consulting' Then 'CON'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Customer Support' Then 'CS'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Education Center' Then 'ED'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Engagement Practices' Then 'EP'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'IP&BB and Core' Then 'IPBB'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Local Delivery' Then 'LD'
when substring(recipient.personname,0,charindex(',',recipient.personname)) = 'TP&I' Then 'TPI'
when ( recipient.personname like 'Global%') then
rtrim(ltrim(substring (recipient.personname, charindex(',', recipient.personname)+1,len(recipient.personname))))
else
substring(recipient.personname,0,charindex(',',recipient.personname))
end Inbox
from
STAFFINGREQUESTTHREAD (nolock) srt
join evuser (nolock) recipient
on recipient.oid = srt.recipientOID
join poolownerentry (nolock) poolownerentryRecipient
on recipient.oid = poolownerentryRecipient.userOID
join Pool (nolock) recipientsPool
on poolownerentryRecipient.poolOID = recipientsPool.oid
join STAFFINGREQUEST sr WITH (NOLOCK)
on sr.OID = srt.parentOID
join PVC_PrimaryCenter_GSR (nolock) GSR
on GSR.PrimaryCenterName=
case
when substring(recipient.personname ,0, charindex(',',recipient.personname)) = 'Radio Access Networks' Then 'RAN'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Media and Applications' Then 'MA'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Consulting' Then 'CON'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Customer Support' Then 'CS'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Education Center' Then 'ED'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Engagement Practices' Then 'EP'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'IP&BB and Core' Then 'IPBB'
when substring(recipient.personname,0, charindex(',',recipient.personname)) = 'Local Delivery' Then 'LD'
when substring(recipient.personname,0,charindex(',',recipient.personname)) = 'TP&I' Then 'TPI'
when ( recipient.personname like 'Global%') then
rtrim(ltrim(substring (recipient.personname, charindex(',', recipient.personname)+1,len(recipient.personname))))
else
substring(recipient.personname,0,charindex(',',recipient.personname))
end
WHERE sr.requestedDatetime >= @pStartDate
AND sr.requestedDatetime <= dateadd(day,1,@pEndDate)
update #temp_results
set PC = d.Inbox
from
#temp_results r
join #pctable d
on r.RequestOID = d.RequestOID
and r.Ownerpoolsname=d.RecipientspoolName
and r.ReceivedDatetime = d.ReceivedDatetime
END V 1.1
update #temp_results
set PC = isnull(PC,'Internal LM'),
Region = isnull(Region,'Missing Recipient')
delete from #temp_results
where PC not like @pPC + '%'
update #temp_results
set ResourceSuggested = x.historyDatetime
from
#temp_results r join
(
select
srtsuggested.parentOID,
min(suggested.historyDatetime) as historyDatetime
from
STAFFINGREQUESTTHREAD srtsuggested
join STAFFINGREQUESTTHREADHL suggested
on suggested.staffingRequestThreadOID = srtsuggested.oid
where actionType IN('SUGGESTED')
group by srtsuggested.parentOID
) x
on x.parentOID = r.RequestOID
where StaffingAssignmentStatus<>'Specific'
update #temp_results
set ResourceSuggested = x.historyDatetime
from
#temp_results r join
(
select
srtsuggested.parentOID,
min(suggested.historyDatetime) as historyDatetime
from
STAFFINGREQUESTTHREAD srtsuggested
join STAFFINGREQUESTTHREADHL suggested
on suggested.staffingRequestThreadOID = srtsuggested.oid
where actionType IN('SUGGESTED','APPROVED')
group by srtsuggested.parentOID
) x
on x.parentOID = r.RequestOID
where StaffingAssignmentStatus='Specific'
update #temp_results
set FunctionalRole = skill.name
from #temp_results r
JOIN PROJECTTEAMSLOT position ON
r.PositionOID = position.oid
JOIN ASSIGNEDSKILL ON
ASSIGNEDSKILL.entityOwnerOID = position.oid
JOIN SKILL ON
ASSIGNEDSKILL.skillOID = SKILL.oid
and skill.name like 'FR-%'
update #temp_results
set RequestStatus = case RequestStatus
when 'COMPLETED_DENIED' then 'Completed, Denied'
when 'COMPLETED_CANCELLED' then 'Completed, Cancelled'
when 'COMPLETED_DECLINED' then 'Completed, Declined'
when 'COMPLETED_TIMEDOUT' then 'Completed, TimedOut'
when 'WAITING_FOR_CONFIRM' then 'Waiting for Confirm'
when 'COMPLETED_ACCEPTED' then 'Completed, Accepted'
when 'WAITING_FOR_REPLY' then 'Waiting for Reply'
end
update #temp_results
set Completed = a.timeoutdate
from #temp_results r
join
( select
sr.oid,
min(hl.historydatetime) as timeoutdate
from
staffingrequest sr
join staffingrequesthl hl
on hl.staffingrequestoid = sr.oid
and hl.actiontype like 'COMPLETED_TIMEDOUT'
where sr.overallstatus like 'COMPLETED_TIMEDOUT'
and sr.completeddatetime is null
group by sr.oid
) a
on a.oid = r.RequestOID
where Completed is null
and RequestStatus = 'Completed, TimedOut'
update #temp_results
set DaysToCommitment = DATEDIFF(day, Initiation, CommitmentStartDate)
--"Clock should stop when either of this occurs: --1) When first recipient suggests a resource --2) When first recipient approves a resource
--3) When the request is cancelled
--4) When the request times out"
--5) When the request is accepted
--6) When the sender suggests a resource
--7) When the sender approves a resource
update #temp_results
set DaysToSuggested =
case
when RequestStatus IN ('Completed, TimedOut') then
DATEDIFF(day, Initiation, RequestTimedoutDate)
when RequestStatus IN ('Completed, Accepted','Completed, Cancelled') then
DATEDIFF(day, Initiation, Completed)
else
DATEDIFF(day, Initiation, getdate())
end
update #temp_results
set DaysToSuggested = tmp.dts,
DaysToSuggestedIndicator=1
FROM
(select requestid,
(case when Request_First_Action IN ('SUGGESTED','APPROVED') then DaysSince_Request_First_Action
end) dts
from #temp_results
where (Request_First_Action IN ('SUGGESTED','APPROVED'))
) tmp
where #temp_results.requestid=tmp.requestid
--"Clock should stop when either of this occurs:
--1) When the requests is accepted
--2) When the request is cancelled
--3) When request times out"
update #temp_results
set DaysToAccepted =
case
when RequestStatus ='Completed, Accepted' or RequestStatus ='Completed, Cancelled' then DATEDIFF(day, Initiation, Completed)
when RequestStatus ='Completed, TimedOut' then DATEDIFF(day, Initiation, RequestTimedoutDate)
else DATEDIFF(day, Initiation, getdate())
end
update #temp_results
set DaysToAcceptedIndicator =
case
when RequestStatus like 'Completed, Accepted' then 1
else 0
end
insert into #leadtime(
RequestOID, ProjectPool ,
Region ,
PC ,
RequestID,
Creator ,
CreatorsPool ,
CreateDate ,
Client ,
Project ,
Position ,
PositionOID,
FunctionalRole ,
RequestStatus ,
PositionCreated ,
Initiation ,
Completed ,
ResourceSuggested ,
StaffingAssignmentStatus ,
StaffingAssignmentResource ,
CommitmentStartDate ,
DaysToSuggested ,
SuggestedInTime,
DaysToAccepted ,
DaysToCommitment ,
DaysToSuggestedIndicator ,
DaysToAcceptedIndicator ,
RequestCreatedInTime ,
RecipientName ,
Recipient_First_Action ,
Recipient_Last_Action ,
DaysSince_First_Action ,
DaysSince_Last_Action ,
Request_First_Action ,
DaysSince_Request_First_Action ,
OwnerPoolsName ,
RecipientDaysToSuggested ,
RecipientDaysToAccepted ,
RecipientStatus ,
ResipientCreateDate ,
RecipientRespondedDate ,
RequestTimedoutDate,
ReceivedDatetime
)
select distinct RequestOID,
ProjectPool ,
Region ,
PC ,
RequestID,
Creator ,
CreatorsPool ,
CreateDate ,
Client ,
Project ,
Position ,
PositionOID,
FunctionalRole ,
RequestStatus ,
PositionCreated ,
Initiation ,
Completed ,
ResourceSuggested ,
StaffingAssignmentStatus ,
StaffingAssignmentResource ,
CommitmentStartDate ,
DaysToSuggested ,
SuggestedInTime ,
DaysToAccepted ,
DaysToCommitment ,
DaysToSuggestedIndicator ,
DaysToAcceptedIndicator ,
RequestCreatedInTime ,
RecipientName ,
Recipient_First_Action ,
Recipient_Last_Action ,
DaysSince_First_Action ,
DaysSince_Last_Action ,
Request_First_Action ,
DaysSince_Request_First_Action ,
null,
RecipientDaysToSuggested ,
RecipientDaysToAccepted ,
RecipientStatus ,
ResipientCreateDate ,
RecipientRespondedDate ,
RequestTimedoutDate,
ReceivedDatetime
from #temp_results
update #leadtime set #leadtime.OwnerPoolsName = #temp_results.OwnerPoolsName
from
#leadtime join
#temp_results
on #temp_results.RequestOID = #leadtime.RequestOID
and #temp_results.ProjectPool=#leadtime.ProjectPool
and #temp_results.Region=#leadtime.Region
and #temp_results.PC=#leadtime.PC
update #temp_results
set RequestCreatedInTime =
case
when #temp_results.DaysToCommitment >= @vRequestCreateTime then 1
--and temp_results.region=rtrim(substring(temp_results.ownerpoolsname,1,charindex('_',temp_results.ownerpoolsname)-1))
else 0
end
FROM
#leadtime join
#temp_results
on #temp_results.RequestOID = #leadtime.RequestOID
and #temp_results.ProjectPool=#leadtime.ProjectPool
and #temp_results.Region=#leadtime.Region
and #temp_results.PC=#leadtime.PC
--and #temp_results.OwnerPoolsName=#leadtime.OwnerPoolsName /* change done on 27-10-2010 debasis*/
--where #temp_results.OwnerPoolsName is not null /* change done on 27-10-2010 debasis*/
update #temp_results
set RecipientDaysToSuggested = case
when RequestStatus ='Completed, Accepted' or RequestStatus ='Completed, Cancelled' then DATEDIFF(day, Initiation, Completed)
when RecipientStatus IN ('Request_Timedout') then --ADDED
DATEDIFF(day, Initiation, RequestTimedoutDate)--ADDED
when RequestStatus ='Completed, TimedOut' then DATEDIFF(day, Initiation, RequestTimedoutDate)
else
DATEDIFF(day, Initiation, getdate())
end
update #temp_results
set RecipientDaysToSuggested = tmp.dts,
SuggestedInTime=DaysSince_First_Action --1 --- RecipientDaysToSuggestedIndicator
FROM
(select requestoid,recipientname, ProjectPool,Region,PC,
case when Recipient_First_Action IN ('SUGGESTED','APPROVED','FORWARDED') then DaysSince_First_Action
when Recipient_Last_Action IN ('SUGGESTED','APPROVED','FORWARDED') then DaysSince_Last_Action
end dts
from #temp_results
where (Recipient_First_Action IN ('SUGGESTED','APPROVED','FORWARDED') or Recipient_Last_Action IN ('SUGGESTED','APPROVED','FORWARDED'))
group by recipientname,requestoid,recipientstatus,Recipient_First_Action,Recipient_Last_Action,
DaysSince_First_Action,DaysSince_Last_Action,ProjectPool,Region,PC) tmp
where #temp_results.requestoid=tmp.requestoid
and #temp_results.ProjectPool=tmp.ProjectPool
and #temp_results.Region=tmp.Region
and #temp_results.PC=tmp.PC
and #temp_results.recipientname=tmp.recipientname
--and #temp_results.ownerpoolsname is not null /* change done on 27-10-2010 debasis*/
update #temp_results
set RecipientDaysToAccepted =
case
when RequestStatus = 'Completed, Accepted' then
DATEDIFF(day, Initiation, Completed)
when RequestStatus != 'Completed, Accepted' then
DATEDIFF(day, Initiation, getdate())
when RequestStatus = 'Completed, TimedOut' then
DATEDIFF(day, Initiation, RequestTimedoutDate)
else
0
end
truncate table #results
insert into #results
select * from #temp_results
--delete from #results where OwnerPoolsName is null and Region <> 'Missing Recipient'
update #results set OwnerPoolsName='Pool not available' where OwnerPoolsName is null
insert into StatusReader values ('Insert @pinternalRegion=1')
if (@pinternalRegion=1)
begin
delete #results from #results join #internal
on #results.requestoid=#internal.requestoid
end
truncate table TempRRMResults
insert into TempRRMResults select * from #results
--select * into TempRRMResults from #results
if (select count(1) from rrmfix1 ) = 0
begin
select
'0' as ProjectId
,'-' as PositionID
,'-' as PositionName
,getdate() as PositionStartDate
,'NA' as CDName
,'NA' as JRName
,'-' as OrgName,
--0 as RequestOID,
'-' as ProjectPool,
'-' as Region,
'-' as PC,
'' as RequestID,
'No qualifing data returned.' as Creator,
'' as CreatorsPool,
getdate() as CreateDate,
'' as Client,
'' as Project,
'' as Position,
0 as PositionOID,
'' as FunctionalRole,
'' as RequestStatus,
getdate() as Initiation,
getdate() as Completed,
getdate() as ResourceSuggested,
'' as StaffingAssignmentStatus,
'' as StaffingAssignmentResource,
getdate() as CommitmentStartDate,
0 as DaysToSuggested,
0 as SuggestedInTime,
0 as DaysToAccepted,
0 as DaysToCommitment,
0 as DaysToSuggestedIndicator,
0 as DaysToAcceptedIndicator,
0 as RequestCreatedInTime,
'' as RecipientName,
'' as Recipient_First_Action ,
'' as Recipient_Last_Action ,
0 AS DaysSince_First_Action ,
0 AS DaysSince_Last_Action ,
'' as OwnerPoolsName,
0 as RecipientDaysToSuggested,
0 as RecipientDaysToAccepted,
'' as RecipientStatus,
getdate() as ResipientCreateDate,
getdate() as RecipientRespondedDate,
getdate() as RequestTimedoutDate,
'-' as PCR,
getdate() as ReceivedDatetime
end
if (@pinternalRegion=1)
begin
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ABC]') AND type in (N'U'))
DROP TABLE [dbo].[ABC]
select * into ABC from #ProjectPosition_JR_CD
select distinct
rs.ProjectId
,rs.PositionID
,rs.PositionName
,rs.PositionStartDate
,rs.CDName
,rs.JRName
,rs.OrgName OrgName,
r.ProjectPool ,
--ProjectPool,
r.Region,
r.PC,
r.RequestID,
r.Creator,
r.CreatorsPool,
r.CreateDate,
r.Client,
r.Project,
r.Position,
r.PositionOID,
r.FunctionalRole,
r.RequestStatus,
r.Initiation,
r.Completed,
r.ResourceSuggested,
r.StaffingAssignmentStatus,
r.StaffingAssignmentResource,
r.CommitmentStartDate,
r.DaysToSuggested, r.SuggestedInTime,
r.DaysToAccepted,
r.DaysToCommitment,
r.DaysToSuggestedIndicator,
r.DaysToAcceptedIndicator,
r.RequestCreatedInTime,
r.RecipientName,
r.Recipient_First_Action ,
r.Recipient_Last_Action ,
r.DaysSince_First_Action ,
r.DaysSince_Last_Action ,
ISNULL(r.OwnerPoolsName, '') as OwnerPoolsName,
r.RecipientDaysToSuggested,
r.RecipientDaysToAccepted,
r.RecipientStatus,
r.ResipientCreateDate,
r.RecipientRespondedDate,
r.RequestTimedoutDate ,
upper(r.pc) as PCR,
r.ReceivedDateTime
from #results r
left outer join (select * from #ProjectPosition_JR_CD) rs
ON r.positionoid = rs.oid
where r.Region=@pProjectPool
else
begin
select distinct
r.ProjectId
,r.PositionID
,r.PositionName
,r.PositionStartDate
,r.CDName
,r.JRName
,r.OrgName OrgName,
--ProjectPool as
ProjectPool,
Region,
PC,
RequestID,
Creator,
CreatorsPool,
CreateDate,
Client,
Project,
Position,
PositionOID,
FunctionalRole,
RequestStatus,
Initiation,
Completed,
ResourceSuggested,
StaffingAssignmentStatus,
StaffingAssignmentResource,
CommitmentStartDate,
DaysToSuggested,
SuggestedInTime,
DaysToAccepted,
DaysToCommitment,
DaysToSuggestedIndicator,
DaysToAcceptedIndicator,
RequestCreatedInTime,
RecipientName,
Recipient_First_Action ,
Recipient_Last_Action ,
DaysSince_First_Action ,
DaysSince_Last_Action ,
ISNULL(OwnerPoolsName, '') as OwnerPoolsName,
RecipientDaysToSuggested,
RecipientDaysToAccepted,
RecipientStatus,
ResipientCreateDate,
RecipientRespondedDate,
RequestTimedoutDate ,
upper(pc) as PCR,
ReceivedDatetime
--into rrm_Temp
from #results left outer join (select * from #ProjectPosition_JR_CD) r
ON positionoid=r.oid -- AND (r.ccmflag=0 OR r.ccmflag=4)
------ UNION ALL
end
--select * from #rrm_temp
end -- proc
drop table #temp_results
DROP TABLE #leadtime
DROP TABLE #regiontable
DROP TABLE #PCtable
DROP TABLE #ProjectPosition_JR_CD
DROP TABLE #ProjectTeamSlot
DROP TABLE #StaffingRequest
SET NOCOUNT OFF
-
Welcome to SQLServerCentral.
You posted a lot of code and it will be hard to get help with your post as it is.
I recommend you to read the article How to Post Performance Problems[/url] to get better help.
Help us to help you, because just with the code, the most you will get would be best practices and not a complete solution.
-
This seems to be part of the problem posted in here
http://www.sqlservercentral.com/Forums/Topic1466855-1291-1.aspx
-
Avik Roy (6/24/2013)
Hi All,I need your urgent help regarding this procedure performance tuning so that the execution time become very less.
Below is the procedure :
I know you are brand new around here but what you posted is a mess. I ran it through a free formatter.
-- sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II
--select Oid
--,(Select appidentity from project where oid = projectoid) ProjectId
--,Appidentity PositionID
--,name PositionName
--,sdr_StartDate PositionStartDate
--,sdr_EndDate PositionEndDate
--,isnull(CName,'NA')CName
--,isnull(CCMFlag,0) CCMFlag
--,PA.OrgName OrgName
--,financialRulesOid
--,projectoid
--into #ProjectPosition_JR_CD
--from PROJECTTEAMSLOT PTS --(Position Details)
--LEFT OUTER JOIN rptProject_Position_JR_CD_View PJC
-- ON PTS.OID = PJC.ProjectTeamSlotOid
--,(select W.Oid FinancialOid,O.name OrgName,OrgWunitOid from OrganizationalWUnit O, CommonProjectFinancials W
-- where O.oid = W.OrgWunitOid)PA
--where PA.FinancialOid = financialRulesOid
--order by oid
--
--
--sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS
--PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II '12/13/2012','12/15/2012','%','%','%','%',1,1,1,1,1,1,1,0
--sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS '10/01/2012','10/20/2012','All','all','%','%',1,0,1,1,1,1,1,0
CREATE PROCEDURE [dbo].[PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II] (
@pStartDate VARCHAR(10)
,@pEndDate VARCHAR(10)
,@pRequestorsPool VARCHAR(255)
,@pRegion VARCHAR(255)
,@pPC VARCHAR(255)
,@pProjectPool VARCHAR(255)
,@pRequestStatusAccepted INT
,@pRequestStatusCancelled INT
,@pRequestStatusDeclined INT
,@pRequestStatusDenied INT
,@pRequestStatusTimeout INT
,@pRequestStatusWaitingConfirm INT
,@pRequestStatusWaitingReply INT
,@pinternalRegion INT
)
AS
BEGIN
/**
Author: Jayaraj Arulandu
Date: 1/10/2010
Function: [PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS]
Description: This report includes all requests general and specific, whether fulfilled within the MU or sent to the GSDC.
It measures the response times at several points during the process: Overall time to “Suggested/Approved” status,
Overall time to "Accepted" status, Overall MU lead-time and time to first and latest action.
Changes History:
Date ChangedBy Description
1/10/2010 Jayaraj Changed the report for Q409 release extensively.
1/23/2010 Jayaraj Added additional enhancements to this report
Promoted to Production.
01/23/10 Jayaraj Promoted to Production - Q409 Release
02/24/2011 Hemanth Excluding Project and Opportunity Pools
*/
SET NOCOUNT ON
CREATE TABLE #internal (RequestOID VARCHAR(50))
CREATE TABLE #regiontable (
requestoid NUMERIC(19, 0)
,ReceivedDatetime DATETIME
,Region VARCHAR(500)
,recipientName VARCHAR(500)
)
CREATE TABLE #pctable (
requestoid NUMERIC(19, 0)
,ReceivedDatetime DATETIME
,personname VARCHAR(500)
,recipientsPoolName VARCHAR(800)
,inbox VARCHAR(200)
)
/****** Object: Table [dbo].[rrm_temp] Script Date: 10/03/2012 16:18:08 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[rrm_temp]')
AND type IN (N'U')
)
DROP TABLE [dbo].[rrm_temp]
--Create table RRM_Parameter
--
--(pStartDate VARCHAR(10),
-- pEndDate VARCHAR(10),
-- pRequestorsPool VARCHAR(255),
-- pRegion VARCHAR(255),
-- pPC VARCHAR(255),
-- pProjectPool VARCHAR(255),
-- pRequestStatusAccepted int,
-- pRequestStatusCancelled int,
-- pRequestStatusDeclined int,
-- pRequestStatusDenied int,
-- pRequestStatusTimeout int,
-- pRequestStatusWaitingConfirm int,
-- pRequestStatusWaitingReply int,
-- pinternalRegion int
--)
--
--insert into RRM_Parameter
--values( @pStartDate
-- ,@pEndDate
-- ,@pRequestorsPool
-- ,@pRegion
-- ,@pPC
-- ,@pProjectPool
-- ,@pRequestStatusAccepted
-- ,@pRequestStatusCancelled
-- ,@pRequestStatusDeclined
-- ,@pRequestStatusDenied
-- ,@pRequestStatusTimeout
-- ,@pRequestStatusWaitingConfirm
-- ,@pRequestStatusWaitingReply
-- ,@pinternalRegion
--)
CREATE TABLE #results (
--ProjectId varchar(50)
--,PositionID varchar(50)
--,PositionName varchar(255)
--,PositionStartDate datetime
--,CName varchar(255)
--,CCMFlag int
--,OrgName varchar(255),
RequestOID NUMERIC(19, 0)
,ProjectPool VARCHAR(500)
,Region VARCHAR(500)
,PC VARCHAR(500)
,RequestID VARCHAR(50)
,Creator VARCHAR(500)
,CreatorsPool VARCHAR(500)
,CreateDate DATETIME
,Client VARCHAR(500)
,Project VARCHAR(500)
,Position VARCHAR(500)
,PositionOID NUMERIC(19, 0)
,FunctionalRole VARCHAR(500)
,RequestStatus VARCHAR(100)
,PositionCreated DATETIME
,Initiation DATETIME
,Completed DATETIME
,ResourceSuggested DATETIME
,StaffingAssignmentStatus VARCHAR(100)
,StaffingAssignmentResource VARCHAR(500)
,CommitmentStartDate DATETIME
,DaysToSuggested INT
,SuggestedInTime INT
,DaysToAccepted INT
,DaysToCommitment INT
,DaysToSuggestedIndicator INT
,DaysToAcceptedIndicator INT
,RequestCreatedInTime INT
,RecipientName VARCHAR(500)
,Recipient_First_Action VARCHAR(500)
,Recipient_Last_Action VARCHAR(500)
,DaysSince_First_Action INT
,DaysSince_Last_Action INT
,Request_First_Action VARCHAR(500)
,DaysSince_Request_First_Action INT
,OwnerPoolsName VARCHAR(500)
,RecipientDaysToSuggested INT
,RecipientDaysToAccepted INT
,RecipientStatus VARCHAR(500)
,ResipientCreateDate DATETIME
,RecipientRespondedDate DATETIME
,RequestTimedoutDate DATETIME
,ReceivedDatetime DATETIME
)
CREATE TABLE #temp_results (
--ProjectId varchar(50)
--,PositionID varchar(50)
--,PositionName varchar(255)
--,PositionStartDate datetime
--,CName varchar(255)
--,CCMFlag int
--,OrgName varchar(255),
RequestOID NUMERIC(19, 0)
,ProjectPool VARCHAR(500)
,Region VARCHAR(500)
,PC VARCHAR(500)
,RequestID VARCHAR(50)
,Creator VARCHAR(500)
,CreatorsPool VARCHAR(500)
,CreateDate DATETIME
,Client VARCHAR(500)
,Project VARCHAR(500)
,Position VARCHAR(500)
,PositionOID NUMERIC(19, 0)
,FunctionalRole VARCHAR(500)
,RequestStatus VARCHAR(100)
,PositionCreated DATETIME
,Initiation DATETIME
,Completed DATETIME
,ResourceSuggested DATETIME
,StaffingAssignmentStatus VARCHAR(100)
,StaffingAssignmentResource VARCHAR(500)
,CommitmentStartDate DATETIME
,DaysToSuggested INT
,SuggestedInTime INT
,DaysToAccepted INT
,DaysToCommitment INT
,DaysToSuggestedIndicator INT
,DaysToAcceptedIndicator INT
,RequestCreatedInTime INT
,RecipientName VARCHAR(500)
,Recipient_First_Action VARCHAR(500)
,Recipient_Last_Action VARCHAR(500)
,DaysSince_First_Action INT
,DaysSince_Last_Action INT
,Request_First_Action VARCHAR(500)
,DaysSince_Request_First_Action INT
,OwnerPoolsName VARCHAR(500)
,RecipientDaysToSuggested INT
,RecipientDaysToAccepted INT
,RecipientStatus VARCHAR(500)
,ResipientCreateDate DATETIME
,RecipientRespondedDate DATETIME
,RequestTimedoutDate DATETIME
,ReceivedDatetime DATETIME
)
CREATE TABLE #leadtime (
RequestOID NUMERIC(19, 0)
,ProjectPool VARCHAR(500)
,Region VARCHAR(500)
,PC VARCHAR(500)
,RequestID VARCHAR(50)
,Creator VARCHAR(500)
,CreatorsPool VARCHAR(500)
,CreateDate DATETIME
,Client VARCHAR(500)
,Project VARCHAR(500)
,Position VARCHAR(500)
,PositionOID NUMERIC(19, 0)
,FunctionalRole VARCHAR(500)
,RequestStatus VARCHAR(100)
,PositionCreated DATETIME
,Initiation DATETIME
,Completed DATETIME
,ResourceSuggested DATETIME
,StaffingAssignmentStatus VARCHAR(100)
,StaffingAssignmentResource VARCHAR(500)
,CommitmentStartDate DATETIME
,DaysToSuggested INT
,SuggestedInTime INT
,DaysToAccepted INT
,DaysToCommitment INT
,DaysToSuggestedIndicator INT
,DaysToAcceptedIndicator INT
,RequestCreatedInTime INT
,RecipientName VARCHAR(500)
,Recipient_First_Action VARCHAR(500)
,Recipient_Last_Action VARCHAR(500)
,DaysSince_First_Action INT
,DaysSince_Last_Action INT
,Request_First_Action VARCHAR(500)
,DaysSince_Request_First_Action INT
,OwnerPoolsName VARCHAR(500)
,RecipientDaysToSuggested INT
,RecipientDaysToAccepted INT
,RecipientStatus VARCHAR(500)
,ResipientCreateDate DATETIME
,RecipientRespondedDate DATETIME
,RequestTimedoutDate DATETIME
,ReceivedDatetime DATETIME
)
DECLARE @vRequestStatusAccepted VARCHAR(20)
,@vRequestStatusCancelled VARCHAR(20)
,@vRequestStatusDeclined VARCHAR(20)
,@vRequestStatusDenied VARCHAR(20)
,@vRequestStatusTimeout VARCHAR(20)
,@vRequestStatusWaitingConfirm VARCHAR(20)
,@vRequestStatusWaitingReply VARCHAR(20)
,@vRequestCreateTime INT
,@vGSRfunctionvalue VARCHAR(20)
SET @vRequestCreateTime = 28
IF @pRequestStatusAccepted = 1
SET @vRequestStatusAccepted = 'COMPLETED_ACCEPTED'
ELSE
SET @vRequestStatusAccepted = ''
IF @pRequestStatusCancelled = 1
SET @vRequestStatusCancelled = 'COMPLETED_CANCELLED'
ELSE
SET @vRequestStatusCancelled = ''
IF @pRequestStatusDeclined = 1
SET @vRequestStatusDeclined = 'COMPLETED_DECLINED'
ELSE
SET @vRequestStatusDeclined = ''
IF @pRequestStatusDenied = 1
SET @vRequestStatusDenied = 'COMPLETED_DENIED'
ELSE
SET @vRequestStatusDenied = ''
IF @pRequestStatusTimeout = 1
SET @vRequestStatusTimeout = 'COMPLETED_TIMEDOUT'
ELSE
SET @vRequestStatusTimeout = ''
IF @pRequestStatusWaitingConfirm = 1
SET @vRequestStatusWaitingConfirm = 'WAITING_FOR_CONFIRM'
ELSE
SET @vRequestStatusWaitingConfirm = ''
IF @pRequestStatusWaitingReply = 1
SET @vRequestStatusWaitingReply = 'WAITING_FOR_REPLY'
ELSE
SET @vRequestStatusWaitingReply = ''
SET @pRequestorsPool = ltrim(rtrim(@pRequestorsPool))
IF @pRequestorsPool LIKE ''
SET @pRequestorsPool = '%'
SET @pProjectPool = ltrim(rtrim(@pProjectPool))
IF @pProjectPool LIKE ''
SET @pProjectPool = '%'
SET @pPC = ltrim(rtrim(@pPC))
IF @pPC LIKE ''
SET @pPC = '%'
SET @pRegion = ltrim(rtrim(@pRegion))
CREATE CLUSTERED INDEX IX_temp_results ON #temp_results (RequestOID)
CREATE NONCLUSTERED INDEX IX_temp_results2 ON #temp_results (
RequestOID
,recipientName
) --,Region)
CREATE NONCLUSTERED INDEX IX_leadtime ON #leadtime (
RequestOID
,ProjectPool
)
CREATE NONCLUSTERED INDEX IX_results ON #results (
requestid
,projectpool
)
CREATE NONCLUSTERED INDEX IX_region ON #regiontable (
requestoid
,receiveddatetime
)
CREATE NONCLUSTERED INDEX IX_region ON #pctable (
requestoid
,recipientsPoolName
,ReceivedDatetime
)
SELECT *
INTO #StaffingRequest
FROM STAFFINGREQUEST sr WITH (NOLOCK)
WHERE sr.requestedDatetime >= @pStartDate
AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate) -- add one day so that the day is included (12 am is default)
AND sr.overallstatus IN (
@vRequestStatusAccepted
,@vRequestStatusCancelled
,@vRequestStatusDeclined
,@vRequestStatusDenied
,@vRequestStatusTimeout
,@vRequestStatusWaitingConfirm
,@vRequestStatusWaitingReply
)
SELECT *
INTO #ProjectTeamSlot
FROM ProjectTeamSlot WITH (NOLOCK)
WHERE oid IN (
SELECT positionOid
FROM StaffingAssignment
WHERE Oid IN (
SELECT requestedAssignmentOid
FROM #StaffingRequest
)
)
----***** Hanamesh
SELECT Oid
,(
SELECT appidentity
FROM project
WHERE oid = projectoid
) ProjectId
,Appidentity PositionID
,NAME PositionName
,sdr_StartDate PositionStartDate
,sdr_EndDate PositionEndDate
--,' ' as CDName
--,' ' as JRName
--,isnull(CName,'NA')CName
--,isnull(CCMFlag,0) CCMFlag
,PA.OrgName OrgName
,financialRulesOid
,projectoid
INTO #ProjectPosition_JR_CD
FROM #ProjectTeamSlot PTS --(Position Details)
--LEFT OUTER JOIN rptProject_Position_JR_CD_View PJC
-- ON PTS.OID = PJC.ProjectTeamSlotOid
,(
SELECT W.Oid FinancialOid
,O.NAME OrgName
,OrgWunitOid
FROM OrganizationalWUnit O
,CommonProjectFinancials W
WHERE O.oid = W.OrgWunitOid
) PA
WHERE PA.FinancialOid = financialRulesOid
---- *** Hanamesh
ALTER TABLE #ProjectPosition_JR_CD ADD CDName VARCHAR(max)
ALTER TABLE #ProjectPosition_JR_CD ADD JRName VARCHAR(max)
CREATE TABLE #TempCD (
OID VARCHAR(19)
,CDName VARCHAR(max)
)
---- ***** CD Hanamesh ***** -----
TRUNCATE TABLE #TEMPCD
DECLARE @Cnt AS BIGINT
DECLARE @PTS_OID VARCHAR(19)
DECLARE @Name VARCHAR(max)
SET @Cnt = 9999
WHILE @cnt <> 0
BEGIN
--insert into #Temp
SELECT TOP 1 @PTS_OID = PT_S.OID
FROM assignedskill ASK
,CCMAllChild CD
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = CD.Oid
AND ccmflag = 4
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid NOT IN (
SELECT OID
FROM #TempCD
)
INSERT INTO #TempCD (OID)
SELECT @PTS_OID
SELECT TOP 1 @Cnt = count(PT_S.OID)
FROM assignedskill ASK
,CCMAllChild CD
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = CD.Oid
AND ccmflag = 4
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid NOT IN (
SELECT OID
FROM #TempCD
)
SELECT /* distinct */
@Name = replace(replace((
SELECT DISTINCT CD.NAME
FROM assignedskill ASK
,CCMAllChild CD
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = CD.Oid
AND ccmflag = 4 --CD
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid = @PTS_OID
FOR XML PATH('')
), '</Name>', CHAR(13)), '<Name>', '')
--print @vorgname
--select
--Print @Name
--select @Name OrgName into #result
UPDATE #TempCD
SET CDName = replace(@Name, '&', '&')
WHERE OID = @PTS_OID
--print @Cnt
IF @Cnt = 0
BREAK;
END
UPDATE #ProjectPosition_JR_CD
SET #ProjectPosition_JR_CD.CDName = TCD.CDName
FROM #TempCD TCD
WHERE TCD.OID = #ProjectPosition_JR_CD.OID
CREATE TABLE #TempJR (
OID VARCHAR(19)
,JRName NVARCHAR(max)
)
---- ***** CD Hanamesh ***** -----
TRUNCATE TABLE #TEMPJR
--Declare @Cnt as Bigint
--Declare @PTS_OID varchar(19)
--Declare @Name varchar(max)
SET @Cnt = 9999
WHILE @cnt <> 0
BEGIN
--insert into #Temp
SELECT TOP 1 @PTS_OID = PT_S.OID
FROM assignedskill ASK
,CCMAllChild JR
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = JR.Oid
AND ccmflag = 5
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid NOT IN (
SELECT OID
FROM #TempJR
)
INSERT INTO #TempJR (OID)
SELECT @PTS_OID
SELECT TOP 1 @Cnt = count(PT_S.OID)
FROM assignedskill ASK
,CCMAllChild JR
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = JR.Oid
AND ccmflag = 5
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid NOT IN (
SELECT OID
FROM #TempJR
)
SELECT /* distinct */
@Name = replace(replace((
SELECT DISTINCT JR.NAME
FROM assignedskill ASK
,CCMAllChild JR
,#ProjectTeamSlot PT_S
WHERE ASK.Skilloid = JR.Oid
AND ccmflag = 5
AND PT_S.Oid = ASK.EntityOwnerOid
AND PT_S.Oid = @PTS_OID
FOR XML PATH('')
), '</Name>', CHAR(13)), '<Name>', '')
--print @vorgname
--select
--Print @Name
--select @Name OrgName into #result
UPDATE #TempJR
SET JRName = replace(@Name, '&', '&')
WHERE OID = @PTS_OID
--print @Cnt
IF @Cnt = 0
BREAK;
END
UPDATE #ProjectPosition_JR_CD
SET #ProjectPosition_JR_CD.JRName = TJR.JRName
FROM #TempJR TJR
WHERE TJR.OID = #ProjectPosition_JR_CD.OID
INSERT INTO #results
SELECT
--ProjectId
--,PositionID
--,PositionName
--,PositionStartDate
--,CName
--,CCMFlag
--,OrgName,
sr.oid AS RequestOID
,MU.NAME AS ProjectPool
,NULL AS Region
,NULL AS PC
,sr.appidentity AS RequestID
,Requestor.personName AS Creator
,RequestorsPool.NAME AS CreatorsPool
,sr.creationDateTime AS CreateDate
,Client.NAME AS Client
,Project.NAME AS Project
,position.NAME AS Position
,position.oid AS PositionOID
,NULL AS FunctionalRole
,sr.overallstatus AS RequestStatus
,position.creationDateTime AS PositionCreated
,sr.requestedDatetime AS Initiation
,sr.completedDatetime AS Completed
,NULL AS ResourceSuggested
,CASE
WHEN (
employee.NAME IS NOT NULL
AND pc.ischangerequested = 1
)
THEN 'Change Requested'
WHEN employee.NAME IS NOT NULL
THEN 'Specific'
ELSE 'General'
END AS StaffingAssignmentStatus
,employee.NAME AS StaffingAssignmentResource
,sa.dateRange_StartDate AS CommitmentStartDate
,NULL AS DaysToSuggested
,NULL AS SuggestedInTime
,NULL AS DaysToAccepted
,NULL AS DaysToCommitment
,NULL AS DaysToSuggestedIndicator
,NULL AS DaysToAcceptedIndicator
,NULL AS RequestCreatedInTime
,Recipient.personName AS RecipientName
,ISNULL((
SELECT first_action
FROM sr_first_action(sr.oid, Recipient.oid)
), 'No Action') Recipient_First_Action
,ISNULL((
SELECT last_action
FROM sr_last_action(sr.oid, Recipient.oid)
), 'No Action') Recipient_Last_Action
,ISNULL((
SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)
FROM sr_first_action(sr.oid, Recipient.oid)
), 0) DaysSince_First_Action
,ISNULL((
SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)
FROM sr_last_action(sr.oid, Recipient.oid)
), 0) DaysSince_Last_Action
,(
SELECT first_action
FROM sr_first_action_Request(sr.oid)
) Request_First_Action
,(
SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)
FROM sr_first_action_Request(sr.oid)
) DaysSince_Request_First_Action
,pool.NAME AS OwnerPoolsName
,NULL AS RecipientDaysToSuggested
,NULL AS RecipientDaysToAccepted
,CASE srt.STATUS
WHEN 'ACCEPTED'
THEN 'Accepted'
WHEN 'APPROVED'
THEN 'Approved'
WHEN 'CANCELLED'
THEN 'Cancelled'
WHEN 'CONFIRMATION_TIMEDOUT'
THEN 'Confirmation_Timedout'
WHEN 'DECLINED'
THEN 'Declined'
WHEN 'DENIED'
THEN 'Denied'
WHEN 'REQUEST_TIMEDOUT'
THEN 'Request_Timedout'
WHEN 'REQUESTED'
THEN 'Requested'
WHEN 'SUGGESTED'
THEN 'Suggested'
END RecipientStatus
,srt.creationDateTime AS ResipientCreateDate
,srt.respondedDateTime AS RecipientRespondedDate
,sr.timedoutDateTime AS RequestTimedoutDate
,srt.ReceivedDatetime AS ReceivedDatetime
FROM #STAFFINGREQUEST sr WITH (NOLOCK)
INNER JOIN STAFFINGASSIGNMENT sa WITH (NOLOCK) ON sa.oid = sr.requestedAssignmentOID
AND sa.STATUS IN (
0
,2
,4
,6
,8
,10
) -- resource requests
INNER JOIN #ProjectTeamSlot position WITH (NOLOCK) ON sa.positionOID = position.oid
INNER JOIN PROJECT Project WITH (NOLOCK) ON Project.OID = position.ProjectOID
LEFT JOIN PROJECTCOMMITMENT pc WITH (NOLOCK) ON sa.commitmentoid = pc.oid
LEFT JOIN CLIENT Client WITH (NOLOCK) ON Project.clientOID = Client.OID
INNER JOIN POOL ProjectPool WITH (NOLOCK) ON Project.parentPoolOID = ProjectPool.OID
INNER JOIN POOL MU WITH (NOLOCK) ON ProjectPool.poolid LIKE MU.Poolid + '%'
AND MU.poollevel = 1
INNER JOIN evuser Requestor WITH (NOLOCK) ON sr.requesterOID = Requestor.oid
INNER JOIN Pool RequestorsPool WITH (NOLOCK) ON Requestor.parentPoolOID = RequestorsPool.oid
LEFT JOIN employee WITH (NOLOCK) ON sa.resourceoid = employee.oid
LEFT JOIN STAFFINGREQUESTTHREAD srt WITH (NOLOCK) ON sr.OID = srt.parentOID
LEFT JOIN StaffingRequestThreadHL srthl WITH (NOLOCK) ON srthl.StaffingRequestThreadOID = srt.oid
LEFT JOIN evuser Recipient WITH (NOLOCK) ON srt.recipientOID = Recipient.OID
LEFT JOIN OwnerEntryDLView OwnerPool WITH (NOLOCK) ON Recipient.OID = OwnerPool.entityOID
LEFT JOIN Pool pool WITH (NOLOCK) ON OwnerPool.poolID = pool.poolID
-- JOIN #ProjectPosition_JR_CD PJRC on
-- Project.oid = PJRC.projectoid
--WHERE sr.requestedDatetime >= @pStartDate
--AND sr.requestedDatetime <= dateadd(day,1,@pEndDate) -- add one day so that the day is included (12 am is default)
--AND sr.overallstatus IN (@vRequestStatusAccepted,@vRequestStatusCancelled,@vRequestStatusDeclined,@vRequestStatusDenied,@vRequestStatusTimeout,@vRequestStatusWaitingConfirm,@vRequestStatusWaitingReply)
WHERE RequestorsPool.NAME LIKE @pRequestorsPool
AND MU.NAME LIKE @pProjectPool
/*Removing Project/Opportunities Pool from the poolOwners list*/
AND pool.pooltype NOT IN (
1
,6
)
--and pool.pooltype = 2 or
--(pool.pooltype = 0 and (pool.name not like '%_Projects_%' and pool.name not like '%_Projects') )
--and (pool.pooltype = 0 and (pool.name not like '%_Opportunities_%' and pool.name not like '%_Opportunities'))
--AND MU.name not in('R&D','Request_PC Flows','Request Flows')
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[RRMFix1]')
AND type IN (N'U')
)
DROP TABLE [dbo].[RRMFix1]
SELECT *
INTO RRMFix1
FROM #results
/* Remove Duplicate records from #results table and loading into temp_Results*/
/* Delete Project/Opportunity Domain Pools*/
DELETE
FROM #results
WHERE OwnerPoolsName NOT LIKE '%_Project%Resources%'
AND (
OwnerPoolsName LIKE '%_Projects_%'
OR OwnerPoolsName LIKE '%_Projects'
)
OR (
OwnerPoolsName LIKE '%_Opportunities_%'
OR OwnerPoolsName LIKE '%_Opportunities'
)
INSERT INTO #temp_results
SELECT DISTINCT
--ProjectId
--,PositionID
--,PositionName
--,PositionStartDate
--,CName
--,CCMFlag
--,OrgName,
RequestOID
,ProjectPool
,Region
,PC
,RequestID
,Creator
,CreatorsPool
,CreateDate
,Client
,Project
,Position
,PositionOID
,FunctionalRole
,RequestStatus
,PositionCreated
,Initiation
,Completed
,ResourceSuggested
,StaffingAssignmentStatus
,StaffingAssignmentResource
,CommitmentStartDate
,DaysToSuggested
,SuggestedInTime
,DaysToAccepted
,DaysToCommitment
,DaysToSuggestedIndicator
,DaysToAcceptedIndicator
,RequestCreatedInTime
,RecipientName
,Recipient_First_Action
,Recipient_Last_Action
,DaysSince_First_Action
,DaysSince_Last_Action
,Request_First_Action
,DaysSince_Request_First_Action
,OwnerPoolsName
,RecipientDaysToSuggested
,RecipientDaysToAccepted
,RecipientStatus
,ResipientCreateDate
,RecipientRespondedDate
,RequestTimedoutDate
,ReceivedDatetime
FROM #results
--select * into RRMFix1 from #temp_results
INSERT INTO #regiontable (
requestoid
,ReceivedDatetime
,Region
,recipientName
)
SELECT srt.parentoid AS RequestOID
,srt.receivedDatetime AS ReceivedDatetime
,CASE
WHEN (
SELECT NAME
FROM pool
WHERE oid = recipient.parentpooloid
) = 'Request Flows'
THEN (
SELECT rtrim(ltrim(substring(PERSONNAME, charindex(',', PERSONNAME) + 1, len(PERSONNAME))))
FROM evuser
WHERE oid = srt.recipientoid
)
ELSE (
SELECT NAME
FROM pool
WHERE oid = recipient.parentpooloid
)
END AS Region
,recipient.personName AS recipientName
FROM STAFFINGREQUESTTHREAD srt WITH (NOLOCK)
INNER JOIN evuser recipient WITH (NOLOCK) ON recipient.oid = srt.recipientOID
INNER JOIN #STAFFINGREQUEST sr WITH (NOLOCK) ON sr.OID = srt.parentOID
WHERE sr.requestedDatetime >= @pStartDate
AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate)
UPDATE #temp_results
SET Region = d.Region
FROM #temp_results r
INNER JOIN #regiontable d ON r.RequestOID = d.RequestOID
AND r.recipientName = d.RecipientName
/*Added by Gopi Krishna Namana for displaying results when the requests sent from Region to GSRR Function Inbox on 20111115'*/
UPDATE #temp_results
SET Region = 'GSRR Function'
WHERE Region IN (
'Americas'
,'APAC'
,'EMEA'
)
/*update #temp_results
set Region = d.Region
from
#temp_results r
join [PVC_DateRequestReceivedByRegion] d
on r.RequestOID = d.RequestOID and r.recipientName=d.RecipientName */
IF (@pinternalRegion = 1)
BEGIN
INSERT INTO #internal
SELECT DISTINCT t.requestoid
FROM #temp_results t
INNER JOIN PVC_DateRequestReceivedByRegion a ON t.requestoid = a.requestoid
AND a.region <> t.region
END
IF (@pinternalRegion = 0)
BEGIN
DELETE
FROM #temp_results
WHERE Region NOT LIKE '%' + @pRegion + '%'
END
INSERT INTO #pctable (
requestoid
,ReceivedDatetime
,personname
,recipientsPoolName
,inbox
)
SELECT srt.parentoid AS RequestOID
,srt.receivedDatetime AS ReceivedDatetime
,(
SELECT personname
FROM evuser
WHERE oid = poolownerentryRecipient.useroid
) personname
,recipientsPool.NAME recipientsPoolName
,CASE
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Radio Access Networks'
THEN 'RAN'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Media and Applications'
THEN 'MA'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Consulting'
THEN 'CON'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Customer Support'
THEN 'CS'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Education Center'
THEN 'ED'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Engagement Practices'
THEN 'EP'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'IP&BB and Core'
THEN 'IPBB'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Local Delivery'
THEN 'LD'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'TP&I'
THEN 'TPI'
WHEN (recipient.personname LIKE 'Global%')
THEN rtrim(ltrim(substring(recipient.personname, charindex(',', recipient.personname) + 1, len(recipient.personname))))
ELSE substring(recipient.personname, 0, charindex(',', recipient.personname))
END Inbox
FROM STAFFINGREQUESTTHREAD(NOLOCK) srt
INNER JOIN evuser(NOLOCK) recipient ON recipient.oid = srt.recipientOID
INNER JOIN poolownerentry(NOLOCK) poolownerentryRecipient ON recipient.oid = poolownerentryRecipient.userOID
INNER JOIN Pool(NOLOCK) recipientsPool ON poolownerentryRecipient.poolOID = recipientsPool.oid
INNER JOIN STAFFINGREQUEST sr WITH (NOLOCK) ON sr.OID = srt.parentOID
INNER JOIN PVC_PrimaryCenter_GSR(NOLOCK) GSR ON GSR.PrimaryCenterName = CASE
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Radio Access Networks'
THEN 'RAN'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Media and Applications'
THEN 'MA'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Consulting'
THEN 'CON'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Customer Support'
THEN 'CS'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Education Center'
THEN 'ED'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Engagement Practices'
THEN 'EP'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'IP&BB and Core'
THEN 'IPBB'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Local Delivery'
THEN 'LD'
WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'TP&I'
THEN 'TPI'
WHEN (recipient.personname LIKE 'Global%')
THEN rtrim(ltrim(substring(recipient.personname, charindex(',', recipient.personname) + 1, len(recipient.personname))))
ELSE substring(recipient.personname, 0, charindex(',', recipient.personname))
END
WHERE sr.requestedDatetime >= @pStartDate
AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate)
UPDATE #temp_results
SET PC = d.Inbox
FROM #temp_results r
INNER JOIN #pctable d ON r.RequestOID = d.RequestOID
AND r.Ownerpoolsname = d.RecipientspoolName
AND r.ReceivedDatetime = d.ReceivedDatetime
/*update #temp_results
set PC = d.Inbox
from
#temp_results r
join [PVC_DateRequestReceivedByGSR] d
on r.RequestOID = d.RequestOID
and r.Ownerpoolsname=d.RecipientspoolName
and r.ReceivedDatetime = d.ReceivedDatetime */
/*
END V 1.1
--*/
UPDATE #temp_results
SET PC = isnull(PC, 'Internal LM')
,Region = isnull(Region, 'Missing Recipient')
/*Commented on 20111115
update #temp_results
set Region = 'GSR Function' where Region in ('Americas','APAC','EMEA')*/
DELETE
FROM #temp_results
WHERE PC NOT LIKE @pPC + '%'
UPDATE #temp_results
SET ResourceSuggested = x.historyDatetime
FROM #temp_results r
INNER JOIN (
SELECT srtsuggested.parentOID
,min(suggested.historyDatetime) AS historyDatetime
FROM STAFFINGREQUESTTHREAD srtsuggested
INNER JOIN STAFFINGREQUESTTHREADHL suggested ON suggested.staffingRequestThreadOID = srtsuggested.oid
--where actionType = 'SUGGESTED'
--Jayaraj Arulandu 11/1/09 Fix for ST0000007262668
WHERE actionType IN ('SUGGESTED')
GROUP BY srtsuggested.parentOID
) x ON x.parentOID = r.RequestOID
WHERE StaffingAssignmentStatus <> 'Specific'
-- added by Jayaraj for specific requests
UPDATE #temp_results
SET ResourceSuggested = x.historyDatetime
FROM #temp_results r
INNER JOIN (
SELECT srtsuggested.parentOID
,min(suggested.historyDatetime) AS historyDatetime
FROM STAFFINGREQUESTTHREAD srtsuggested
INNER JOIN STAFFINGREQUESTTHREADHL suggested ON suggested.staffingRequestThreadOID = srtsuggested.oid
--where actionType = 'SUGGESTED'
--Jayaraj Arulandu 11/1/09 Fix for ST0000007262668
WHERE actionType IN (
'SUGGESTED'
,'APPROVED'
)
GROUP BY srtsuggested.parentOID
) x ON x.parentOID = r.RequestOID
WHERE StaffingAssignmentStatus = 'Specific'
UPDATE #temp_results
SET FunctionalRole = skill.NAME
FROM #temp_results r
INNER JOIN PROJECTTEAMSLOT position ON r.PositionOID = position.oid
INNER JOIN ASSIGNEDSKILL ON ASSIGNEDSKILL.entityOwnerOID = position.oid
INNER JOIN SKILL ON ASSIGNEDSKILL.skillOID = SKILL.oid
AND skill.NAME LIKE 'FR-%'
UPDATE #temp_results
SET RequestStatus = CASE RequestStatus
WHEN 'COMPLETED_DENIED'
THEN 'Completed, Denied'
WHEN 'COMPLETED_CANCELLED'
THEN 'Completed, Cancelled'
WHEN 'COMPLETED_DECLINED'
THEN 'Completed, Declined'
WHEN 'COMPLETED_TIMEDOUT'
THEN 'Completed, TimedOut'
WHEN 'WAITING_FOR_CONFIRM'
THEN 'Waiting for Confirm'
WHEN 'COMPLETED_ACCEPTED'
THEN 'Completed, Accepted'
WHEN 'WAITING_FOR_REPLY'
THEN 'Waiting for Reply'
END
-- there is a known bug that sometimes the completedDatetime is not set for timed out requests
-- so this corrects for that bug for this report
UPDATE #temp_results
SET Completed = a.timeoutdate
FROM #temp_results r
INNER JOIN (
SELECT sr.oid
,min(hl.historydatetime) AS timeoutdate
FROM staffingrequest sr
INNER JOIN staffingrequesthl hl ON hl.staffingrequestoid = sr.oid
AND hl.actiontype LIKE 'COMPLETED_TIMEDOUT'
WHERE sr.overallstatus LIKE 'COMPLETED_TIMEDOUT'
AND sr.completeddatetime IS NULL
GROUP BY sr.oid
) a ON a.oid = r.RequestOID
WHERE Completed IS NULL
AND RequestStatus = 'Completed, TimedOut'
UPDATE #temp_results
SET DaysToCommitment = DATEDIFF(day, Initiation, CommitmentStartDate)
--"Clock should stop when either of this occurs: --1) When first recipient suggests a resource --2) When first recipient approves a resource --3) When the request is cancelled --4) When the request times out"
--5) When the request is accepted
--6) When the sender suggests a resource
--7) When the sender approves a resource
UPDATE #temp_results
SET DaysToSuggested = CASE
WHEN RequestStatus IN ('Completed, TimedOut')
THEN DATEDIFF(day, Initiation, RequestTimedoutDate)
WHEN RequestStatus IN (
'Completed, Accepted'
,'Completed, Cancelled'
)
THEN DATEDIFF(day, Initiation, Completed)
ELSE DATEDIFF(day, Initiation, getdate())
END
-- When First Recipient Suggests a Resource or Approves a Resource
UPDATE #temp_results
SET DaysToSuggested = tmp.dts
,DaysToSuggestedIndicator = 1
FROM (
SELECT requestid
,(
CASE
WHEN Request_First_Action IN (
'SUGGESTED'
,'APPROVED'
)
THEN DaysSince_Request_First_Action
END
) dts
FROM #temp_results
WHERE (
Request_First_Action IN (
'SUGGESTED'
,'APPROVED'
)
)
--group by requestid
) tmp
WHERE #temp_results.requestid = tmp.requestid
--update results1
--set SuggestedInTime =
-- case
-- when isnull(ResourceSuggested, -1) = -1 then 0
-- when DaysToSuggested <= 7 then 1
-- else 0
-- end
--update results1
--set DaysToSuggestedIndicator =
-- case
-- when isnull(ResourceSuggested, -1) = -1 then 0 -- else 1
-- end
--"Clock should stop when either of this occurs: --1) When the requests is accepted --2) When the request is cancelled --3) When request times out"
UPDATE #temp_results
SET DaysToAccepted =
-- case isnull(Completed, -1)
-- when -1 then DATEDIFF(day, Initiation, getdate())
-- else DATEDIFF(day, Initiation, Completed)
-- end
CASE -- added by jayaraj with requirement for accepted.
WHEN RequestStatus = 'Completed, Accepted'
OR RequestStatus = 'Completed, Cancelled'
THEN DATEDIFF(day, Initiation, Completed)
--when RequestStatus ='Completed, Cancelled'
WHEN RequestStatus = 'Completed, TimedOut'
THEN DATEDIFF(day, Initiation, RequestTimedoutDate)
ELSE DATEDIFF(day, Initiation, getdate())
END
UPDATE #temp_results
SET DaysToAcceptedIndicator = CASE
--when isnull(Completed, -1) = -1 then 0
WHEN RequestStatus LIKE 'Completed, Accepted'
THEN 1
ELSE 0
END
--update temp_results
--set RequestCreatedInTime =
-- case
-- when DaysToCommitment >= @vRequestCreateTime then 1
-- else 0
-- end
--FROM
-- (select requestid,region,
-- from temp_results
-- --group by requestid
-- ) tmp
--join PVC_PrimaryCenter_GSR GSR
--on GSR.PrimaryCenterName= substring((select personname from evuser where oid = poolownerentryRecipient.useroid),0,
-- charindex(',',(select personname from evuser where oid = poolownerentryRecipient.useroid)))
--where temp_results.requestid=tmp.requestid and temp_results.[region]=tmp.[region]
--and temp_results.ownerpoolsname is not null and
/* Distinct Values into Temp_leadtime table*/
INSERT INTO #leadtime (
RequestOID
,ProjectPool
,Region
,PC
,RequestID
,Creator
,CreatorsPool
,CreateDate
,Client
,Project
,Position
,PositionOID
,FunctionalRole
,RequestStatus
,PositionCreated
,Initiation
,Completed
,ResourceSuggested
,StaffingAssignmentStatus
,StaffingAssignmentResource
,CommitmentStartDate
,DaysToSuggested
,SuggestedInTime
,DaysToAccepted
,DaysToCommitment
,DaysToSuggestedIndicator
,DaysToAcceptedIndicator
,RequestCreatedInTime
,RecipientName
,Recipient_First_Action
,Recipient_Last_Action
,DaysSince_First_Action
,DaysSince_Last_Action
,Request_First_Action
,DaysSince_Request_First_Action
,OwnerPoolsName
,RecipientDaysToSuggested
,RecipientDaysToAccepted
,RecipientStatus
,ResipientCreateDate
,RecipientRespondedDate
,RequestTimedoutDate
,ReceivedDatetime
)
SELECT DISTINCT RequestOID
,ProjectPool
,Region
,PC
,RequestID
,Creator
,CreatorsPool
,CreateDate
,Client
,Project
,Position
,PositionOID
,FunctionalRole
,RequestStatus
,PositionCreated
,Initiation
,Completed
,ResourceSuggested
,StaffingAssignmentStatus
,StaffingAssignmentResource
,CommitmentStartDate
,DaysToSuggested
,SuggestedInTime
,DaysToAccepted
,DaysToCommitment
,DaysToSuggestedIndicator
,DaysToAcceptedIndicator
,RequestCreatedInTime
,RecipientName
,Recipient_First_Action
,Recipient_Last_Action
,DaysSince_First_Action
,DaysSince_Last_Action
,Request_First_Action
,DaysSince_Request_First_Action
,NULL
,RecipientDaysToSuggested
,RecipientDaysToAccepted
,RecipientStatus
,ResipientCreateDate
,RecipientRespondedDate
,RequestTimedoutDate
,ReceivedDatetime
FROM #temp_results
UPDATE #leadtime
SET #leadtime.OwnerPoolsName = #temp_results.OwnerPoolsName
FROM #leadtime
INNER JOIN #temp_results ON #temp_results.RequestOID = #leadtime.RequestOID
AND #temp_results.ProjectPool = #leadtime.ProjectPool
AND #temp_results.Region = #leadtime.Region
AND #temp_results.PC = #leadtime.PC
--
--select * into rrm_tempresult from #temp_results
--select * into rrm_leadtime from #leadtime
UPDATE #temp_results
SET RequestCreatedInTime = CASE
WHEN #temp_results.DaysToCommitment >= @vRequestCreateTime
THEN 1
--and temp_results.region=rtrim(substring(temp_results.ownerpoolsname,1,charindex('_',temp_results.ownerpoolsname)-1))
ELSE 0
END
FROM #leadtime
INNER JOIN #temp_results ON #temp_results.RequestOID = #leadtime.RequestOID
AND #temp_results.ProjectPool = #leadtime.ProjectPool
AND #temp_results.Region = #leadtime.Region
AND #temp_results.PC = #leadtime.PC
--and #temp_results.OwnerPoolsName=#leadtime.OwnerPoolsName /* change done on 27-10-2010 debasis*/
--where #temp_results.OwnerPoolsName is not null /* change done on 27-10-2010 debasis*/
UPDATE #temp_results
SET RecipientDaysToSuggested = CASE
WHEN RequestStatus = 'Completed, Accepted'
OR RequestStatus = 'Completed, Cancelled'
THEN DATEDIFF(day, Initiation, Completed)
WHEN RecipientStatus IN ('Request_Timedout')
THEN --ADDED
DATEDIFF(day, Initiation, RequestTimedoutDate) --ADDED
WHEN RequestStatus = 'Completed, TimedOut'
THEN DATEDIFF(day, Initiation, RequestTimedoutDate)
ELSE DATEDIFF(day, Initiation, getdate())
END
UPDATE #temp_results
SET RecipientDaysToSuggested = tmp.dts
,SuggestedInTime = DaysSince_First_Action --1 --- RecipientDaysToSuggestedIndicator
FROM (
SELECT requestoid
,recipientname
,ProjectPool
,Region
,PC
,CASE
WHEN Recipient_First_Action IN (
'SUGGESTED'
,'APPROVED'
,'FORWARDED'
)
THEN DaysSince_First_Action
WHEN Recipient_Last_Action IN (
'SUGGESTED'
,'APPROVED'
,'FORWARDED'
)
THEN DaysSince_Last_Action
END dts
FROM #temp_results
WHERE (
Recipient_First_Action IN (
'SUGGESTED'
,'APPROVED'
,'FORWARDED'
)
OR Recipient_Last_Action IN (
'SUGGESTED'
,'APPROVED'
,'FORWARDED'
)
)
GROUP BY recipientname
,requestoid
,recipientstatus
,Recipient_First_Action
,Recipient_Last_Action
,DaysSince_First_Action
,DaysSince_Last_Action
,ProjectPool
,Region
,PC
) tmp
WHERE #temp_results.requestoid = tmp.requestoid
AND #temp_results.ProjectPool = tmp.ProjectPool
AND #temp_results.Region = tmp.Region
AND #temp_results.PC = tmp.PC
AND #temp_results.recipientname = tmp.recipientname
--and #temp_results.ownerpoolsname is not null /* change done on 27-10-2010 debasis*/
UPDATE #temp_results
SET RecipientDaysToAccepted = CASE
WHEN RequestStatus = 'Completed, Accepted'
THEN DATEDIFF(day, Initiation, Completed)
WHEN RequestStatus != 'Completed, Accepted'
THEN DATEDIFF(day, Initiation, getdate())
WHEN RequestStatus = 'Completed, TimedOut'
THEN DATEDIFF(day, Initiation, RequestTimedoutDate)
ELSE 0
END
TRUNCATE TABLE #results
INSERT INTO #results
SELECT *
FROM #temp_results
--delete from #results where OwnerPoolsName is null and Region <> 'Missing Recipient'
UPDATE #results
SET OwnerPoolsName = 'Pool not available'
WHERE OwnerPoolsName IS NULL
INSERT INTO StatusReader
VALUES ('Insert @pinternalRegion=1')
IF (@pinternalRegion = 1)
BEGIN
DELETE #results
FROM #results
INNER JOIN #internal ON #results.requestoid = #internal.requestoid
END
TRUNCATE TABLE TempRRMResults
INSERT INTO TempRRMResults
SELECT *
FROM #results
--select * into TempRRMResults from #results
IF (
SELECT count(1)
FROM rrmfix1
) = 0
BEGIN
SELECT '0' AS ProjectId
,'-' AS PositionID
,'-' AS PositionName
,getdate() AS PositionStartDate
,'NA' AS CDName
,'NA' AS JRName
,'-' AS OrgName
,
--0 as RequestOID,
'-' AS ProjectPool
,'-' AS Region
,'-' AS PC
,'' AS RequestID
,'No qualifing data returned.' AS Creator
,'' AS CreatorsPool
,getdate() AS CreateDate
,'' AS Client
,'' AS Project
,'' AS Position
,0 AS PositionOID
,'' AS FunctionalRole
,'' AS RequestStatus
,getdate() AS Initiation
,getdate() AS Completed
,getdate() AS ResourceSuggested
,'' AS StaffingAssignmentStatus
,'' AS StaffingAssignmentResource
,getdate() AS CommitmentStartDate
,0 AS DaysToSuggested
,0 AS SuggestedInTime
,0 AS DaysToAccepted
,0 AS DaysToCommitment
,0 AS DaysToSuggestedIndicator
,0 AS DaysToAcceptedIndicator
,0 AS RequestCreatedInTime
,'' AS RecipientName
,'' AS Recipient_First_Action
,'' AS Recipient_Last_Action
,0 AS DaysSince_First_Action
,0 AS DaysSince_Last_Action
,'' AS OwnerPoolsName
,0 AS RecipientDaysToSuggested
,0 AS RecipientDaysToAccepted
,'' AS RecipientStatus
,getdate() AS ResipientCreateDate
,getdate() AS RecipientRespondedDate
,getdate() AS RequestTimedoutDate
,'-' AS PCR
,getdate() AS ReceivedDatetime
END
IF (@pinternalRegion = 1)
BEGIN
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ABC]')
AND type IN (N'U')
)
DROP TABLE [dbo].[ABC]
SELECT *
INTO ABC
FROM #ProjectPosition_JR_CD
SELECT DISTINCT rs.ProjectId
,rs.PositionID
,rs.PositionName
,rs.PositionStartDate
,rs.CDName
,rs.JRName
,rs.OrgName OrgName
,r.ProjectPool
,
--ProjectPool,
r.Region
,r.PC
,r.RequestID
,r.Creator
,r.CreatorsPool
,r.CreateDate
,r.Client
,r.Project
,r.Position
,r.PositionOID
,r.FunctionalRole
,r.RequestStatus
,r.Initiation
,r.Completed
,r.ResourceSuggested
,r.StaffingAssignmentStatus
,r.StaffingAssignmentResource
,r.CommitmentStartDate
,r.DaysToSuggested
,r.SuggestedInTime
,r.DaysToAccepted
,r.DaysToCommitment
,r.DaysToSuggestedIndicator
,r.DaysToAcceptedIndicator
,r.RequestCreatedInTime
,r.RecipientName
,r.Recipient_First_Action
,r.Recipient_Last_Action
,r.DaysSince_First_Action
,r.DaysSince_Last_Action
,ISNULL(r.OwnerPoolsName, '') AS OwnerPoolsName
,r.RecipientDaysToSuggested
,r.RecipientDaysToAccepted
,r.RecipientStatus
,r.ResipientCreateDate
,r.RecipientRespondedDate
,r.RequestTimedoutDate
,upper(r.pc) AS PCR
,r.ReceivedDateTime
-- into #rrm_temp
FROM #results r
LEFT JOIN (
SELECT *
FROM #ProjectPosition_JR_CD
) rs ON r.positionoid = rs.oid
WHERE r.Region = @pProjectPool
---- select distinct
---- rs.ProjectId
---- ,rs.PositionID
---- ,rs.PositionName
---- ,rs.PositionStartDate
---- ,rs.CDName
---- ,rs.JRName
---- ,rs.OrgName OrgName,
---- r.*
---- from #rrm_temp r left outer join (select * from #ProjectPosition_JR_CD) rs
---- ON r.positionoid = rs.oid
----select distinct
---- rs.ProjectId
---- ,rs.PositionID
---- ,rs.PositionName
---- ,rs.PositionStartDate
---- ,rs.CDName
---- ,rs.JRName
---- ,rs.OrgName OrgName,
----
---- r.ProjectPool ,
---- --ProjectPool,
---- r.Region,
---- r.PC,
---- r.RequestID,
---- r.Creator,
---- r.CreatorsPool,
---- r.CreateDate,
---- r.Client,
---- r.Project,
---- r.Position,
---- r.PositionOID,
---- r.FunctionalRole,
---- r.RequestStatus,
---- r.Initiation,
---- r.Completed,
---- r.ResourceSuggested,
---- r.StaffingAssignmentStatus,
---- r.StaffingAssignmentResource,
---- r.CommitmentStartDate,
---- r.DaysToSuggested, r.SuggestedInTime,
---- r.DaysToAccepted,
---- r.DaysToCommitment,
---- r.DaysToSuggestedIndicator,
---- r.DaysToAcceptedIndicator,
---- r.RequestCreatedInTime,
---- r.RecipientName,
---- r.Recipient_First_Action ,
---- r.Recipient_Last_Action ,
---- r.DaysSince_First_Action ,
---- r.DaysSince_Last_Action ,
---- ISNULL(r.OwnerPoolsName, '') as OwnerPoolsName,
---- r.RecipientDaysToSuggested,
---- r.RecipientDaysToAccepted,
---- r.RecipientStatus,
---- r.ResipientCreateDate,
---- r.RecipientRespondedDate,
---- r.RequestTimedoutDate ,
---- upper(r.pc) as PCR,
---- r.ReceivedDateTime
------ into #rrm_temp
---- from #results r left outer join (select * from #ProjectPosition_JR_CD) rs
---- ON rs.oid = r.positionoid
---- Where r.Region=@pProjectPool
END
ELSE
BEGIN
SELECT DISTINCT r.ProjectId
,r.PositionID
,r.PositionName
,r.PositionStartDate
,r.CDName
,r.JRName
,r.OrgName OrgName
,
--ProjectPool as
ProjectPool
,Region
,PC
,RequestID
,Creator
,CreatorsPool
,CreateDate
,Client
,Project
,Position
,PositionOID
,FunctionalRole
,RequestStatus
,Initiation
,Completed
,ResourceSuggested
,StaffingAssignmentStatus
,StaffingAssignmentResource
,CommitmentStartDate
,DaysToSuggested
,SuggestedInTime
,DaysToAccepted
,DaysToCommitment
,DaysToSuggestedIndicator
,DaysToAcceptedIndicator
,RequestCreatedInTime
,RecipientName
,Recipient_First_Action
,Recipient_Last_Action
,DaysSince_First_Action
,DaysSince_Last_Action
,ISNULL(OwnerPoolsName, '') AS OwnerPoolsName
,RecipientDaysToSuggested
,RecipientDaysToAccepted
,RecipientStatus
,ResipientCreateDate
,RecipientRespondedDate
,RequestTimedoutDate
,upper(pc) AS PCR
,ReceivedDatetime
--into rrm_Temp
FROM #results
LEFT JOIN (
SELECT *
FROM #ProjectPosition_JR_CD
) r ON positionoid = r.oid -- AND (r.ccmflag=0 OR r.ccmflag=4)
------ UNION ALL
END
--select * from #rrm_temp
END -- proc
--select * into RptRegionRequestMeasurementsTempResults from #results
DROP TABLE #temp_results
DROP TABLE #leadtime
DROP TABLE #regiontable
DROP TABLE #PCtable
DROP TABLE #ProjectPosition_JR_CD
DROP TABLE #ProjectTeamSlot
DROP TABLE #StaffingRequest
SET NOCOUNT OFF
--select * from RptRegionRequestMeasurementsTempResults
--where requestid = 524441
----
OK so now we have code that is at least legible. The problem here is that you posted a stored proc with over 1600 lines and expect somebody to come along and make this fast. I don't mean in any way to sound negative but you need a lot more help than can be provided by an online forum. You need to hire a professional to come on site and help sort this out. There are so many issues with this code.
Why do you need to truncate temp tables immediately after creating them?
You have lots of looping and very inefficient code. To properly tune this query I would quote a client that it would take 2-3 weeks at a minimum. There are dozens of tables and functions in here.
Why all the NOLOCK hints? Are you familiar with the issues that hint brings to the table? I have a feeling that hint was thrown in here to make it faster without understanding the pitfalls.
You have setup this process with multiple execution paths so that no matter the tuning this will continue to perform poorly because the execution plan will need to be different for each execution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Post the execution plan, you might be lucky and have a 80/20 rule problem which someone can quickly solve. (long shot though)
-
Hi Avik
Imho it's hard to give one good advice for you, please try to do some small improvements first.
1. An asterisk (*) is resolved as a reference to all columns in all tables or views specified in the FROM clause, please change for only for reuired columns.
2. Check size of data sets inserted to the temp tables, if they are small (few rows), use varaible table.
3. Convert some parts to the functions and call them from SP.
4. Declare smaller data types, e.g. smallint or tinyint instead of int.
Regards
Mike
-
Avik Roy (6/24/2013)
Hi All,I need your urgent help regarding this procedure performance tuning so that the execution time become very less.
Below is the procedure :
...
Your stored procedure looks like the contents of a few SSMS windows during the early stages of developing the queries for a report. It's not so much unfinished as barely started, day two of a ten day task. Finishing this job isn't a trivial exercise and doesn't fit well with forum dynamics. Follow Sean's advice and hire someone for the job.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
michal.lisinski (6/25/2013)
2. Check size of data sets inserted to the temp tables, if they are small (few rows), use varaible table.Disagree there, table variables are not magically faster than temp tables, they can be slower due to lack of stats.
3. Convert some parts to the functions and call them from SP.
Disagree there, functions are notorious for causing performance problems, not solving them.
Oh, and for the OP, last time I tuned something this size it took a full week, 8-10 hours a day, 6 days.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi Gila
You have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.
Regards
Mike
-
michal.lisinski (6/25/2013)
Hi GilaYou have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.
Regards
Mike
There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.
-
Sean Pearce (6/25/2013)
michal.lisinski (6/25/2013)
Hi GilaYou have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.
Regards
Mike
There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.
Well, table variables might perform better in very specific ocassions. However, it won't be a significant improvement if the procedure is taking several minutes or even hours to run. There's not much to work here and even less to give a correct solution.
-
Luis Cazares (6/25/2013)
Sean Pearce (6/25/2013)
michal.lisinski (6/25/2013)
Hi GilaYou have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.
Regards
Mike
There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.
Well, table variables might perform better in very specific ocassions. However, it won't be a significant improvement if the procedure is taking several minutes or even hours to run. There's not much to work here and even less to give a correct solution.
Exploit the differences. Table variables are handy if you wish to save some state when a transaction rolls back. It's about all I use them for.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply