4 Replies Latest reply: Jul 7, 2011 11:20 AM by 839973 RSS

    execution time for an insert/update

    839973
      Hello!

      We are using EJB entities 3.0 and JPA configured to run on WAS and DB2. We also are using Container Managed Persistence
      We have a transactional method let's name it addA(), when executed, ultimately inserts data in 11 DB2 tables.
      In some of the 11 tables there are could be multiple rows inserted, in average, about 2 inserts.
      We are using the EntityManager.persist method to handle each entity.
      The method completes in about 11 seconds when the resources on the server (CPU,memory) are in a good state (so not overloaded).
      Is this a reasonable/decent time for the operation we are trying to do?
      If not, what would be a reasonable running time for such an operation?
      What do we need to do in order to improve the performance and decrease the execution time, other than switching to BMP and coding manual SQL inserts?
        • 1. Re: execution time for an insert/update
          gimbal2
          user2617486 wrote:
          The method completes in about 11 seconds when the resources on the server (CPU,memory) are in a good state (so not overloaded).
          11 tables, 11 seconds. 1 second per table. That is pretty much terrible.
          What do we need to do in order to improve the performance and decrease the execution time
          Well first you need to figure out what the bottleneck is. Network performance? Badly setup database (huge indexes can be a good cause for delays)? Perhaps it is not even the queries but some other piece of code you are not considering that is actually the cause.
          • 2. Re: execution time for an insert/update
            839973
            Thank you for your reply.

            The 11 tables are not independent, but related between them by
            The network latency can not be considered in this case since we run the test application on the same WAS where the application resides so it no networking involved.
            I rather suspect, as you mentioned, the DB design. That is a legacy DB already containing production data, so this is an obstacle in re-factoring and re-design of the tables, etc.
            Do you have any idea how we can localize/isolate better the problem at the DB level?
            Can we programatically insert log statements to see how long it takes the processing on the WAS and how long takes the actual SQL statements execution once they hit the DB2 database?
            • 3. Re: execution time for an insert/update
              gimbal2
              user2617486 wrote:
              Do you have any idea how we can localize/isolate better the problem at the DB level?
              Can we programatically insert log statements to see how long it takes the processing on the WAS and how long takes the actual SQL statements execution once they hit the DB2 database?
              You need help from a DBA, you can't reason this problem away. You need cold hard facts from whatever tooling the database provides. Of course you could try adding log statements to see how long each database operation is taking on the Java side of things, but that only proves that it is slow, not WHY it is slow.
              The network latency can not be considered in this case since we run the test application on the same WAS where the application resides so it no networking involved.
              and the database runs on that machine as well? This is new information you are pulling out of your hat by the way, now all of a sudden there are two applications? And with the limited information you give I am to assume you are having performance problems from the test application and not from your "main application"? Otherwise I see no point in you making this argument.
              • 4. Re: execution time for an insert/update
                839973
                The DB2 is located on another machine, let's call it M1
                The WAS is located on M2 and on that server there are running 2 applications
                - the main application that provides the API to insert data
                - the test application which is used to call that API

                The test application does nothing but calling the API exposed in the main application, so the problem is with the main application.

                So you are right about networking, it could be a delay on the M1-M2 path. Any ideas on how to determine/measure how much % from this 11sec are due to the network latency?

                Thank you in advance