Hi,

I'm having some performance issue using fulltext query to filter results from the database. My performance issue doesn't come from the fulltext query itself but rather from the fact that the fulltext indexed field is included in the entity. (using self servicing)

col = new InfoCandidatsShortCollection();

IPredicateExpression predicate = new PredicateExpression();

predicate.Add(new FieldFullTextSearchPredicate(EntityFieldFactory.Create(InfoCandidatsShortFieldIndex.content), FullTextSearchOperator.Contains, containsString));

col.GetMulti(predicate);    

I have been trying with no luck to find a way to achieve this without including the field in the entity in the designer. FieldFullTextSearchPredicate expects an IEntityField as the first parameter and I'm not sure what to pass if my EntityField hasn't been generated.

My question : Is there a way to run my query and build a predicate using a field unmapped in the generated entity ? How ?

Thanks

Iannick

My performance issue doesn't come from the fulltext query itself but rather from the fact that the fulltext indexed field is included in the entity. (using self servicing)

Would you please elaborate more on this? What are the runtimeLibrary version in use?

My question : Is there a way to run my query and build a predicate using a field unmapped in the generated entity ? How ?

You can implement IExpression: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I also fail to see how this can be a performance bottleneck, which is NOT related to the fulltext search query but apparently somewhere else?

The only performance issue I've seen with full text search is on sqlserver 2000, where you have a fulltextsearch on two fields from two tables in a single filter. This really incredibly slows things down as the RDBMS engine suddenly decides to roll out the matching indexes into a temptable. In sqlserver 2005 this has been solved. You can see that in action on this forum, try to search on subject AND message text

Sorry about that. You are right the problem doesn't come from the Fulltext query, it's coming from the fact that the full text index field is returned as part of the resultset (and entity). The database column indexed is a Text column with KB of data on every row (thousands of row). A typical query returns thousands of rows.

Using version 2.0.00 - Sql Server 2005 - .NET 2 - SqlServerSpecific.NET.20

The effects of this are : 1) Saturated bandwitch 2) Painfull performance (because of amount of data that has to go over the wired to return the results)

I have tried unmapping my fulltext indexed field from the entity using the designer. (because I never need this column as part of a search result) The only time this column is needed into an entity is when I Create / Edit such an Entity. To overcome this I have created to separate Entities over the same table. One "Full" entity containing the field (for insert / update scenario of an Entity) and one "Short" entity of the same table without that "huge" field / column included.

My problem is that when I use the "Short" version of the entity that doesn't contain the field on which I want to Fulltext search I am unable to build a FieldFullTextSearchPredicate because I don't know how to build an IEntityField2 reference over a field that wasn't generated by the designer.

This words but is really slow

FullEntity :
 -FirstName
 -LastName
 -Phone
 -Address
 -Content  (Text - Lots of it)

predicate.Add(new FieldFullTextSearchPredicate(EntityFieldFactory.Create(FullEntity.content), FullTextSearchOperator.Contains, containsString));

This is what I would want to achieve : (filter on a field not included in the entity but that exists in the table)

ShortEntity : 
 -FirstName
 -LastName
 -Phone
 -Address

predicate.Add(new FieldFullTextSearchPredicate(<can't find what goes here>, FullTextSearchOperator.Contains, containsString));

Hope this helps

Did you try to simply use the full entity's field on the filter?

i.e. when you fetch the collection of the ShortEntity, use the exact predicate of the FullEntity.

predicate.Add(new FieldFullTextSearchPredicate(EntityFieldFactory.Create(FullEntity.content), FullTextSearchOperator.Contains, containsString));

Works great thanks. I should have thinked about that before posting... I guess the fact I saw indexes mislead me to think you couldn't reuse an entityfield accross entities..

Wouldn't there have been another way around the problem to be able to filter on a field that is unmapped in the entity but coutained in the underlying table ? (To avoid having to create a Dummy Entity just for the sake of having and EntityField reference if I ever have the need to filter on an unmapped field). This works great but the next guy who gets into that code is going to say "what the hell is going on here, he's filtering over the field of another entity !?" (Of course I'll put some comments )

Something like : (I've shortened it to show my point, not even sure there would be such a signature)

IEntityField = new EntityField(<my unmapped field contained in the table>)

Thanks

Iannick

I haven't tried it before, but I encourage you to try it

Try to construct an entityField (check the reference manual for possible overloads of the constructor). Then you can try to set some of its important properties, such as: SourceColumnName.

Turn on tracing to examine the generated SQL query to better debug your work.

iannick wrote:

Works great thanks. I should have thinked about that before posting... I guess the fact I saw indexes mislead me to think you couldn't reuse an entityfield accross entities..

Wouldn't there have been another way around the problem to be able to filter on a field that is unmapped in the entity but coutained in the underlying table ? (To avoid having to create a Dummy Entity just for the sake of having and EntityField reference if I ever have the need to filter on an unmapped field). This works great but the next guy who gets into that code is going to say "what the hell is going on here, he's filtering over the field of another entity !?" (Of course I'll put some comments )

Something like : (I've shortened it to show my point, not even sure there would be such a signature)

IEntityField = new EntityField(<my unmapped field contained in the table>)

Thanks

Iannick

Possibly a clearer and more flexible approach would be to allow fields to be defined as lazy load (i.e. your content field) - IIRC that is on the feature list for version 2.1.

True, delay loaded blobs/clobs (image, text etc. fields). You can mark them in the designer as delay loaded and with blobpaths you can fetch them into a graph. Precise details aren't available at this point, but this is the plan