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.
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
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