Page 3 of 3 FirstFirst 123
Results 41 to 58 of 58

Thread: Connection Pool

  1. #41
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,434
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by Tolls View Post
    Blame eejit users who don't bother logging off.
    A database has limited resources, and clogging them up with twits who aren't doing anything is a waste...:)
    That's why I always deploy my database services with my usb driven Rotating Knives (tm). If a user walks away from the keyboard without returning the precious connection a simple signal through the usb bus takes care of the moron (after the event the Connection is released automagically ;-) That should teach those stoopid users ;-)

    kindest regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  2. #42
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Brings an interesting interpretation of bleeding edge development.

  3. #43
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Hi, Thank you for the time you spent answering my questions. I decided to use connection pool
    because of the time it will take when establishing a connection from a network(LAN).

    I try to compare 2 connections from 2 computer, one uses connection pool, the other one uses direct connection.
    Trial# 1 - 3, both computer try to establish connection at the same time.

    TEST 1: Connection pool seems to be faster than direct connection. With time interval of 15 seconds each trial.
    RECORD TO RETURN: 60
    POOLED CONNECTION:
    --------- .:: TRIAL #1 ::. ----------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 281 milliseconds

    --------- .:: TRIAL #2 ::. ----------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 0 milliseconds

    --------- .:: TRIAL #3 ::. ----------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 0 milliseconds

    DIRECT CONNECTION:
    --------- .:: TRIAL #1 ::. ----------
    after execute
    after close
    TIME CONSUMED: 344 milliseconds

    --------- .:: TRIAL #2 ::. ----------
    after execute
    after close
    TIME CONSUMED: 15 milliseconds

    --------- .:: TRIAL #3 ::. ----------
    after execute
    after close
    TIME CONSUMED: 16 milliseconds
    TEST#2: Pooled Connection gives Java Heap (out of memory).
    RECORD TO RETURN: 200,000
    POOLED CONNECTION:
    --------- .:: TRIAL #1 ::. ----------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 9582 milliseconds

    --------- .:: TRIAL #2 ::. ----------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    Java Heap

    DIRECT CONNECTION:
    --------- .:: TRIAL #1 ::. ----------
    after execute
    after close
    TIME CONSUMED: 9495 milliseconds

    --------- .:: TRIAL #2 ::. ----------
    after execute
    after close
    TIME CONSUMED: 8165 milliseconds
    TEST#3: Direct connection is faster than Pooled connection
    RECORD TO RETURN: 100,000
    POOLED CONNECTION:
    --------- .:: TRIAL #1 ::. ---------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 6391 milliseconds
    --------- .:: TRIAL #2 ::. ---------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 6922 milliseconds
    --------- .:: TRIAL #3 ::. ---------
    Active: 1, Idle: 0
    Active: 0, Idle: 1
    TIME CONSUMED: 5078 milliseconds

    DIRECT CONNECTION:
    --------- .:: TRIAL #1 ::. ---------
    after execute
    after close
    TIME CONSUMED: 6172 milliseconds
    --------- .:: TRIAL #2 ::. ---------
    after execute
    after close
    TIME CONSUMED: 5860 milliseconds
    --------- .:: TRIAL #3 ::. ---------
    after execute
    after close
    TIME CONSUMED: 4954 milliseconds

  4. #44
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Not convinced you're timing what you think you're timing there.
    Running a query that involves gettig a connection, creating the statement then executing it will be faster via a pooled connection everytime except if there's fluctuations in network speed. The reason is the pooled connection does not have to connect. The unpooled connection does.

    If you're timing between a pooled connection and a connection that is held, then there should be next to no difference, outside of network fluctuations.

    Anyway, how many of these clients are there going to be?

  5. #45
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,434
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by mine0926 View Post
    TEST#2: Pooled Connection gives Java Heap (out of memory).
    Did you find the cause of that? I find this highly suspicious and my guess is that your pool is broken ...

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  6. #46
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Quote Originally Posted by JosAH View Post
    Did you find the cause of that? I find this highly suspicious and my guess is that your pool is broken ...

    kind regards,

    Jos
    I'm guessing it's not the same code.
    Though it could be that the statement/resultset aren't being closed on a connection.close(), that isn't the case with DBCP. They fixed the last leak like that 2 or 3 years ago, and that was on meta data.

    I'm guessing the OP isn't closing something like the connection. In the case of the non-pooled it will eventually get gc'ed. In the case of the pooled connection it will hang around leaking resource.

  7. #47
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    Not convinced you're timing what you think you're timing there.
    Running a query that involves gettig a connection, creating the statement then executing it will be faster via a pooled connection everytime except if there's fluctuations in network speed. The reason is the pooled connection does not have to connect. The unpooled connection does.
    then, Why direct connection is faster than pooled connection?

    Quote Originally Posted by Tolls View Post
    Anyway, how many of these clients are there going to be?
    Around 20 computers.

  8. #48
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,434
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by mine0926 View Post
    then, Why direct connection is faster than pooled connection?
    My guess: because your pool is broken; the numbers you get are irrelevant, first you have to fix your errors.

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  9. #49
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by JosAH View Post
    Did you find the cause of that? I find this highly suspicious and my guess is that your pool is broken ...

    kind regards,

    Jos
    Not yet. I am suspecting that the pool connection that I did is not true, does not actually doing what
    is suppose to do. I am about to ask this but would like to finish this thread first...

  10. #50
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    I'm guessing it's not the same code.
    Though it could be that the statement/resultset aren't being closed on a connection.close(), that isn't the case with DBCP. They fixed the last leak like that 2 or 3 years ago, and that was on meta data.

    I'm guessing the OP isn't closing something like the connection. In the case of the non-pooled it will eventually get gc'ed. In the case of the pooled connection it will hang around leaking resource.
    I am using prepared statement, and I am closing both connection and prepared statement. I would like to post the
    code here but it is kind of long and this is the shortest code I can make to example here at forum.

  11. #51
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Quote Originally Posted by mine0926 View Post
    then, Why direct connection is faster than pooled connection?
    Which pool is it?
    Which version?

    As I say, I suspect you're not timing what you think you're timing. Or the pool is broken.
    But if it's DBCP I find that hard to believe since it's used all over the place.

    Quote Originally Posted by mine0926 View Post
    Around 20 computers.
    OK, that's not a problem. Presumably it's a lan as well.

  12. #52
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    It is Apache DBCP, I also believe that the problem is not in Apache DBCP.

    On first test what I am trying to get the time consumed is the whole process of JButton's action event.
    Java Code:
     private void buttonActionPerformed()
     {
        long timeStart = Calendar.getInstance().getTimeInMillis();
    
        try
        {
            Connection conn = DriverManager.getConnection(
                    "jdbc:apache:commons:dbcp:example");
    
            PreparedStatement pstmt =
                    conn.prepareStatement("select * from table1 LIMIT 100000");
            pstmt.execute();
    
            System.out.println("\tActive: " + conPool.getNumActive()
                    + ", Idle: " + conPool.getNumIdle());
            pstmt.close();
            conn.close();
    
            System.out.println("\tActive: " + conPool.getNumActive()
                    + ", Idle: " + conPool.getNumIdle());
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        long timeEnd = Calendar.getInstance().getTimeInMillis();
        System.out.println("\tTIME CONSUMED: " + (timeEnd - timeStart + " milliseconds"));
     }
    Now, I move the startTime and endtime before and after connection, and the result is on second run pooled connection is faster
    than direct connection. What makes connection pool longer is in prepared statement. But both pooled connection
    and direct connection is using same query.

    Thanks,
    Last edited by mine0926; 06-16-2011 at 10:44 AM.

  13. #53
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    It's possible the Statement is wrapped as well, to ensure one a close on the connection that Statements and ResultSets are closed.
    And I note you're not actually reading the resultset.
    So you would need to check whether the DBCP statement is grabbing a larger chunk of results than the statement via the DriverManager Connection.
    That query will not send all the results to your system unless you actually request it.

  14. #54
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    That query will not send all the results to your system unless you actually request it.
    I try to load the result using resultset and print records using System.out.println.

    Quote Originally Posted by Tolls View Post
    So you would need to check whether the DBCP statement is grabbing a larger chunk of results than the statement via the DriverManager Connection.
    How will I do that?

    Thanks,

  15. #55
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Quote Originally Posted by mine0926 View Post
    I try to load the result using resultset and print records using System.out.println.
    Not according to that code there you're not.

    Quote Originally Posted by mine0926 View Post
    How will I do that?

    Thanks,
    No idea.
    Presumably it's a DBCP setting, which will be whatever the DBCP default is if you haven't set it manually.
    Same goes for the Driver you're using.

    I do think you're overanalysing this, though.

  16. #56
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    No idea.
    Presumably it's a DBCP setting, which will be whatever the DBCP default is if you haven't set it manually.
    Same goes for the Driver you're using.
    Oh ok..

    Quote Originally Posted by Tolls View Post
    I do think you're overanalysing this, though.
    You thing so? All I want is to test and prove that the connection pool that I did is correct.
    And to do this, I am thinking that it should respond faster that direct connection.

  17. #57
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,936
    Rep Power
    19

    Default

    Connection pools are a plugin thing.
    I don't really understand what you mean by "correct".
    If each pool is only holding onto a single connection (which is probably all you need) you'll be fine.

    Timing connections is notoriously unreliable since there's so much else that affects the result...chief being the network the connection sits over.

  18. #58
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    Connection pools are a plugin thing.
    I don't really understand what you mean by "correct".
    Though connection pool is plug-in, I would like to test the correct setup that will fit for this like if I need to pool preparedstatements or not, something like that.


    Quote Originally Posted by Tolls View Post
    Timing connections is notoriously unreliable since there's so much else that affects the result...chief being the network the connection sits over.
    You're right, honestly, I did not really thought about network connection.


    @JosAh and Tolls - Thanks for your help and the time you spent here, and also your patience. :)

Page 3 of 3 FirstFirst 123

Similar Threads

  1. Access to app server db connection pool
    By metazone in forum JDBC
    Replies: 3
    Last Post: 03-26-2011, 03:45 PM
  2. Replies: 2
    Last Post: 12-15-2010, 06:11 AM
  3. using snaq.db for connection pool
    By Li Xiao in forum JDBC
    Replies: 1
    Last Post: 08-08-2010, 10:28 AM
  4. Using Database connection pool
    By amsagara in forum Advanced Java
    Replies: 0
    Last Post: 08-11-2009, 12:44 PM
  5. connection pool for db2
    By paty in forum JDBC
    Replies: 1
    Last Post: 08-06-2007, 02:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •