4

We are making broker query to fetch some forms data which are not user specific. There are total around 1000 forms and for different users the result is different based on the Query Criteria.

Following is the code sample(broker query part):

ComponentFactory cpf = new ComponentFactory();
                Query query = new Query();
                ItemTypeCriteria componentsOnly = new ItemTypeCriteria(ItemType.Component.GetHashCode());
                SchemaTitleCriteria basedOnSchema = new SchemaTitleCriteria("Schema Name");

                PublicationCriteria basedOnPublication = new PublicationCriteria(pubId);
               Criteria Criteria = CriteriaFactory.And(new Criteria[] { componentsOnly, basedOnSchema, basedOnPublication });
                query.Criteria = Criteria;
               SortParameter sortParameter = new SortParameter(
                                 SortParameter.ItemLastPublishedDate,
                                 SortParameter.Descending);
                query.AddSorting(sortParameter);

                //Execute the query
                var results = query.ExecuteQuery().ToList();

We have placed caching in Application Level (IIS) and also in code from the second time onwards. But for the first time it is taking too much time as along with Tridion Broker call we make some other service calls asynchronously, but we found out only for the broker call it is taking maximum time (20-22 secs) , which makes the page load complete after 30 secs around.

Please let me know if we can place any other caching at any level / modules to avoid such conditions. We are thinking to load all the forms at application start (MVC application) but again it will delay the login process. Please suggest.

1
  • 1
    Have a look into Database Indexing, it helps to improve your search sometimes. Commented Jan 29, 2016 at 8:02

5 Answers 5

1

I agree with Raj, that you are loading too much of data for the first time. For, Caching implementation you can check from here also along with the other references from Tanner. Now from your question, it seems you don't have to load all of the data(forms) for any specific user.

Firstly, I don't think loading the data at Application_Start will solve your case and you are correct it will delay the login (initial) processes/pages. Seeing your request, i think you could do some modifications by providing some extra filtration criteria to your broker query logic.See below example.

    Query query = new Query();
    ItemTypeCriteria componentsOnly = new ItemTypeCriteria(ItemType.Component.GetHashCode());
    SchemaTitleCriteria basedOnSchema = new SchemaTitleCriteria("Schema Name");
    PublicationCriteria basedOnPublication = new PublicationCriteria(pubId);

    //Here is the extra criteria to load miminum data
    var defaultCriteria = CriteriaFactory.And(new Criteria[] { componentsOnly, basedOnSchema, basedOnPublication });

    Criteria criteria = null;
    Criteria customCriteria = new CustomMetaValueCriteria(new CustomMetaKeyCriteria(""),"", Criteria.Equal);
    var conditionCriteriaList = new List<Criteria>();
    List<KeyValuePair<string, string>> conditions = new List<KeyValuePair<string, string>>();

    //this is a single condtion, in your case you can have all values set for any particular user, create list object like in this format

    conditions.Add(new KeyValuePair<string, string>("the metadata field name[e.g. formtype]", "your value for loading any specific items"));                        

    foreach (var condition in conditions)
     {
      conditionCriteriaList.Add(
      new CustomMetaValueCriteria(
                new CustomMetaKeyCriteria(condition.Key),
                condition.Value,
                Criteria.Equal
                )
               );
     }

    var conditionCriteria = CriteriaFactory.Or(conditionCriteriaList.ToArray());                    
    criteria = CriteriaFactory.And(defaultCriteria, conditionCriteria);                    
    query.Criteria = criteria;
    SortParameter sortParameter = new SortParameter(
                                 SortParameter.ItemLastPublishedDate,
                                 SortParameter.Descending);
    query.AddSorting(sortParameter);

    //Execute the query
    var results = query.ExecuteQuery().ToList();

So, It requires changes in Tridion also, Like in the schema you need to add metadata field (key/value) and values for related components. If this helps, then it will be a challenge for you to make changes in 1000 components, but you can automate it (preferable in development/qa environments). Let me know if this helps.

1
  • 1
    You can also try as suggested by Pankaj by implementing Search Engine like SOLR (SI4T/SS4T) based on your project bandwidth. Commented Jan 29, 2016 at 7:08
4

i think your are loading too much content from the broker. Can't you do just some optimization.

  • As mentioned by @Tanner Object Cache with CCS is indeed very good option check if it helps
  • Nothing works better than optimizing your queries. how about putting some taxonomy and find only required content at load then lazy load rest of it.
  • Keep your db indices optimum

.

4

@Shatadru, How you are invalidating your application level cached result to reflect published data?

To improve the performance of you query execution you can check the below options:

  • Broker database maintenance(You can check the link)
  • Broker query optimization- We can directly relate broker query with the SQL query being executed on broker database and further analyze the execution plan of query. To check the query created because of your broker query used, you can either check in hibernate log(Check SDL documentation to create hibernate log) or by directly using profiler on your development database.
  • Use multi-threading concept in your mvc application to get the broker query result while your page is loading in parallel(For this you can check feasibility in your application).
  • Use caching of broker query result in your application- I am assuming that application level cache will not work for you. Either you could rely on tridion provided query result caching(if publishing is not happening constantly in your environment, as mentioned in Nuno's reply here) or You can use caching to store result in your application code as per requirement. You can see example in Nuno's post.
2
  • 2
    Broker queries are now cached, not just objects. Nuno's post was correct at the time. Commented Jan 29, 2016 at 6:45
  • 1
    Thanks Alvin to provide insight here. Query result caching is useful in case of less publishing in environment. I have edited my answer too. Commented Jan 29, 2016 at 8:56
3

You may try following option:

  • Keep the Maintenance Plan for SDL Tridion in effect and quite frequent - for extensive broker queries, I guess it was recommended to run the SDL Tridion Maintenance Plan every day, twice a day or even every few hours depending on the number of transactions and in parallel optimize the Broker Query as much possible - Check the documentation for same or get in touch with SDL Support for the Maintenance plan.
  • Another option is to consider a change in your architecture and for such content instead of making slow broker queries, try to implement a Search Engine like SOLR, Endeca etc. - For Solr Integration, you can choose to implement the open community SI4T framework or the SS4T framework depending on your need.
0

It's not clear to me what you mean by having caching "in code", so it's possible you've already implemented this, but one option to explore is the Tridion Object Cache. To quote Julian Wraith's excellent introductory blog post,

The SDL Tridion Object Cache is an in-memory cache that stores frequently used objects from the Content Data Store. The cache keeps objects available to applications for repeated usage. This prevents SDL Tridion Content Delivery from having to retrieve objects from the Content Data Store for each request. Typically caching includes linking information, metadata and component presentations.

If this sounds like what you're after, I'd suggest starting with Julian's article, which includes information on setting up this cache, and then move on to the number of other articles you can find on Google for help with any specific challenges you might face.

1
  • How will that improve on the first time load? The caching is already there for any frequent DB access as suggested in the question Commented Jan 28, 2016 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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