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.