none
What is the best way to keep linq RAM cache for C#? RRS feed

  • Question

  • I am new to linq, so my questions may sound stupid, but i hope for help )) I have database with a couple of tables - one is huge and rarely used (for logs) and one is rather small (<10mb of data) but is used very often (a lot of reads and updates, some rare writes)

    So i have two datasets for logs and data, while writing to logs i can use usual direct linq queries, but for data table i want some cache in RAM to reduce database load

    Seems like i can do it by simply adding object between dataset and queries:

          DCDataContext _pDC = new DCDataContext();
          var _buffer = from a in _pDC.pTables
               select a;
          var _result = from a in _buffer
               where a.pId>1
               select a;
          pNameList.ItemsSource = _result;
    
    • DCDataContext is data context of MSLinqToSQLGenerator with data table in it
    • pId is primary key id for data table

    If i'm not wrong, such code will create _buffer object by loading whole database table in it, and all _result queries will be executed in ram as they are pointed not to SQL object (like _buffer query) but are pointed to local resource _buffer, that already contains data from SQL

    So i have some questions:

    1. am i right?
    2. is there any kind of better solution?
    3. how can i sync _result with database? if i will add new row or update old row in _resul object, i will need to store this changes - maybe appling changes to _pDC is the best way (so i will need to re-execute _result query to get it in sync with _pDC, and submitChanges() on _pDC when programm will have spare time to update real SQL db)?
    4. when i call submitChanges - what data is actually updated - only changed one (since last read/update?) or there is some overhead?

    Sorry for being so dumb ))

    Thursday, April 21, 2011 6:58 PM

Answers

  • http://msdn.microsoft.com/en-us/library/bb896272.aspx

    Read this, this explains how related objects are loaded.

    Look your code will be executed later on when you submit the changes, you can use Log property to see the sql generated.  This will generated two db calls unless you explicitly tell linq what to load as explained in the link.  As you see you can tell linq to what to load to some degree so in most cases there is no need to explicitly do as you ask.  For example for a heavy table linq will load only what is needed for the query result unless you want to delay that and it will only do it when it is needed.

    You don't need to call the whole table, you can call only the records you need.

    Regards

    • Marked as answer by orlovsn Friday, April 22, 2011 7:19 PM
    Friday, April 22, 2011 12:15 PM

All replies

  • Using linq is different than pure sql, for example in linq most queries executions are deferred so you can manipulate the query as much as you want without a performance hit by going to the db.

    Besides you keep a local in memory copy of the object so subsequent queries to the same object will only need one db trip as long as they don't require new info.

    Only when you submit changes the object and the db will be synced, and yes as long as the object tracking is enabled then only the parts that have changed will change.  This is a bit more complex in concurrent scenarios as you will need to resolve conflicts etc.

    Regards 

    Thursday, April 21, 2011 7:59 PM
  • so i don't need any _buffer as it is already cached at execution time? sorry, i can't catch - if i will do this:

    DCDataContext _pDC = new DCDataContext();
       var _result1 = from a in _pDC.pTables
       where a.pId == someVar1
          select a;
       pNameList1.ItemsSource = _result1;
       var _result2 = from a in _pDC.pTables
       where a.pId == someVar2
          select a;
       pNameList2.ItemsSource = _result2;
    
    

    there will be only one query to real SQL database as both queries are targeted to one datacontext?

    or there will be 2 queries as long as there are 2 different query bodies

    in my first variant i suppose i can do as many queries as i need while i target them to _buffer, because _buffer is local object, but if LINQ can cache database while creating datacontext ... that's just brilliant! but another question then - what if i will query heavy table (around 5Gb - that's average size of logs table)? it will cache only some amount i hope? can i set cache size and it's direction (leave start or end on cache overflow)?

    Thursday, April 21, 2011 9:38 PM
  • http://msdn.microsoft.com/en-us/library/bb896272.aspx

    Read this, this explains how related objects are loaded.

    Look your code will be executed later on when you submit the changes, you can use Log property to see the sql generated.  This will generated two db calls unless you explicitly tell linq what to load as explained in the link.  As you see you can tell linq to what to load to some degree so in most cases there is no need to explicitly do as you ask.  For example for a heavy table linq will load only what is needed for the query result unless you want to delay that and it will only do it when it is needed.

    You don't need to call the whole table, you can call only the records you need.

    Regards

    • Marked as answer by orlovsn Friday, April 22, 2011 7:19 PM
    Friday, April 22, 2011 12:15 PM
  • Thanks a lot!
    Friday, April 22, 2011 7:20 PM