0

I have a software who does a heavy processing based on some files. I have to query some tables in SQL Server in the process and this is killing the DB and the application performance. (other applications use the same tables).

After optimizing queries and code, getting better results but not enough. After research I reached the solution: Caching some query results. My idea is cache one specific table (identified as the overhead) rows that the file being process need.

I was think in using AppCache Fabric (I'm on MS stack), made some tests it have a large memory usage for small objects ( appcache service have ~350MB of ram usage without objects). But I need to make some queries in these result table (like search for lastname, ssn, birthdate etc.)

My second option is MongoDb as a cache store. I've research about this and most of people I read recommend using memcached or Redis, but I'm using Windows servers and they're not supported officialy.

Using mongo as cache store in this case it is a good approach? Or AppFabric Caching + tag search is better?

MaltMaster
  • 758
  • 1
  • 10
  • 25

2 Answers2

1

It is hard to tell what is better because we don't know enough about your bottlenecks. A lot is depending on quality of the data you're discussing. If the data is very static and is not called constantly but to compile the data set is time-consuming, the good solution might be to use the materialized view. If this data is frequently called than you better caching it on some server (e.g. app fabric). There are many techniques and possibilities. But you really need to think of the network traffic, demand, size, etc, etc. And it is hard to answer this here without knowing all the details. Looks like you are on the right way but may be all you need is just a parametrized query. Hard to tell. But I would add Materialized view into the roster that you just posted. May be all you need is to build this view from all the data you need and just access its contents.

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • The data don't change frequently. The real problem is the amount of time I need to query SQLServer per file line + the type of query I do (My searches is mostly in varchar columns). This query is blocking other applications and also taking too long to process a file. Today I process a batch (~1MB per file) 260MB in 1.07 hours, in the near future I'll have GBs of files to process and the processing can't last more than one day. – MaltMaster Apr 28 '13 at 20:52
  • 1
    If the data pretty static and your only concern that building result set takes a long time, you can create "materialized view" ("indexed view" in Sql Server). See here: http://msdn.microsoft.com/en-us/library/dd171921%28SQL.100%29.aspx You might need to create additional tables, etc., to create your search capabilities. Then, some process will populate all that. Then your data will be highly available on the Sql Server. If that doesn't fit your conditions, you can always use server memory with caching mechanism of your choice. – T.S. Apr 29 '13 at 16:45
  • Indexed view will work for now and save some costs for a while, but in the future I'll have to use cache (in a dedicated server) to reduce this overhead. Thanks @T.S. – MaltMaster May 02 '13 at 14:34
0

My question to you would be that what are your long-term goals or estimates for your application? If this is the highest load you are going to expereince then tuning the DB or using MVL would be an answer. But the long term solution to this is distributed caching and you are already thinking along those lines. Your data requirements is what we'd called "reference data" or "lookup-data" and once you are excuting multiple lookups with limited DB resources there will be performance issue and your DB will become a performance bottleneck.

So the solution, that you are already thinking of, is caching this "reference" data in a cache without the need to go to the database, while, at the same time, keeping cache synchronized with the Database.

Appfabric I wouldn't be too sure about as it will have the same support issues that you mention. What is your budget like? Can you think about spending on a cachisng solution like NCache?

Andy H
  • 132
  • 3