0

I have a java based web application in which I have included the Connection Pooling method.

Code is as follows :

public final class Database {
    private static final String SQL_EXIST = "show tables;";
    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
    }

    private static final BasicDataSource dataSource = new BasicDataSource();

    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://link");
        dataSource.setUsername("user");
        dataSource.setPassword("pass");
        dataSource.setMaxTotal(10);
    }

    private Database() {
        //
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

Now in my application whenever I need a connection, I use Database.getConnection(). Issue is that, whenever am calling Database.getConnection(), it's taking around 900 ms .

Am also closing the connection after every DB operation.

Code which I used is :

System.out.println("Time before callingg Func is : "+(System.currentTimeMillis()-main_time));
ABC a=new ABC();    
count = a.d(Database.getConnection(), jObj.getString("a"), jObj.getString("b"),c,jObj.getString("d"),jObj.getString("e"));
System.out.println("Time after callingg Func is : "+(System.currentTimeMillis()-main_time));

Output which I get is :

Time before callingg Func is : 61
sql Query time is : 266
Time after callingg Func is : 1123

EDIT :

I calculated the time when conn is not closed.

I also added a timestamp before going into the db function and a timestamp in the first line of the function. Then I calculated the time from 2nd line to the end of the function which is actually sql time.

Time before callingg Func is : 68 and time in ms is : 1490001506121
Time in ms when inside function is : 1490001506843
Diff in time is : 722
sql Query time is : 260
Time after callingg Func is : 1050

EDIT 2

Removed the connection part from the function i.e.

count = a.d(jObj.getString("a"), jObj.getString("b"),c,jObj.getString("d"),jObj.getString("e"));

and then the time calculations are :

Time before callingg Func is : 65 and time in ms is : 1490003211049
Time in ms when inside function is : 1490003211049
Diff in time is : 0
sql Query time is : 1
Time after callingg Func is : 66
driftking9987
  • 1,673
  • 1
  • 32
  • 63
  • Why not just open a connection pool and not to close after each connection. User permission is verified at your logic and the connections use a single application account. – Alex Suo Mar 20 '17 at 08:54
  • http://stackoverflow.com/questions/4938517/closing-jdbc-connections-in-pool This link says that we should always close a connection. Having said that, I also tried without closing the connection. It still takes the same amount of time. – driftking9987 Mar 20 '17 at 08:56
  • You didn't read your quoted post carefully. In the accepted post it said explicitly that calling `close()`on a pooled connection only return the connection to the pool. I also doubt you measure the time correctly by claiming that without closing connection it takes same amount of time. – Alex Suo Mar 20 '17 at 09:04
  • To me it looks like you are measuring roundtrip time of `a.d(...` call, not only the `getConnection`. And we completely do not see how you measure "sql Query time" ... there could be a lot other stuff going on there, too. – Fildor Mar 20 '17 at 09:12
  • Please check the edit. I added the timestamp whne conn is not closed. @Fildor, am creating a variable as `long startTime=System.currentTimeMillis();` and then at the end, just before retuen am printing `System.out.println("sql Query time is : "+(System.currentTimeMillis()-startTime));` – driftking9987 Mar 20 '17 at 09:25
  • Still you have the `jObj.getString` calls that may also need some time. Just assuming they are fast is not enough when blaming the pool. – Fildor Mar 20 '17 at 09:31
  • @Fildor, I was sure but then just to show the numbers, I removed the connection part from the function and then ran the function. You can see that the time taken is 0. So `jObj.getString`, doesnt really takes time but connection does. – driftking9987 Mar 20 '17 at 09:50
  • Just because you set setMaxTotal to 10 connections, it doesn't mean that they have actually been created. Use getNumActive to see how many active connections are currently in the pool. – dsp_user Mar 20 '17 at 10:03
  • @dsp_user, number of active connections are 4-5. I printed that before calling the DB function. – driftking9987 Mar 20 '17 at 11:31
  • Well, I'm not sure why it's taking so long. I've written a simple implementation of a connection pool (http://stackoverflow.com/questions/37128603/is-singleton-not-good-for-get-jdbc-connection-any-good-implementation-of-connec/37133098#37133098) so maybe you can take a look. I basically get a DB connection either from the DriverManager or DataSource but store all the connections within my pool. It works pretty well, at least for simple applications. – dsp_user Mar 20 '17 at 11:43

0 Answers0