How-to establish an efficient cache strategy for DataTable(s)

How-to establish an efficient cache strategy for DataTable(s)

• yhz

  are DataViews automatically cached
  i couldn't find anything related to it, in the documentation ......
  
  except under System.Web.Caching which i won't need as i don't use ASP ......
  do you have any keywords i could search for
  
  also, to answer you, i planned to use a Loading Marquee at startup, exactly for this :-)
  
  Thank you.
  
  

• SGK

  Sorry but i haven't been precise enough,
  
  I'm not using SQL, i use an external XML file,
  so all DataWizard/SQL features won't be available to me,
  
  also, i rarely need a precise entry, rather a group of entries,
  when i need only one, it is at the end of the cascaded filter, thus it's very fast.
  
  i only use DataTables, DataViews & a DataSet;
  so, is Indexing really worth for me
  
  also, my DB doesn't need to update in real-time, it is offline
  
  
  i think it could come from the DISTINCT function,
  because at first it was very slow, however,
  by using Data.ToTable, it just goes as fast as possible,
  
  maybe it's that the bottleneck;
  
  (while testing, i found it's only slow on first generation of Distinct fields, after it's pretty fast)
  (this is why i was thinking about a stupid static cache)
  
  at last, i know if it was C or C# it would be straight forward,
  but i have no knowledge of them :-(
  
  anyway,
  
  thank you for your help :-)
  
  have a good week-end !
  
  

• Nicolas Mueggler

  No, DataViews are not automatically cached. You would need to create a static collection in an appropriate place to store them.
  
  

• BBVB

  Agreed. Put proper indexes on table and SQL Server will handle all the caching for you automatically and efficiently. Plus you will always have the latest data. Only good case I've seen for DataTable caching is if the table is fairly static and commonly used.
  
  

• BenLovesSQL

  You should add the necessary indexes to your database to maximize performance and then execute the queries as needed. Caching so many copies of the same data is probably not a good idea.
  
  

• yubai

  alright, i'll try that
  
  (not so easy for me )
  
  thanks !
  
  

• Ghawas

  In that case I would recommend using a single DataTable with cached DataViews. A DataView will create an index on the underlying datatable based on the filter criteria you supply it. The memory requirements of these views will be much smaller than actually storing many separate copies of the data. The initial cost of creating all the views might be high, but you can frontload that to the startup process of your application, and after that you should see pretty good performance.
  
  
• How-to establish an efficient cache strategy for DataTable(s)