Optimizing SQL query with multiple LIKE operations on nvarchar columns

I am facing performance issues with an SQL query that involves multiple LIKE operations on nvarchar columns. The query aims to search for a substring in almost every column, from extraOne to extraEight, in three tables (except table A that has only three searchable columns).
And if match is found in any of these tree tables I need to retrieve records from Table A (C -> B -> A).
Tables structure

Table A
- Id
- ExtraOne
...
- ExtraThree
Table B
- Id
- ExtraOne
...
- ExtraEight
- A_Id (FK to table A)
Table C
- Id
- ExtraOne
...
- ExtraEight
- B_Id (FK to table B)

Currently, this is the query I am using:

SELECT [t1].[id]
FROM
(
SELECT DISTINCT [t0].[id]
FROM
(
SELECT [b].[id]
FROM [Table A] AS [b]
WHERE
(
(
([b].[id] LIKE '%searchText%')
OR ([b].[extraone] LIKE '%searchText%')
)
OR ([b].[extratwo] LIKE '%searchText%')
)
UNION
SELECT [b0].[id]
FROM [Table A] AS [b0]
INNER JOIN [Table B] AS [c] ON [b0].[id] = [c].[A_Id]
WHERE
(
(
(
(
(
(
(
(
[c].[id] LIKE '%searchText%'
)
OR ([c].[name] LIKE '%searchText%')
)
OR ([c].[extraone] LIKE '%searchText%')
)
OR ([c].[extratwo] LIKE '%searchText%')
)
OR ([c].[extrathree] LIKE '%searchText%')
)
OR ([c].[extrafour] LIKE '%searchText%')
)
OR ([c].[extrafive] LIKE '%searchText%')
)
OR ([c].[extrasix] LIKE '%searchText%')
)
UNION
SELECT [b1].[id]
FROM [Table A] AS [b1]
INNER JOIN [Table B] AS [c0] ON [b1].[id] = [c0].[A_id]
INNER JOIN [Table C] AS [d] ON [c0].[id] = [d].[B_id]
WHERE
(
(
(
(
(
(
(
(
[d].[id] LIKE '%searchText%'
)
OR ([d].[name] LIKE '%searchText%')
)
OR ([d].[extraone] LIKE '%searchText%')
)
OR ([d].[extratwo] LIKE '%searchText%')
)
OR ([d].[extrathree] LIKE '%searchText%')
)
OR ([d].[extrafour] LIKE '%searchText%')
)
OR ([d].[extrafive] LIKE '%searchText%')
)
OR ([d].[extrasix] LIKE '%searchText%')
)
) AS [t0]
) AS [t1]
ORDER BY [t1].[id]
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

I would like to optimize this query for better performance. Here are a few questions I have:

• Are there any alternative techniques or optimizations I can apply to improve the performance of the LIKE operations in the query?

• Should I consider using Full-Text Search instead of LIKE for searching substrings in the nvarchar columns?

• How can I ensure that appropriate indexes are created on the columns involved in the search conditions to improve query performance?

So far I achieved performance gain by using UNION instead of making simply left joins but still needs some optimizations.

Also I have noticed when fetch next rows has number 100 it is much more faster (3x) then when limit is 10.

Please rewrite with a readbale structure like this :

WITH 
t0 AS
(
SELECT b.id
FROM   TableA AS b
WHERE     b.id       LIKE '%searchText%'
OR b.extraone LIKE '%searchText%'
OR b.extratwo LIKE '%searchText%'
UNION
SELECT b0.id
FROM   TableA AS b0
INNER JOIN TableB AS c 
ON b0.id = c.A_Id
WHERE  c.id LIKE '%searchText%'
OR (c.name LIKE '%searchText%')
OR (c.extraone LIKE '%searchText%')
OR (c.extratwo LIKE '%searchText%')
OR (c.extrathree LIKE '%searchText%')
OR (c.extrafour LIKE '%searchText%')
OR (c.extrafive LIKE '%searchText%')
OR (c.extrasix LIKE '%searchText%')
UNION
SELECT b1.id
FROM TableA AS b1
INNER JOIN TableB AS c0 ON b1.id = c0.A_id
INNER JOIN TableC AS d ON c0.id = d.B_id
WHERE d.id LIKE '%searchText%'
OR (d.name LIKE '%searchText%')
OR (d.extraone LIKE '%searchText%')
OR (d.extratwo LIKE '%searchText%')
OR (d.extrathree LIKE '%searchText%')
OR (d.extrafour LIKE '%searchText%')
OR (d.extrafive LIKE '%searchText%')
OR (d.extrasix LIKE '%searchText%')
)
SELECT DISTINCT t0.id
FROM   t0
ORDER  BY t0.id OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

If you want performances, not classical indexes will be used. You need your own structure to do so...

1 - for each colums to be search split the value into trigrams in another table that references the table name, the key value, the position of first char of the trigram into the string value

2 - create an index on this table

3 - rewrite your query to use this new table

I'm not sure about performance but it should be valid to left join table A by [id] column to these other tables, then concatenate all searchable columns together and perform a single string search. CONCAT() removes NULLs so not sure if that's in the data you're querying, but it will be produced from non-matching LEFT JOINs. Something like:

SELECT A.[id]
FROM [Table A] A
LEFT JOIN [Table B] B ON A.id = B.A_id
LEFT JOIN [Table C] C ON B.id = C.B_id
WHERE CONCAT(A.[extraone], '|', A.[extratwo], '|'
,B.[name], '|', B.[extraone], '|', B.[extratwo], '|', B.[extrathree], '|'
,B.[extrafour], '|', B.[extrafive], '|', B.[extrasix], '|'
,C.[name], '|', C.[extraone], '|', C.[extratwo], C.[extrathree], '|'
,C.[extrafour], '|', C.[extrafive], '|', C.[extrasix], '|'
,C.[extraseven], '|', C.[extraeight], '|'
) LIKE '%searchText%'

If there are duplicate id's returned then you could write SELECT DISTINCT A.[id].

The left joins can use an index to left join on ID value to table A, but the where-clause condition is not sargable.