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:
- Count the number of characters in the string in the desired search column in table A, producing one row for each character
- Filter the unneeded rows using view C
- 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