• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Ravi Gupt
Greenhorn
Posts: 17
  • 0
  • Mark post as helpful
  • send pies
  • 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 ??

 
Jan Cumps
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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

 
chris webster
Bartender
Pie
Posts: 2369
31
Linux Oracle Postgres Database Python Scala
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic