locked
Enhance the t-sql RRS feed

  • Question

  • User-1319178603 posted

    The below query is taking a lot of time to process. Around 2 minutes. Is there any way I could enhance or correct the below query. Any idea?

    SELECT
    TP.[DutyDate],
    TP.[Day],
    TP.Shifts,
    Split.a.value('.', 'VARCHAR(100)') AS StaffID,
    TP.RecordStatus,
    TP.LastUpdated,
    TP.LastUpdatedBy
    FROM
    (
    SELECT
    [DutyDate],
    [Day],
    Shifts,
    CAST('<M>' + REPLACE([StaffList], ',', '</M><M>') + '</M>' AS XML) AS String,
    [RecordStatus],
    [LastUpdated],
    [LastUpdatedBy]
    FROM ##SelectedTable
    ) AS TP
    CROSS APPLY String.nodes ('/M') AS Split(a)
    WHERE NOT EXISTS (SELECT [DutyDate]
    FROM Time_DutyRoster t2
    WHERE t2.DutyDate = TP.DutyDate
    AND t2.ShiftID = TP.Shifts
    AND t2.StaffID = Split.a.value('.', 'VARCHAR(100)'))
    ORDER BY 1,2

    Thanks

    hahsm

    Monday, April 18, 2016 5:30 AM

Answers

  • User77042963 posted

    Instead of using XML to split your stafflist, you can use a NUMBER table to split your stafflist. It should run much farster if you have a list.

    --===== Create a number table on-the-fly 
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
      
     
    ,mycte as (
    select [DutyDate],[Day],Shifts
    ,substring(StaffList, n, charindex(',', StaffList + ',', n) - n)  splitVal 
    from  ##SelectedTable
    cross apply Nums
    Where n <= len(StaffList) AND substring(',' + StaffList, n, 1) = ',' )
    
    
    Select TP.[DutyDate],
    TP.[Day],
    TP.Shifts, 
    splitVal AS StaffID, 
    TP.RecordStatus, 
    TP.LastUpdated, 
    TP.LastUpdatedBy
    FROM mycte  TP 
    WHERE NOT EXISTS (SELECT [DutyDate] 
    FROM Time_DutyRoster t2 
    WHERE t2.DutyDate = TP.DutyDate 
    AND t2.ShiftID = TP.Shifts 
    AND t2.StaffID = splitVal)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 18, 2016 1:57 PM

All replies

  • User-595703101 posted

    I just converted the NOT EXISTS to LEFT JOIN. I'm not sure if it will help but might worth to try

    SELECT
    	TP.[DutyDate],
    	TP.[Day],
    	TP.Shifts,
    	Split.a.value('.', 'VARCHAR(100)') AS StaffID,
    	TP.RecordStatus,
    	TP.LastUpdated,
    	TP.LastUpdatedBy
    FROM (
    	SELECT
    		[DutyDate],
    		[Day],
    		Shifts,
    		CAST('<M>' + REPLACE([StaffList], ',', '</M><M>') + '</M>' AS XML) AS String,
    		[RecordStatus],
    		[LastUpdated],
    		[LastUpdatedBy]
    	FROM ##SelectedTable
    ) AS TP
    CROSS APPLY String.nodes ('/M') AS Split(a)
    LEFT JOIN Time_DutyRoster t2
    	ON		t2.DutyDate = TP.DutyDate
    		AND t2.ShiftID = TP.Shifts
    		AND t2.StaffID = Split.a.value('.', 'VARCHAR(100)')
    WHERE t2.DutyDate is null
    

    You can review the SQL tutorial on split string values using XML in SQL Server. But it seems that your query works fine but slow.

    You can use another method for splitting string like using a SQL Split String CLR function which I experience as the fastest method

    Monday, April 18, 2016 6:22 AM
  • User-1319178603 posted

    I just test the query. Seems like its taking the same amount of time to process. After some investigations, I found out that, the CROSS APPLY section is very time consuming. Is there any way that I could replace the CROSS APPLY to some other code (which is faster) for that query.

    Thanks

    hahsm

    Monday, April 18, 2016 7:03 AM
  • User-595703101 posted

    In fact, the trick in this query is the CROSS APPLY in my opinion.

    By Cross Apply we can apply a user function on a field of a row and manage this in a set based operation.

    Monday, April 18, 2016 10:14 AM
  • User77042963 posted

    Instead of using XML to split your stafflist, you can use a NUMBER table to split your stafflist. It should run much farster if you have a list.

    --===== Create a number table on-the-fly 
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
      
     
    ,mycte as (
    select [DutyDate],[Day],Shifts
    ,substring(StaffList, n, charindex(',', StaffList + ',', n) - n)  splitVal 
    from  ##SelectedTable
    cross apply Nums
    Where n <= len(StaffList) AND substring(',' + StaffList, n, 1) = ',' )
    
    
    Select TP.[DutyDate],
    TP.[Day],
    TP.Shifts, 
    splitVal AS StaffID, 
    TP.RecordStatus, 
    TP.LastUpdated, 
    TP.LastUpdatedBy
    FROM mycte  TP 
    WHERE NOT EXISTS (SELECT [DutyDate] 
    FROM Time_DutyRoster t2 
    WHERE t2.DutyDate = TP.DutyDate 
    AND t2.ShiftID = TP.Shifts 
    AND t2.StaffID = splitVal)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 18, 2016 1:57 PM