Performance tips

[LordRevan007]

Good evening,

I am using the latest version of OpenAccess, for developing a large scale web application and now I am evaluating some performance strategies, (I would also like to mention that database backend that is used is MySQL):
1. Context separation, right now it is a big context with all the tables and the relationship mapped from that database? I am wondering if splitting the context into smaller ones could mean a difference?
2.Fetch plans, this feature doesn't seem to be an option because a lot of code should be modified, and some parts of it, may need some serious refurbish.
3.ADO.NET- this strategy would be the last resort and it will be used only in the extreme cases only.
4.Using stored procedures and moving a lot of the code towards MySQL, here the problem is shifted towards the database server and I don't  know how well OpenAccess is with these.

My question is simple:
Has anyone tried OpenAccess in a scenario with a lot of records inside the database, and if so what strategy was used to improve the performance? 

Right now, my instincts are telling me to do a context separation and after that using OpenAcces, only for Data manipulation, let the Ado.net to the selection and calculation job.

Thanks in advance.

[Ivailo]

Hello,

Telerik OpenAccess ORM is capable of handling large web applications, as this is already been done by other people and even within Telerik - by Sitefinity Online Business Platform, which is entirely based on OpenAccess ORM.

To get to your questions:

1. Splitting the context would make sense only if you will not be using the different contexts simultaneously. If your application will use different parts of the database in isolation for a period of time, and no other parts will be touched, it might be useful since only parts of the metadata would be loaded. Still, it depends on the tables and columns that you would like to have - for 100-200 tables with not too long lists of columns each, splitting won't matter too much, while if the tables are thousands it might make some difference.
Note that if you load all the contexts in parallel anyway, you will have the same metadata in memory, so splitting the context would not bring you value in such scenario. Also, have in mind that data will be loaded lazily by default, so having a larger metadata for a single context class would not mean more data consumption. Furthermore, handling different contexts with different metadata with the current version of OpenAccess ORM would require you to use the AggregateMetadataSource class to manually merge the metadata from the different contexts and the ReplaceMetadata method to start using the merged metadata container. This inconvenience will be resolved with the Q3 2013 release when we are planning to implement a fine-grain control over the metadata management and automated aggregation out of the box.

2. Fetch Strategies are used mostly for performance optimization. For instance, if you have a Person and an Address classes, if you believe you will always need the address of each person, instead of loading it lazily you would load both with one single database call, instead of two separate calls. Therefore I would recommend you to use them selectively, deciding whether they are necessary based on each scenario in your application.

3. If you mean OpenAccess ADO API, it is indeed designed to be used only as a last resort, along with the cases when you need to call stored procedures and functions.

4. In general I would recommend moving functionality to the database only for scenarios, where you are very certain that you won't be changing your back-end from MySQL to something different and in the same time you have very time-sensitive but complex operations in your data access and business logic. One of the best features of OpenAccess ORM is the fact that you can relatively cheaply move from one back-end type to another, which is almost lost as a benefit if you implement your logic in the database.

Could you elaborate more about your idea of using OpenAccess ORM together with ADO.NET - what parts would you implement using plain ADO.NET code? We might be able to recommend you certain OpenAccess ORM features that would help.

In general, I would recommend you to take a look at the several approaches we have defined for handling the OpenAccessContext in web applications, which can be found in this video or this documentation article. Also It might be useful to see why we think OpenAccess ORM is capable of handling complex scenarios, explained in this blog post.

I am looking forward to your feedback or any other questions you might have on OpenAccess ORM usage in web scenarios.

Regards,
Ivailo
Telerik

[LordRevan007]

Thanks for the answer and excuse for such a late response. After researching through the documentation I found out, that OpenAccess has everything that I need in terms of low level ADO.API. Right now I hadn't done any stress test in terms of perfomance but at a couple of hundres of records everything seems to go smoothly. 

Another good tip would be to use the second level cache, but here I think I have a problem. Right now if I use L2 cache and I make any modifications inside the database the context would have to be initialized to be up to date with the modification. Is there any work around for this issue?

[Kristian Nikolov]

Hello Sorin,

Telerik OpenAccess ORM has two levels of cache. Per context instance Level 1 cache which is always enabled and Level 2 cache which is shared by all context instances using a single connection string. To register changes in the records in the database made outside the context, you need to release entirely or partially (depending on your scenario) the objects held in the cache - both Level 1 and Level 2.

• For Level 1 cache use the ClearChanges() method of the context
  

  context.ClearChanges();

• For Level 2 cache use the EvictAll() method
  

  context.LevelTwoCache.EvictAll();

I hope this will resolve your issue.

Regards,
Kristian Nikolov
Telerik