Announcement Announcement Module
Collapse
No announcement yet.
In memory database or Caching solution Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • In memory database or Caching solution

    Hello we have an application running, using Spring and database is SQLServer.
    The application is giving us problems wrt to performance.
    We are thinking of using some caching mechanism in our application.

    Any thoughts which is better In memory databases like HSQLDB or H2 or
    java caching solutions like ehcache or JCS.

    We want to cache lots of data in memory/disk that is primarily queried again and again from database and run our application validations against that data. Basically mostly reads very little writes.

    Any suggestions, tips will be very helpful

    Thanks
    Diana

  • #2
    The easiest solution would be to use a cache.

    What kind of technology do you use for database communication? direct JDBC, and OR Mapper like Hibernate or something else? And do you need to select collections, or just single items?
    Last edited by Alarmnummer; Mar 13th, 2007, 06:10 AM.

    Comment


    • #3
      We are using Spring JDBC directly. no hibernate or anything else.

      More than easy we would like to know which will give me the best results.

      we are also thinking of using JDBC directly instead of using Spring JDBCTemplate.

      Would appreciate your inputs on this.

      Also sometimes we need to access collections, like the entire row from the database currently. or sometimes single item like one column value from the DB.

      Comment


      • #4
        More than easy we would like to know which will give me the best results.
        I would go for good enough Best results are difficult at best.

        we are also thinking of using JDBC directly instead of using Spring JDBCTemplate.
        Why? Spring JDBC support only makes JDBC easier to use but doesn't add much by itself.

        Also sometimes we need to access collections, like the entire row from the database currently. or sometimes single item like one column value from the DB.
        I really don't know enough about your application to point you in the good direction that suits your application best.

        But there are many ways to speed up data access. Data that is expensive to calculate, and is allowed to be a little stale (for example a few minutes old is acceptable, a report containing all trains that are too late for a certain station). This can be improved by caching that information. If there is a lot of information to be cached, you could go for a datagrid (tangosol, javaspaces).

        So it really depends on the type of applications. And I don't have enough experience to point you in the good direction. Maybe someone with more experience could jump in?

        Comment


        • #5
          If your queries are trivial then an in memory database should be all that necessary. But if that's not the case you can add caching.

          I found that a properly designed DAO structure would allow you to change either decision pretty easily. The DAO itself would either implement direct calls to the database or in memory database. You would use the declarative caching support in Spring Modules to add caching with AOP.

          For my application, I chose in memory database data and caching. That was because our database doesn't allow direct selects, we have to use stored procedures. Initially I was only going to use the in memory database, but for logic that had a involved dozens of non-trivial queries per operation, the remote stored procedure was more efficient. Once I added caching the performance improved dramatically.

          Comment


          • #6
            I would agree with lots of the previous points. I would also add however, do some profiling and see where the problems are. I helped out on a project where people were introducing all kinds of crazyness to improve performance, turned out changing the SQL had much more impact than all the other changes.

            Comment


            • #7
              Originally posted by dinsush View Post
              Hello we have an application running, using Spring and database is SQLServer.
              The application is giving us problems wrt to performance.
              We are thinking of using some caching mechanism in our application.

              Any thoughts which is better In memory databases like HSQLDB or H2 or
              java caching solutions like ehcache or JCS.
              I would take a step back and profile your application to identify where the bottlenecks are, for example, what methods are called the most and what take the longest time and so on.

              Your application may well be IO bound but it is much better to check out what is happening first and then identify a solution, rather than trying out a solution first.

              It is quite likely that caching would speed up your application (be careful, it needs to be cluster safe), but you could waste a lot of time implementing this if your problem was caused by something else, like, for example, a small connection pool, memory leaks or record locking.

              Also bear in mind with Hibernate you tend to cache at the class level, so it makes sense to know which classes are the culprits in any persistence.

              Comment


              • #8
                Thanks a lot to everyone for suggestions.
                We did some profiling, and found that most of the time was taken in making database trips either for selects or updates.

                Well updates we are now using batch processing and for selects, we are planning to use caching.

                The reason I am hesitant in using in - memory database is mainly because the amount of data I would like to cache is huge and with large chunk of data HSQLDB, that we evaluated seems to become slow or marginally faster than what we have been doing so far.

                I haven't explored tangolsol or javaspaces, would like to explore that, any pointers towards that will be helpful.

                Diana.

                Comment


                • #9
                  Tangosol, Javaspaces et. al. are certainly options. It how you integrate those technololgies that's important.

                  Spring Modules caching integrates with Tangosol as well as other free caches (OsCache, EHCache, JBossCache). The issue becomes one of cache capability. What kind of clustering, extra abilities, etc.

                  Comment


                  • #10
                    Originally posted by dinsush View Post
                    Thanks a lot to everyone for suggestions.
                    We did some profiling, and found that most of the time was taken in making database trips either for selects or updates.

                    Well updates we are now using batch processing and for selects, we are planning to use caching.

                    The reason I am hesitant in using in - memory database is mainly because the amount of data I would like to cache is huge and with large chunk of data HSQLDB, that we evaluated seems to become slow or marginally faster than what we have been doing so far.

                    Diana.
                    There is a big section in hibernate in action all about caching and performance tuning.

                    I don't think you'll gain anything from using an in memory database to be honest.

                    On a final note, don't forget, "Tin is cheap". Sometimes it's just easier to be pragmatic and throw some hardware at a problem. A monster server is after all about the same price as a contractors wages for a week.

                    Comment


                    • #11
                      Originally posted by Paul Newport View Post
                      There is a big section in hibernate in action all about caching and performance tuning.

                      I don't think you'll gain anything from using an in memory database to be honest.

                      On a final note, don't forget, "Tin is cheap". Sometimes it's just easier to be pragmatic and throw some hardware at a problem. A monster server is after all about the same price as a contractors wages for a week.
                      In our performance testing environment in the current project, we will probably use hp rx4640s, which are priced somewhere in the area of 60,000 GBP... I would LOVE to earn that much in a week... :-)

                      BR,

                      Robert

                      Comment


                      • #12
                        Originally posted by dinsush View Post
                        The reason I am hesitant in using in - memory database is mainly because the amount of data I would like to cache is huge and with large chunk of data HSQLDB, that we evaluated seems to become slow or marginally faster than what we have been doing so far.
                        If you are dealing with huge amounts of data I'm not sure HSQLDB will play very nicely. I've had lots of problems in the past as the data set gets larger. H2 database was much better, but if you are dealing with lots of data I'm not sure how this would scale.

                        Comment


                        • #13
                          I have no clue not only about details but also about general characteristics of your project, so can only wild guess...

                          But in 95% of cases bad performance is due to the bugs in the DB design and/or badly written SQL. So first make sure that in this area all is Ok. Only then search for another solutions.

                          BTW, sometimes relatevely small changes in the DB design give tremendous performance gain.

                          If you will provide more info, then some more targeted advices may be given.

                          BTW, overhead of Spring JDBCTemplate is rather minor, so changing to plain JDBC will save you (in the best case) only few percents, more likely fraction of percent.

                          Regards,
                          Oleksandr Alesinskyy

                          Originally posted by dinsush View Post
                          Hello we have an application running, using Spring and database is SQLServer.
                          The application is giving us problems wrt to performance.
                          We are thinking of using some caching mechanism in our application.

                          Any thoughts which is better In memory databases like HSQLDB or H2 or
                          java caching solutions like ehcache or JCS.

                          We want to cache lots of data in memory/disk that is primarily queried again and again from database and run our application validations against that data. Basically mostly reads very little writes.

                          Any suggestions, tips will be very helpful

                          Thanks
                          Diana

                          Comment


                          • #14
                            I guess efficient SQL is always a good starting point. As you say the improvements can be massive. It was mentioned that this is mostly reads. If that's the case, making sure the queries are efficient, and in-memory database and possibily a cache should solve most problems. I think the previous posts have summed up all the options in detail.

                            Comment


                            • #15
                              We are facing a similar issue: we have an application that must perform traversals of very large graphs. There are use cases when we need to traverse up and down a graph structure that contains, potentially, 200,000-300,000 nodes. Today, the app makes a trip to the DB for each node element and retrieves the IDs of the element's children and parents. The application then goes on to retrieve each child or ancestor element from the database. Needless to say, it is extremely inefficient. It works ok so far only because the data volume has not yet reached its projected size. We have been considering caching the data graph (elements and their associations) using in-memory and disk cache. Would EhCache or JCS be suitable for this? Can any of you guys suggest a good approach?

                              Also, are there any good examples of using spring-modules-cache - complete and working. SM 0.8 distribution does not seem to contain any...

                              Thanks for any advice!

                              Comment

                              Working...
                              X