aspose file tools*
The moose likes JDBC and the fly likes Reading data from multiple tables. Any better way than this? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reading data from multiple tables. Any better way than this?" Watch "Reading data from multiple tables. Any better way than this?" New topic
Author

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

Ravi Gupt
Greenhorn

Joined: Oct 16, 2007
Posts: 17
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 ??

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

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?


OCUP UML fundamental and ITIL foundation
youtube channel
Ravi Gupt
Greenhorn

Joined: Oct 16, 2007
Posts: 17
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

chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1845
    
  16

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.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Reading data from multiple tables. Any better way than this?