I am working on a project which is db performance intensive. and I want to build the dynamic query following the db index which has been designed and implement and used by other app. the query will have to follow the old index to get better db performance without adding new indexes.

For Example,

Table A left join table B on A.id = B.id and A.mid = 123 and B.mid = 123

IEntityRelation relation = AEntity.Relations.BEntityUsingId; reation.CustomFilter = new PredicateExpression(AFields.mid == 123 | BFields.mid ==123); relations.add(relation, JoinHit.Left);

This is working. but somehow the index needs different sequence of these filters.

Can the LLBLGen do the following?

Table A left join table B on A.mid = 123 and A.id = B.id and B.mid = 123

and

Table A left join table B on A.mid = 123 and B.mid = 123 and A.id = B.id

Thanks

The 2 statement are sematically equivalent arent they - the query optimizer should produce the same query plan for each. Can you explain why it is necessary to specify the order of the operators...?

Matt

When the order of operators gets changed, it seems that the execution plan in SQL server is picking up different index. For example, index like idx_clientId_merchantId VS idx_merchantID . and performance is different depends on which index the execution plan picks.

Thanks

Mmmm. I see. I don't have much experience in index selection... What I know is that what you want is not possible right now. Making it to work would represent change sensitive things in the generated code, and/or deriving a lot the LLBLGen objects.

It's feasible to you to manage this at DB side? e.g. telling the DB server how to select the correct index you want.

I don't fully see what you want. The 'and' between the two join statements, is that an and you want to see? as that's not going to work of course.

If you specify a custom filter it's going to be the ON clause of the join if you set the flag CustomFilterReplacesOnClause on the relation to true. (default is false). If false, the custom filter is appended to the on clause, if true, the custom filter is replacing the on clause defined from the fields. This thus means you have to specify the complete ON clause in the custom filter.

Thanks guys, That's what I am looking for. It works great for me! Problem solved!