Optimizing SQL query with multiple LIKE operations on nvarchar columns

Question

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.

Answer 1

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