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
100000 rows. There is a single SQL to fetch rows from the 3 tables. Its functionality is quite poor.
WITH t1 AS
LeadId, dbo.get_item_id(Log) AS ItemId, DateCreated AS PriceDate
t.ID, t.LeadID, t.Log, t.DateCreated, f.AskingPrice
f ON f.PKID = t.LeadID
t.Log LIKE '%xxx%') temp
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
DECLARE @outcome VARCHAR(200),
Pick @index = PatIndex('%([-9]%)%', @message)
IF(@index = )
Choose @end result=''
Choose @consequence= Change(Substitute(Exchange(SUBSTRING(@message, PatIndex('%([-9]%)%', @message),8000), '.', ''),'(',''),')','')
RETURN @end result