1

I'm designing an accounting application with more than 400 tables in SQL Server.

About 10% of those tables are operational tables, and the others are used for decoding and reference information.

For example, Invoice tables (Master and details) use about 10 table to decode information like buyer, item , marketer and ... .

I want to know is it acceptable to cache decode tables in asp.net cache and do not query them from SQL Server (I know that changes to cache items should commit on SQL Server too). And use cache items for decoding?

I think it makes it so much faster than regular applications.

Maybe all of them together(Cache tables) are about 500 MB after some years because they don't change frequently.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amir
  • 352
  • 3
  • 10
  • 2
    sql server has its own caching..... for the most, a well designed db isn't going to be slow unless you are hammering it. – Keith Nicholas Nov 29 '15 at 06:55
  • @KeithNicholas I know sql server has . but if i use sql server cache I have to gather all operating data with decode information . This scenario has two cost : 1- Join tables in sql server . 2- size of data transfer from sql server to application server – Amir Nov 29 '15 at 07:10
  • 1
    @Keith, +1, and, you can make your application much slower if you cache tables with thousand, ten thousands (accounting application? Millions?) of rows in memory as data tables and access it by DataTable.Select(). [See metrics here](http://stackoverflow.com/questions/3298167/datatable-select-and-performance-issue-in-c-sharp/33385906#33385906) – Oguz Ozgul Nov 29 '15 at 07:14
  • 1
    You could use something like Redis as a cache, in front of your DB. – Pierre-Luc Pineault Nov 29 '15 at 07:16

1 Answers1

4

If you've got the RAM then it's fine to use 500 MB.

However unless you have a performance problem now then caching will only cause problems. Don't fix problems that you haven't encountered, design for performance and optimize only when you have problems - because otherwise the optimization can cause more problems that it solves.

So I would advise that usually it is better to ensure that your queries are optimized and well structured, you have the correct indexes on the tables and that you issue a minimum amount of queries.

Although 500MB isn't a lot of data to cache, with all due respect, usually SQL Server will do a better job of caching than you can - providing that you use it correctly.

Using a cache will always improve performance; at a cost of higher implementation complexity.

For static data that never changes a cache is useful; but it still needs to be loaded and shared between threads which in itself can present challenges.

For data that rarely changes it becomes much more complex simply because it could have changed. If a single application (process) is the only updater of a cache then it isn't as difficult, but still not a simple task.

I have spent months optimizing a offline batch processing system (where the code has complete control of the database for a period of 12 hours). Part of the optimisation is to use various caches and data reprojections. All of the caches are readonly. Memory usage is around the 10gb mark during execution, database is around 170gb, 60 million records.

Even with the caching there has been considerable changes to the underlying schema to improve efficiency. The readonly caches are to eliminate reading during processing; to allow multi threaded processing and to improve the insert performance.

Processing rate has gone from 6 items processed per second 20 months ago to around 6000 items per second (yesterday) - but there is a genuine need for this optimization as the number of items to process has risen from 100,000 to 8 million in the same period.

If you don't have a need then don't optimize.

Richard Harrison
  • 19,247
  • 4
  • 40
  • 67