在涉及nvarchar列的多个LIKE操作的SQL查询中,我面临着性能问题.该查询的目的是在三个表(除了只有三个可搜索列的表A之外)的几乎每一列中搜索子字符串,从Extra One到Extra Eight. 如果在这些树表中的任何一个中找到匹配,我需要从表A(C-&>;B--&>A)中检索记录. 表 struct
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)
目前,这是我正在使用的查询:
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
我希望优化此查询以获得更好的性能.以下是我有几个问题:
-
是否有其他技术或优化可以应用来提高查询中类似操作的性能?
-
我应该考虑使用全文搜索而不是LIKE来搜索nvarchar列中的子字符串吗?
-
如何确保在搜索条件所涉及的列上创建适当的索引以提高查询性能?
到目前为止,我通过使用Union而不是进行简单的左连接实现了性能提升,但仍然需要一些优化.
此外,我还注意到,当FETCH NEXT ROWS数为100时,它的速度(3倍)比LIMIT为10时快得多.