Efficient way to fetch data from database

Question

I want to know which query style is better & efficient

I am using jdbc to fetch data from multiple tables(I am having more than 10 tables to fetch data). All data are related with keys

for that I am considering two ways

1) I am using joins to fetch data from database

Query s="select a.*,b.*,c.*......";//fetchin all data using joins in single query

Preparedstatement ps = con.prepareStatement(query);//con is a connection object
ResultSet res = ps.executeQuery();
//fetching data from resultset res
con.close();

2)I am taking data from each individual table

Query s="select from table a";
Preparedstatement ps = con.prepareStatement(s);
ResultSet res = ps.executeQuery();
//fetching data from resultset res
Query s1="select from table b";
Preparedstatement ps1 = con.prepareStatement(s1);
ResultSet res1 = ps1.executeQuery();
//fetching data from resultset res1
.............
//continue for all remaining tables
con.close();

I would like to know if there is any other/better method to it.

EDIT: I am using very large amount of data. What about hibernet other methods

As for massive data and using Hibernate I just found some thoughts on that: architects.dzone.com/articles/hibernate-and-querying-50k — Ben
– Ben, Commented Jan 27, 2014 at 10:45

Ben · Accepted Answer · 2014-01-27 10:44:34Z

1

The best way in my opinion is to handle all SQL stuff within one statement. As you proposed as version 1). If this is too complex for you in the Java sources, you could use a stored function in the database (I know only the Oracle/MySQL possibilities) and handle more complex statements in there. In Java you call just the stored function/procedure and handle the results in Java. This works fine for me.

The other possibility is to use ORM like Hibernate or whatever. But for small things this would be overkill :)

With 1), propably combined with stored functions but not really necessary, you let the database cache your statement and get the imho best performance. If you load table after table in your approach 2) and have to process the data and store it. That might be not that efficient.

edited Jan 27, 2014 at 10:44

answered Jan 27, 2014 at 10:25

Ben

4016 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3227094 Over a year ago

I have large amount of data.Is ORM is batter for large data

Ben Over a year ago

No ORM is in imho just for abstraction. What's large? I tested a function call with a Collection as result with 19.200 rows using 8byte data in each field, with 30 fields and had my results extracted in around 100ms as max. Using Oracle12c and Java7 on a local SSD :)

user3227094 Over a year ago

Yes approx 15000 rows. So will it be better. For me writing sql query takes long time

Ben Over a year ago

I could help you with some hints on that if you can provide some details on your SQL ;)

Brian · Accepted Answer · 2014-01-27 10:43:34Z

0

Are your data actually linked? Or are they distinct conceptual data sets, e.g. you're loading Products in query1, and say, Sizes, Colours, Towns int queries 2, 3 and 4? If the latter, then distinct queries could well be best, avoiding problems arising from aritificial JOIN constructs.

Edit - if tables are indeed related, then JOINing sounds appropriate, provided you use an efficient query. In your SELECT be sure to pull back only fields you need (ignore "extra" ones) to aid performance.

edited Jan 27, 2014 at 10:43

answered Jan 27, 2014 at 10:30

Brian

6,4483 gold badges35 silver badges51 bronze badges

Collectives™ on Stack Overflow

Efficient way to fetch data from database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related