locked
Retrieving data from session instead of a database query RRS feed

  • Question

  • User-2024710007 posted

    I am using Kendo gantt widget to display Tasks for a project, unlike the simple task module that is in Telerik documentation where a task has a title, start, end date.

    My tasks are rather heavy and include many other fields, so I want to optimize the process of retrieving these task in a way that if the database has not been updated, then I get the same tasks from the session, and if the database has been updated, then I make a query and call latest from the database.

    Looking at the following code:

    private static bool UpdateDatabase = false;

    and

    public virtual IList<TaskViewModel> GetAll()
    {
            var result = HttpContext.Current.Session["GanttTasks"] as IList<TaskViewModel>;
    
            if (result == null || UpdateDatabase)
            {
                result = db.GanttTasks.ToList().Select(task => new TaskViewModel
                {
                    TaskID = task.ID,
                    Title = task.Title,
                    Start = DateTime.SpecifyKind(task.Start, DateTimeKind.Utc),
                    End = DateTime.SpecifyKind(task.End, DateTimeKind.Utc),
                    ParentID = task.ParentID,
                    PercentComplete = task.PercentComplete,
                    OrderId = task.OrderID,
                    Expanded = task.Expanded,
                    Summary = task.Summary
                }).ToList();
    
                HttpContext.Current.Session["GanttTasks"] = result;
            }
    
            return result;
    

    The issue I am facing is that my application is multi-tenant meaning different user some might belong to the same tenant and others could belong to different tenants will be using application simultaneously, there for the static variable is out of question. my question is what is the best way to optimize in such case, should I save the update database in a session and use it instead of the static variable, therefore it be user aware of that particular user activity if he update or not the db, how this will perform if say 200 users are logged at the same time, is there another way you might know or suggest for such case.

    Thanks

    Tuesday, July 28, 2020 10:50 AM

All replies

  • User475983607 posted

    tree123

    should I save the update database in a session and use it instead of the static variable, therefore it be user aware of that particular user activity if he update or not the db, how this will perform if say 200 users are logged at the same time, is there another way you might know or suggest for such case.

    The database is the system of record.  Storing data in Session creates a greater chance of concurrency issues and stale data.  Whether caching data in Session poses an issue in your design is unknown.  The community cannot see your design but most importantly the Kendo Gantt chart is a Telerik control.  You need Telerik support.

    Tuesday, July 28, 2020 11:09 AM
  • User-2024710007 posted

    thanks mgebhard for your response,

    Lets imagine same scenario without Telerik widget , it is just an example of a single get call to the database that gathers complex data. if the session stores user specific data, can you explain how the risk of concurrency in this case will occur.?

    I understand that i shared a limited code example, i am not asking for a full evaluation of the problem. rather than someone to share a link or point out an example of best practices to cache or optimize in a multi-tenant architecture similar to this scenario. 

    Tuesday, July 28, 2020 11:36 AM
  • User475983607 posted

    tree123

    Lets imagine same scenario without Telerik widget , it is just an example of a single get call to the database that gathers complex data. if the session stores user specific data, can you explain how the risk of concurrency in this case will occur.?

    If the data only belongs to the user and no other uses can change the data then caching the data in Session is fine.  The code becomes a bit more complex due to syncing between Session and the data store. 

    If other users can affect the data then the problem becomes more complex as you'll to handle concurrency.  A distributed cache might be a better approach.

    Tuesday, July 28, 2020 12:17 PM
  • User753101303 posted

    You could include the tenant id in a "key" so that you have a dedicated cache for each tenant (if this is your question). Caching is harder for frequently updated data (at worst you need to reload often which kind of defeat keeping data in cache). My first move here would be likely to make sure there is no optimization possible (all users in a tenant are seeing all tasks right away ?).

    When  caching it's likely best to start by frequently used read only data if possible.

    Tuesday, July 28, 2020 1:06 PM
  • User-474980206 posted

    session is a per user cache, but it sounds like you need a per tenant cache. The issue with caches is when to invalidate. You can use a cache with a database dependency notification.

      https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/caching-data/using-sql-cache-dependencies-cs

    Or you could add a cache table to the database keyed by tenant (and whatever else is required). the cache table can just have a json column with the data. you can use database triggers to update the cache (or just delete it).

    Tuesday, July 28, 2020 3:41 PM
  • User-2024710007 posted

    Thanks for your response, the tasks are accessed by different users under one tenant, as you suggested I need to cache by tenant, which makes it a bit more complected, as such any user could alter / edit the data. so maybe not a good idea to cache data if not read only. 

    Saturday, August 8, 2020 1:25 PM