• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Reading data from multiple tables. Any better way than this?

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

In my application someone has written code which reads data from multiple tables. The code looks like this:
...........
...........

readTable(TABLE1);
readTable(TABLE2);
readTable(TABLE3);
readTable(TABLE4);
readTable(TABLE5);
readTable(TABLE6);

..........

void readTable(String tableName){


InitialContext initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/");
dataSource = (DataSource) envContext.lookup(STORE_DS_NAME);
Connection conn = dataSource.getConnection();
selectStatement = connection.prepareStatement(SELECT_UNSYNC_RECORDS);
resultSet = selectStatement.executeQuery();

.......... //process result set
..........
finally{
conn.rollback();
conn.close();
}
}

The problem I see here in this code is : its creating/getting connection object from DataSource for each method call and closing it in finally . Isn't it an overhead. thats is multiple DB calls !! I understand that Datasource take connections from pool but still getting connection from pool and returning it for each method call ?? I mean come on.. there must be a better way.

Any better solution for this kinda situation ??

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is a bit of overhead, but calling rollback() when not needed is also overhead.
Have you measured the cost of getting and returning the connection, versus the complete cost to read from the 6 tables?

 
Ravi Gupt
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jan,

Thanks for the response. Can we just get connection once and then perform read from 6 tables and finally return connection? isn't it better than borrowing-releasing 6 times ?

I am not sure how expensive is to locate datasource from jndi and get a connection out of it 6 times ie. for each table individually.

What is the possible drawback of this approach (that is getting connection once only...for 6 reads.)

Thanks,
Ravi

 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do these tables all return data in the same format? If so, you could think about combining the queries using UNION:

SELECT col1, col2, ...
FROM table1
UNION /* or UNION ALL */
SELECT col1, col2, ...
FROM table2
UNION /* or UNION ALL */
SELECT col1, col2, ...
FROM table2
...etc.

UNION eliminates duplicates, while UNION ALL will return all rows including duplicates. UNION ALL is also faster, because it doesn't have to the extra sorting to identify duplicates. But this only works if you are returning the same data-types and number of columns in each SELECT.

You might also want to look at what you're doing with all this data you're fetching from different tables. Is this process something you could perform as a stored procedure entirely on the database instead e.g. with PL/SQL if you're on an Oracle database? If so, then there is no need to fetch the data at all, which will save you time and bandwidth.
 
reply
    Bookmark Topic Watch Topic
  • New Topic