Or Criteria taking forever to execute using tridion content delivery api

Question

I am converting from a sql query to using broker api functionality. The query basically retrieves custom meta data based on key and value filters.

The issue is when I join two criteria using an 'or' criteria the query.executequery takes forever and the control never returns. The code I am using is:

PublicationCriteria pubCriteria = new PublicationCriteria(80);

//1st query
CustomMetaKeyCriteria keyCriteria1 = new CustomMetaKeyCriteria("PublicationType");

CustomMetaValueCriteria valueCriteria11 = new CustomMetaValueCriteria(
                                                          "Report", Criteria.Like);
CustomMetaValueCriteria valueCriteria12 = new CustomMetaValueCriteria(
                                                          "Video", Criteria.Like);
Criteria valueCriteria1 = CriteriaFactory.Or(valueCriteria11, valueCriteria12);
Criteria criteria1 =CriteriaFactory.And(keyCriteria1, valueCriteria1);

//2nd query

CustomMetaKeyCriteria keyCriteria2 = new CustomMetaKeyCriteria("Tags");

CustomMetaValueCriteria valueCriteria21 = new CustomMetaValueCriteria(
                                                     "tcm:80-20641", Criteria.Equal);
CustomMetaValueCriteria valueCriteria22 = new CustomMetaValueCriteria(
                                                     "tcm:80-20645", Criteria.Equal);
Criteria valueCriteria2 = CriteriaFactory.Or(valueCriteria21, valueCriteria22);
Criteria criteria2 =  CriteriaFactory.And(keyCriteria2, valueCriteria2);

Criteria querycriteria = CriteriaFactory.Or(criteria1, criteria2);
Criteria finalCriteria = CriteriaFactory.And(pubCriteria, querycriteria);
Query query = new Query(criteria2);
query.SetResultFilter(new LimitFilter(10));
var n = query.ExecuteQuery();

I have tried using new or criteria and passing the criteria as an array but this also didn't work. Please advise.

Answer 1

If you have access to the old forum you can see that there was a pretty long thread (requires login) about a very similar issue.

The solution seemed to be to change the way the criterias are built. In that specific case it was a question of writing them like this:

CustomMetaKeyCriteria key = new CustomMetaKeyCriteria("states");
CustomMetaValueCriteria value = new CustomMetaValueCriteria(key, "* All States");
CustomMetaValueCriteria value2 = new CustomMetaValueCriteria(key, "California");
OrCriteria orCriteria = new OrCriteria(value, value2);

In your case I guess this would translate to something like this (can't test it):

PublicationCriteria pubCriteria = new PublicationCriteria(80);

//1st query
CustomMetaKeyCriteria keyCriteria1 = new CustomMetaKeyCriteria("PublicationType");
CustomMetaValueCriteria valueCriteria11 = new CustomMetaValueCriteria(
                                                keyCriteria1,"Report", Criteria.Like);
CustomMetaValueCriteria valueCriteria12 = new CustomMetaValueCriteria(
                                                keyCriteria1, "Video", Criteria.Like);
OrCriteria orCriteria1 = new OrCriteria(valueCriteria11, valueCriteria12);

//2nd query
CustomMetaKeyCriteria keyCriteria2 = new CustomMetaKeyCriteria("Tags");
CustomMetaValueCriteria valueCriteria21 = new CustomMetaValueCriteria(
                                        keyCriteria2, "tcm:80-20641", Criteria.Equal);
CustomMetaValueCriteria valueCriteria22 = new CustomMetaValueCriteria(
                                        keyCriteria2, "tcm:80-20645", Criteria.Equal);
OrCriteria orCriteria2 = new OrCriteria(valueCriteria21, valueCriteria22);
AndCriteria finalCriteria = new AndCriteria(orCriteria1, orCriteria2);

Query query = new Query(finalCriteria);
query.SetResultFilter(new LimitFilter(10));
var n = query.ExecuteQuery();