1

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?

2

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.

  • Actually thats dead on. These four table fts are always queried together. So I might as well drop the table full text indices and create a view and one for speeding things up? Never thought of that, good idea! – Sam May 29 '15 at 13:05
  • Did the indexed view help your performance? Or not? – RLF Jun 3 '15 at 14:20
  • I've proposed this fix but it is not scheduled yet. I'll update as soon as I get to it. – Sam Jun 4 '15 at 13:59
  • 1
    Thanks for letting me know. I was curious, but I understand scheduling. – RLF Jun 4 '15 at 14:16
  • @RLF What do you do when the view will have to contain left joins and SQL Server does not allow you to index views with outer joins? – skeletank May 14 '18 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.