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.