Question :
I’ve got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow:
SELECT t0.[Key], t0.[Nr]
FROM dbo.[Customers_View] AS t0
WHERE (t0.[Deleted] IS NULL)
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t1 WHERE t0.[Key] = t1.[Key] AND CONTAINS(*, '"smith"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t2 WHERE t0.[Key] = t2.[CustomerKey] AND CONTAINS(*, '"smith"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t3 WHERE t0.[Key] = t3.[CustomerKey] AND CONTAINS(*, '"smith"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t4 WHERE t0.[Key] = t4.[CustomerKey] AND CONTAINS(*, '"smith"')))
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t5 WHERE t0.[Key] = t5.[Key] AND CONTAINS(*, '"munich"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t6 WHERE t0.[Key] = t6.[CustomerKey] AND CONTAINS(*, '"munich"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t7 WHERE t0.[Key] = t7.[CustomerKey] AND CONTAINS(*, '"munich"'))
OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t8 WHERE t0.[Key] = t8.[CustomerKey] AND CONTAINS(*, '"munich"')))
Is there a good way to speed up this Kind of query? Usually it is a bit more complex, I simplyfied it to the full text part.
Maybe there is a better way to to fulltext queries across multiple tables?
Answer :
I do not know how dynamic your queries are, but naturally each table needs a separate query for that table.
Assuming that you are constantly querying this 4 table group, it might be worthwhile to create an indexed view that included the searchable fields you need from the 4 tables into a single view.
This means the overhead of maintaining the indexed view, but may help in this case.
Of course, if you have a widely varying array of tables that might be joined, you would need to consider whether this is a good idea for you or whether you should query each table separately as you are doing.