I am working on a search functionality where in the database is designed as shown.
I have to return the top 20 rows where
- TableA.[Column 3] OR TableC.[Column B] OR TableB.[Column X] CONTAINS the @SearchText.
This is a massive database that has several million records in each one of its tables. A full-text catalog has been created on this database with several full-text indexes.
The Search Query is as follows ...
SELECT DISTINCT
A.ID,
A.Column1,
A.Column2,
A.Column3,
L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
WHERE CONTAINS(A.Column3, @SearchText)
UNION
SELECT DISTINCT
A.ID,
A.Column1,
A.Column2,
A.Column3,
L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
LEFT OUTER JOIN TableB B ON A.Id = B.FK_ID
WHERE CONTAINS(B.ColumnX, @SearchText)
UNION
SELECT DISTINCT
A.ID,
A.Column1,
A.Column2,
A.Column3,
L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
LEFT OUTER JOIN TableC C ON A.Id = C.FK_ID
WHERE CONTAINS(C.ColumnB, @SearchText)
Questions
1) Will this Query perform better - it is very concise.
SELECT DISTINCT
A.ID,
A.Column1,
A.Column2,
A.Column3,
L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
LEFT OUTER JOIN TableB B ON A.Id = B.FK_ID
LEFT OUTER JOIN TableC C ON A.Id = C.FK_ID
WHERE CONTAINS((A.Column3, B.ColumnX, C.ColumnB), @SearchText)
2) Will creating a view MyView with the lookup table left outer joined with TableA improve performance as in ...
SELECT DISTINCT
V.ID,
V.Column1,
V.Column2,
V.Column3,
V.Description
FROM MyView V
LEFT OUTER JOIN TableB B ON V.Id = B.FK_ID
LEFT OUTER JOIN TableC C ON V.Id = C.FK_ID
WHERE CONTAINS((V.Column3, B.ColumnX, C.ColumnB), @SearchText)
Aucun commentaire:
Enregistrer un commentaire