Using a SplitString Table Values Function in an Inner Join-CodePudding

I have a table, ProcessQueueLog, that contains a delimited field value that I need to split and join to another table. I have a working statement but it seems rather slow. Can someone advise a better method?

fn_SplitString was written inhouse by a long-gone dev. It returns part1 and part2 and is rather typical of any split string function.

SELECT pql.*, cl.ShortNote
FROM Automation.ProcessQueueLog pql
    INNER JOIN dbo.CLog         cl
        ON cl.Conversation_ID = CAST((SELECT Part1 FROM dbo.fn_SplitString (pql.QueuedRecordId, '-')) AS INT)
           AND cl.Memo_ID = CAST((SELECT Part2 FROM dbo.fn_SplitString (pql.QueuedRecordId, '-')) AS INT);

CodePudding user response：

To expand on my comment

SELECT pql.*
     , cl.ShortNote
FROM Automation.ProcessQueueLog pql
CROSS APPLY dbo.fn_SplitString(pql.QueuedRecordId, '-') SS
INNER JOIN dbo.CLog         cl
        ON cl.Conversation_ID = SS.Part1
       AND cl.Memo_ID = SS.Part2;

I'm suspect your split function has a LOOP. If so there are more performant approaches

CodePudding user response：

Another approach that eliminates the (almost certainly hopelessly inefficient) function, which is overkill anyway if we are only breaking a small, known, and fixed number of elements from a string (split functions, even horribly inefficient ones, are most useful when the number of input elements is unknown):

;WITH ql(q,p) AS 
(
  SELECT QueuedRecordId, CHARINDEX('-', QueuedRecordId) -- , other cols
  FROM Automation.ProcessQueueLog
  WHERE QueuedRecordId LIKE '%[0-9]-[0-9]%'
)
SELECT ql.*, cl.ShortNote
FROM ql CROSS APPLY 
(
  VALUES
  (
    TRY_CONVERT(int,LEFT(q,p-1)), 
    TRY_CONVERT(int,SUBSTRING(q,p 1,32))
  )
) AS q2(l,r)
INNER JOIN dbo.CLog AS cl
ON cl.Conversation_ID = q2.l
AND cl.Memo_ID = q2.r;

Example db<>fiddle

To guarantee insulation from invalid values missing - that escape the filter inside the CTE, you can just change the first line in q2 (the expression that yields l, the "left side") to the slightly uglier:

    TRY_CONVERT(int,LEFT(q,COALESCE(NULLIF(p,0),1)-1)),

(Though that fix assumes 0 can't represent a valid ID.)

Another possibility if you are on SQL Server 2016 or better (always useful to specify the version you need to support) is to use OPENJSON for reliable, ordered, set-based split, then wrap it back using PIVOT:

;WITH ql AS
(
  SELECT ql.QueuedRecordId, j.[key], j.[value]
  FROM Automation.ProcessQueueLog AS ql CROSS APPLY 
  OPENJSON ('["'   REPLACE(ql.QueuedRecordId,'-','","')   '"]"') AS j
)
SELECT p.QueuedRecordId, cl.ShortNote 
FROM ql PIVOT (MAX([value]) FOR [key] IN ([0],[1])) AS p
INNER JOIN dbo.CLog AS cl
ON cl.Conversation_ID = p.[0]
AND cl.Memo_ID = p.[1];

Example db<>fiddle

However, if any of the slowness is due to the linked server connection you forgot to mention (how could it not be?), you're probably going to be chasing your tail.

My advice: get the most efficient query (which almost definitely will not involve a multi-statement table-valued function) working with a local copy of the CLog table (post actual, post-execution plans to PasteThePlan.com that you think should be efficient but aren't), then troubleshoot the network connection separately. For all we know, the slowness could be anything from an obvious missing index to a can-and-string network connection.