Announcement Announcement Module
Collapse
No announcement yet.
Caching with Spring JDBC? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Caching with Spring JDBC?

    we have developed an application in Spring using Spring JDBC templates etc. Now there is a use case where we are refreshing user screen periodically querying db which contains thousands of records in resultset. For e.g. we are refreshing user screen to update screen with updated/added/deleted/modifed stocks periodically.

    Now this is big performance hit because although there is only one new stock which has been added in the database, querying db to refresh the screen brings back all stocks even which aren't touched or no change since last time queried. I want to develop some caching similar to Hibernate (EHCache) so it would only bring back updated results and rest will remain in the cache. Can anyone advise how to move forward in developing caching solution for Spring JDBC

    thanks,

  • #2
    Honestly speacking, I guess it is just undoable (speacking generally). In this aspect Spring JDBC is no different from plain JDBC and has no way to know what exactly was updated in a database.

    What you really can do - make some kind of cache just above the level of your DAOs (or whatever level responsible for CRUD operations for your objects) and call this cache instead of direct calls to DAOs. Spring AOP may be very useful at this point.

    Regards,
    Oleksandr

    Originally posted by javaxmlsoapdev View Post
    we have developed an application in Spring using Spring JDBC templates etc. Now there is a use case where we are refreshing user screen periodically querying db which contains thousands of records in resultset. For e.g. we are refreshing user screen to update screen with updated/added/deleted/modifed stocks periodically.

    Now this is big performance hit because although there is only one new stock which has been added in the database, querying db to refresh the screen brings back all stocks even which aren't touched or no change since last time queried. I want to develop some caching similar to Hibernate (EHCache) so it would only bring back updated results and rest will remain in the cache. Can anyone advise how to move forward in developing caching solution for Spring JDBC

    thanks,

    Comment


    • #3
      Caching with Spring JDBC?

      Add a timestamp field to the table (depending on what DB you have, it can auto-update itself when a row is modified), change the query to only pull rows since the last refresh, parse the new/updated rows, update your collection accordingly.

      John

      Comment


      • #4
        Originally posted by al0 View Post
        Honestly speacking, I guess it is just undoable (speacking generally). In this aspect Spring JDBC is no different from plain JDBC and has no way to know what exactly was updated in a database.

        What you really can do - make some kind of cache just above the level of your DAOs (or whatever level responsible for CRUD operations for your objects) and call this cache instead of direct calls to DAOs. Spring AOP may be very useful at this point.

        Regards,
        Oleksandr
        Thanks. I am leaning towards Spring AOP in combination of EHCache approach. Following articles also gave some insight http://javaboutique.internet.com/tutorials/ehcache
        http://opensource.atlassian.com/conf...ng+and+EHCache

        Use case for me is to update stock market picture on the clients screen. For e.g. there are 100 records on the user screen currently and now 101 record was added so what I am trying to achieve is only bring back 1 record (101th) from db and rest 100 from the cache. Does Spring AOP +EHcache sound like a correct solution? Any thoughts?

        Comment


        • #5
          Yes, it may be viable solution (IMHO). Probably, more simple (and less general) solution may fit your needs (but you need more exactly formulate them). Your example looks as if no updates are allowed, so data may be only added and cache as such is not needed. All that you need is to read newly added data from DB. How it may be achived highly depend on DB structure (if there is a way to easily identify new data. Take in account that if deletions are possible then you need to check for each record on user screen if it is still in DB so performance gain from cache would be severely reduced (alternatively you implement list of deleted records in DB).

          BTW, how much control do you have over DB structure (do you develop it along with you application or you have to process some existing DB or DB is intended to be used by multiply applications?

          Regards,
          Oleksandr

          Originally posted by javaxmlsoapdev View Post
          Thanks. I am leaning towards Spring AOP in combination of EHCache approach. Following articles also gave some insight http://javaboutique.internet.com/tutorials/ehcache
          http://opensource.atlassian.com/conf...ng+and+EHCache

          Use case for me is to update stock market picture on the clients screen. For e.g. there are 100 records on the user screen currently and now 101 record was added so what I am trying to achieve is only bring back 1 record (101th) from db and rest 100 from the cache. Does Spring AOP +EHcache sound like a correct solution? Any thoughts?

          Comment


          • #6
            Originally posted by al0 View Post
            Yes, it may be viable solution (IMHO). Probably, more simple (and less general) solution may fit your needs (but you need more exactly formulate them). Your example looks as if no updates are allowed, so data may be only added and cache as such is not needed. All that you need is to read newly added data from DB. How it may be achived highly depend on DB structure (if there is a way to easily identify new data. Take in account that if deletions are possible then you need to check for each record on user screen if it is still in DB so performance gain from cache would be severely reduced (alternatively you implement list of deleted records in DB).

            BTW, how much control do you have over DB structure (do you develop it along with you application or you have to process some existing DB or DB is intended to be used by multiply applications?

            Regards,
            Oleksandr
            I have control to DB structure upto some extent. Is it advisable (and makes sense) to use Hibernate for this piece of funtionality? In that case it would eliminate all overhead of me creating cache and all that stuff. let me know.

            thanks,

            Comment


            • #7
              Originally posted by javaxmlsoapdev View Post
              I have control to DB structure upto some extent. Is it advisable (and makes sense) to use Hibernate for this piece of funtionality? In that case it would eliminate all overhead of me creating cache and all that stuff. let me know.

              thanks,
              If DB is used by multiply applications and not all of them are developed in sync then usage of cache may be troublesome as you need some extent of cooperation between applications to ensure fast and reliable recognition of added/modified/deleted records (yes, for some DBs you may transparently achieve it by means of DB itself, but such DBs are relatively rare). And it is true regardless of Hibernate usage.

              So, if you wish to obtain well-grounded advice, you have to provide more detailed info about your case.

              Regards,
              Oleksandr

              Comment


              • #8
                Originally posted by al0 View Post
                If DB is used by multiply applications and not all of them are developed in sync then usage of cache may be troublesome as you need some extent of cooperation between applications to ensure fast and reliable recognition of added/modified/deleted records (yes, for some DBs you may transparently achieve it by means of DB itself, but such DBs are relatively rare). And it is true regardless of Hibernate usage.

                So, if you wish to obtain well-grounded advice, you have to provide more detailed info about your case.

                Regards,
                Oleksandr
                To begin with this is the use case:
                there is a method "getAllTrades()" which can be called by number of clinents. I don't want to hit db every time so I will store this result in a cache and for subsequent calls retrieve list from the cache. Now let's say from previous call of "getAllTrades()" and current call there are 10 more records added into database so cache implementation should be smart enough to not to do "retrieve all" but only update existing cache with 10 new records from the databse and likewise for deleted records.

                Comment


                • #9
                  First of all it should be a way determine which records are "new" (added or updated) and which are deleted. There are 2 approaches to ensure it existence - rely on the capabilities of DB server (which may or may not be present, I do not know which DB are you using) or rely on DB design and cooperation of the applications that modify DB (e.g. proper support for record versioning/timestamping etc.). If no of these approaches is applicable, then it is much better to forget about caching as efforts to maintain cache in sync with DB would be comparable to efforts for direct interaction with DB (i.e. you would add complexity without any real gain).

                  Regards,
                  Oleksandr

                  Originally posted by javaxmlsoapdev View Post
                  To begin with this is the use case:
                  there is a method "getAllTrades()" which can be called by number of clinents. I don't want to hit db every time so I will store this result in a cache and for subsequent calls retrieve list from the cache. Now let's say from previous call of "getAllTrades()" and current call there are 10 more records added into database so cache implementation should be smart enough to not to do "retrieve all" but only update existing cache with 10 new records from the databse and likewise for deleted records.

                  Comment


                  • #10
                    Originally posted by al0 View Post
                    First of all it should be a way determine which records are "new" (added or updated) and which are deleted. There are 2 approaches to ensure it existence - rely on the capabilities of DB server (which may or may not be present, I do not know which DB are you using) or rely on DB design and cooperation of the applications that modify DB (e.g. proper support for record versioning/timestamping etc.). If no of these approaches is applicable, then it is much better to forget about caching as efforts to maintain cache in sync with DB would be comparable to efforts for direct interaction with DB (i.e. you would add complexity without any real gain).

                    Regards,
                    Oleksandr
                    I am not quite understanding your post. Can you elaborate bit more what you meant by not gaining anything, straight to the point?
                    1)When there is a call for "insert()" of this list can't I make a call to update existing cache to ensure cache remains uptodate? what are the implications?
                    2)I am using Oracle database
                    3)Not sure how versioning/timestamp will help in here as to read that we still have to make db trip?

                    Comment


                    • #11
                      Originally posted by javaxmlsoapdev View Post
                      I am not quite understanding your post. Can you elaborate bit more what you meant by not gaining anything, straight to the point?
                      1)When there is a call for "insert()" of this list can't I make a call to update existing cache to ensure cache remains uptodate? what are the implications?
                      2)I am using Oracle database
                      3)Not sure how versioning/timestamp will help in here as to read that we still have to make db trip?
                      Ok. My post was written in assumption that database is updated not by your application only. Is it so?

                      Comment


                      • #12
                        Originally posted by al0 View Post
                        Ok. My post was written in assumption that database is updated not by your application only. Is it so?
                        Yes. Database is updated by my application ONLY AND NOTHING ELSE.

                        Comment


                        • #13
                          Originally posted by javaxmlsoapdev View Post
                          Yes. Database is updated by my application ONLY AND NOTHING ELSE.
                          Ok in this case (and assuming that you have enough control over DB structure to ensure existence of "version_number" field in each table that needs to be cached ) it is quite safe to use cache. What is easier - to bind cache into your existing configuration or switch to the Hibernate I can not say, but switching to Hibernate is anyway not so bad idea.

                          Regards,
                          Oleksandr

                          Comment


                          • #14
                            Originally posted by al0 View Post
                            Ok in this case (and assuming that you have enough control over DB structure to ensure existence of "version_number" field in each table that needs to be cached ) it is quite safe to use cache. What is easier - to bind cache into your existing configuration or switch to the Hibernate I can not say, but switching to Hibernate is anyway not so bad idea.

                            Regards,
                            Oleksandr
                            If I switch to hibernate that would be only for this particular use case. I won't touch any other existing Spring JDBC code. I am just trying to find out if this mix has any implications?

                            Comment


                            • #15
                              Originally posted by javaxmlsoapdev View Post
                              If I switch to hibernate that would be only for this particular use case. I won't touch any other existing Spring JDBC code. I am just trying to find out if this mix has any implications?
                              Generally speaking, such mix is very dangerous. Hibernate makes a lot of assumptions about how it manipulates objects in database and cache, and if your code not competely adhere with these rules then Hibernate can quite easily go weird (especially, when we speak about caching). So, IMHO, you have to take "all-or-nothing" decision.

                              Comment

                              Working...
                              X