How to get rid of the nested loop join for huge tables

How to eliminate the nested loop join for big tables

There are three tables in SQL Server with huge quantity of data, each table consists of about&nbsp100000&nbsprows. There is a single SQL to fetch rows from the 3 tables. Its functionality is quite poor.

WITH t1 AS 
(
 Select 
 LeadId, dbo.get_item_id(Log) AS ItemId, DateCreated AS PriceDate
 FROM 
 (Pick 
 t.ID, t.LeadID, t.Log, t.DateCreated, f.AskingPrice
 FROM 
 t
 JOIN 
 f ON f.PKID = t.LeadID
 The place 
 t.Log LIKE '%xxx%') temp
)
Select COUNT(one)
FROM t1
JOIN s ON s.ItemID = t1.ItemId

When checking its estimated execution prepare, I uncover it utilizes a nested loop join with large rows. Loot at the screenshot under. The prime component in the picture return 124277 rows, and the bottom element is executed 124277 times! I guess this is why it is so slow.

We know that nested loop has large overall performance problem with large information. How to get rid of it, and use hash join or other join instead?

Edit: Under is the associated function.

Produce Perform [dbo].[get_item_Id](@message VARCHAR(200))
RETURNS VARCHAR(200) AS
Get started
 DECLARE @outcome VARCHAR(200),
 @index int

 --Offered in eBay (372827580038).
 Pick @index = PatIndex('%([-9]%)%', @message)
 IF(@index = )
 Choose @end result=''
 ELSE 
 Choose @consequence= Change(Substitute(Exchange(SUBSTRING(@message, PatIndex('%([-9]%)%', @message),8000), '.', ''),'(',''),')','')
 -- Return the end result of the function
 RETURN @end result
End