Find occurrences of strings in view B in column from table A

Question

I'm trying to find the occurrences of strings in view B in a string in table A. However, the performance is horrendous (takes about 17 minutes to run) and I was wondering if anyone had any suggestions to improve it.

view B has 3570 records and has one column of type varchar named MISubCode

Table A has 160081 records and has the schema in the attached image.

There's also a third table, (Table C) which is largely used to filter the results of table A since I don't need to find occurrences in any of those records.

My rational to solve this was as follows:

1. Count the number of characters in the string in the desired search column in table A, producing one row for each character
2. Filter the unneeded rows using view C
3. Run each row generated in result set from 2. and use the Number column to find the occurrences from view B in the string in column from Table A until the end of the string in column from table A.

Code:

 SELECT *
FROM
(
SELECT Number, A.StatusInputPoint_DESC, A.StatusInputPoint_CODE, A.StatusInputPoint_CORE_ID
FROM
(
SELECT Number, A.StatusInputPoint_DESC, A.StatusInputPoint_CODE,  A.StatusInputPoint_CORE_ID
FROM 
tblStatusInputPoints_CORE A
CROSS JOIN
(
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.all_objects
) AS n(Number)
WHERE Number >= 1 AND Number <= CAST(LEN(A.StatusInputPoint_DESC) AS INT) AND System_ID = 1
) AS A
LEFT JOIN
vwAlarmLibrary_MI_StatusPoint_Mask_Match_NoWildCards C
ON
A.StatusInputPoint_CORE_ID = C.StatusInputPoint_CORE_ID
WHERE C.StatusInputPoint_CORE_ID IS NULL
) AS RemainingStatusPoints
LEFT JOIN
dbo.vwAlarmLibrary_MI_Substation_Abbreviations B
ON
SUBSTRING(RemainingStatusPoints.StatusInputPoint_DESC, Number, LEN(B.MISubCode)) = B.MISubCode
WHERE B.MISubCode IS NOT NULL

I'm at a loss as to how to make this faster. I tried several different methods and join combinations and I can't seem to get it to perform well. I'm not an expert in SQL Server so I don't know all the features.

I'm currently using SQL Server 2016

EXAMPLE:

For clarity, lets say I have these strings in view B

 ABC
 EFGH
 IJ

And I have these strings in table A

 123 ABC 45 IJ
 IJ
 IJ EFGH 22

The result should be this:

 Pos   String          Occurrence
 5   | 123 ABC 45 IJ | ABC
 12  | 123 ABC 45 IJ | IJ
 1   | IJ            | IJ
 1   | IJ EFGH 22    | IJ
 4   | IJ EFGH 22    | EFGH

Answer 1

I think you could just simplify this with CHARINDEX and CROSS APPLY(). See how this method stacks up in your environment and paste your execution plans if it happens to be slower, if you can.

To give a concrete example, create these tables:

declare @ViewB table (myStrings varchar(16))
insert into @ViewB
values
('ABC'),
('EFGH'),
('IJ')

declare @TableA table (colToSearch varchar(256))
insert into @TableA
values
('123 ABC 45 IJ'),
('IJ'),
('IJ EFGH 22')

Then this CROSS APPLY() query should be more efficient:

select
    charindex(b.myStrings,a.colToSearch) as Pos
    ,a.colToSearch as String
    ,b.myStrings as Occurance
from
    @ViewB b
cross apply (select * from @TableA) a
where
    charindex(b.myStrings,a.colToSearch) > 0
order by 
    String, charindex(b.myStrings,a.colToSearch)

Alternatively, you could use CROSS JOIN:

select
    charindex(b.myStrings,a.colToSearch) as Pos
    ,a.colToSearch as String
    ,b.myStrings as Occurance
from
    @ViewB b
cross join @TableA a
where
    charindex(b.myStrings,a.colToSearch) > 0
order by 
    String, charindex(b.myStrings,a.colToSearch)

RESULTS

+-----+---------------+-----------+
| Pos |    String     | Occurance |
+-----+---------------+-----------+
|   5 | 123 ABC 45 IJ | ABC       |
|  12 | 123 ABC 45 IJ | IJ        |
|   1 | IJ            | IJ        |
|   1 | IJ EFGH 22    | IJ        |
|   4 | IJ EFGH 22    | EFGH      |
+-----+---------------+-----------+

Answer 2

This might give you better performance

select B.myStrings, A.colToSearch 
     , charindex(b.myStrings,a.colToSearch) as Pos
from @ViewB as B 
join @TableA as A
  on A.colToSearch like '%'+B.myStrings+'%'