Hi.

I am on llblgen v3.5 Final, Sql Server 2008 R2.

I have the following code that builds a free-text predicate expression:

var fullTextFields = new ArrayList { EnquiryFields.ExternalReference, EnquiryFields.Description, EnquiryFields.Subject };
FilterToUse.PredicateExpression.Add(new FieldFullTextSearchPredicate(fullTextFields, FullTextSearchOperator.Freetext, SearchText.Text) | new FieldFullTextSearchPredicate(EnquiryActionFields.Message, null, FullTextSearchOperator.Freetext, SearchText.Text));
FilterToUse.Relations.Add(EnquiryEntity.Relations.EnquiryActionEntityUsingEnquiryId, JoinHint.Left);

It takes 12-13 seconds to execute.

If I remove the EnquiryActionFields.Message field and the join to EnquiryActionEntity, it executes in a few ms.

EnquiryActionFields only contains 2000 records on the test machine.

Any ideas how to speed things up without creating a view for the two tables?

Thanks

CREATE VIEW EnquiryViewFullTextSearch WITH SCHEMABINDING AS
SELECT e.EnquiryId, e.Subject, e.Description, e.ExternalReference, a.Message, a.EnquiryActionId
FROM dbo.Enquiry AS e
JOIN dbo.EnquiryAction AS a ON e.EnquiryId = a.EnquiryId
GO
CREATE UNIQUE CLUSTERED INDEX IX_EnquiryViewFullTextSearch ON EnquiryViewFullTextSearch (EnquiryActionId)
CREATE INDEX IX_EnquiryViewFullTextSearchEnquiryId ON EnquiryViewFullTextSearch (EnquiryId)

var fullTextFields = new ArrayList { EnquiryViewFullTextSearchFields.ExternalReference, EnquiryViewFullTextSearchFields.Description, EnquiryViewFullTextSearchFields.Subject, EnquiryViewFullTextSearchFields.Message};
FilterToUse.PredicateExpression.Add(new FieldFullTextSearchPredicate(fullTextFields, FullTextSearchOperator.Freetext, SearchText.Text));
FilterToUse.Relations.Add(EnquiryEntity.Relations.EnquiryViewFullTextSearchEntityUsingEnquiryId);

I ended up with the view. It is fast now. Doing this using llblgen actually took me about the same time as writing the first post

I should mention that this does not work with left join. I had to make sure that there is always at least one action. I introduced EnquiryActionType.Open.

Capture the executed SQL for the code in your first post (you can use LLBLGen Tracing or a sql profiler like ORMProfiler). Then execute that on a DB script. How does it look like?

I wonder whether this could be a SQLServer issue. See this: http://sqlking.wordpress.com/2010/06/06/slow-fulltext-query-performance-using-freetext-and-contains/

Thanks for this David.

The update you suggested made the slow query 10x faster.

The view solution is still 10x faster than the join so I am sticking with that.