September 18, 2017 at 8:23 am
Is there any efficient way to get a comma separated list than the query below ? This query is getting stck
STUFF(
(SELECT DISTINCT ', ' + (t2.[RolledUpTask])
FROM #RollUps t2
where t1.TaskID = t2.TaskID
FOR XML PATH (''))
, 1, 1, '') AS [RolledUpTask]
Thanks,
PSB
September 18, 2017 at 8:43 am
PSB - Monday, September 18, 2017 8:23 AMIs there any efficient way to get a comma separated list than the query below ? This query is getting stckSTUFF(
(SELECT DISTINCT ', ' + (t2.[RolledUpTask])
FROM #RollUps t2
where t1.TaskID = t2.TaskID
FOR XML PATH (''))
, 1, 1, '') AS [RolledUpTask]
Thanks,
PSB
Can you post the actual execution plan please?
😎
Questions:
September 18, 2017 at 8:58 am
Here you go! It was working all these months and suddenly it's never finishing today .
September 18, 2017 at 9:13 am
Look into these two statements which are the majority of the execution cost, by the looks of it, these are more your problem rather than the concatenation of the comma separated list.
😎
INSERT INTO #TaskStatusCalculation
(
TaskUID,
ExpectedPercentageComplete
)
SELECT Distinct
TASK.TaskUID,
CASE
-- START DATE IN THE FUTURE WE SHOULD BE AT 0% COMPLETION
WHEN DATEDIFF(DAY, getdate(), TaskStartDate) > 0 THEN 0
-- FINISH DATE IN THE PAST WE SHOULD BE AT 100 COMPLETION
WHEN DATEDIFF(DAY, getdate(), TaskFinishDate)< 0 THEN 100
-- FINISH DATE EQUALS TO START AND START IN THE PAST WE SHOULD BE AT 100% COMPLETION
WHEN DATEDIFF(DAY, TaskStartDate, TaskFinishDate) = 0 AND DATEDIFF(DAY, TaskStartDate, GETDATE()) < 0 THEN 100
-- FINISH DATE EQUALS TO START AND START IN THE FUTURE WE SHOULD BE AT 0% COMPLETION
WHEN DATEDIFF(DAY, TaskStartDate, TaskFinishDate) = 0 AND DATEDIFF(DAY, TaskStartDate, GETDATE()) >= 0 THEN 0
ELSE
ISNULL(cast(DATEDIFF(DAY, TaskStartDate, GETDATE()) as float)/cast(DATEDIFF(DAY, TaskStartDate, TaskFinishDate) as float),0)*100
END ExpectedPercentageComplete
FROM [ProjSvr].[MSP_EpmTask] Task LEFT JOIN [ProjSvr].[MSP_EpmAssignment] Assign On Assign.TaskUID = Task.TaskUID
--WHERE Assign.[AssignmentIsPublished] = 1
INSERT INTO #RollUps
(
TaskUID,
TaskOwner,
[TaskOwnerAlias],
[LocalResource_T],
[RolledUpGM],
[RolledUpCountry],
[RolledUpProductLine]
)
--This table will be used for comma separated list of Task owners
SELECT DISTINCT
Task.TaskUID,
Res.ResourceName,
SUBSTRING(Res.ResourceNTAccount, CHARINDEX('\', Res.ResourceNTAccount) + 1, LEN(Res.ResourceNTAccount)),
Assign.[Local Resource_T],
country.GeoMarket,
country.MemberValue,
Pl.MemberValue
FROM [ProjSvr].[MSP_EpmTask] Task
INNER JOIN [ProjSvr].[MSP_EpmProject] Proj ON Task.[ProjectUID] = Proj.[ProjectUID]
INNER JOIN [ProjSvr].[MSPCFTASK_PL_Association] Seg On Seg.[EntityUID] = Task.[TaskUID]
--INNER JOIN [ProjSvr].[MSPLT_Seg_Lookup] Segments ON Segments.[LookupMemberUID] = Seg.[LookupMemberUID]
INNER JOIN [ProjSvr].[MSPLT_ProductLine_Lookup] PL ON PL.[LookupMemberUID] = Seg.[LookupMemberUID]
INNER JOIN [ProjSvr].[MSPCFTASK_Country_Association] GeoA On GeoA.[EntityUID] = Task.[TaskUID]
-- INNER JOIN [ProjSvr].[MSPLT_GM_Lookup] GeoMarkets ON GeoMarkets.[LookupMemberUID] = GeoA.[LookupMemberUID]
INNER JOIN [ProjSvr].[MSPLT_Country_Lookup] country ON country.[LookupMemberUID] = GeoA.[LookupMemberUID]
LEFT JOIN [ProjSvr].[MSP_EpmAssignment] Assign On Assign.TaskUID = Task.TaskUID
LEFT JOIN [ProjSvr].[MSP_EpmResource] Res ON Res.ResourceUID = Assign.ResourceUID
LEFT JOIN #cteMileStones cte ON cte.TaskUID = Task.TaskUID
JOIN #TaskStatusCalculation TSC ON TSC.TaskUID = TAsk.TaskUID
WHERE Proj.BWF IS NOT NULL --AND Assign.[AssignmentIsPublished] = 1
September 26, 2017 at 10:32 am
You might want to examine your full table scans first. There are a couple in there that are really imparing performance.
Once you have done that you could create your .csv file by embedding the code into a BCP statement and executing it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply