CODEWITHSUNDEEP
javasqljoinjdbc
ivanfoofoo
ivanfoofoo

Reputation: 145

Most performant way of querying database with JDBC?

I need to get data from several tables, so I used a query with N left outer joins. It seems to me that it may be a waste of performance since I get the cartesian product of lots of data. Which is the preferable way to this in order to achieve greater performance? I'm thinking of doing N+1 little queries. Am I on the right track?

I know, this has little to do with JDBC specifics. I want to retrieve data from a single table, and make left outer joins to other N tables. The result set gets very big because I get a cartesian product. For example:

table1data1, table2data1, table3data1

table1data1, table2data2, table3data1

table1data1, table2data1, table3data2

table1data1, table2data2, table3data2

I know that if a make several queries to the database (such as in my example I get 1 record for table1, 2 records for table 2 and 2 records for table 2) I'll make a lot of roundtrips to the database. But I've tested this way and it looks a lot faster.

Upvotes: 3

Views: 254

Answers (2)

Aaron
Aaron

Reputation: 57748

I think your current approach off getting a lot of data in one trip to the database is the right approach. However if you find yourself executing the same query many times with different parameters, it is more performant to write it as a stored procedure using bind variables. But I would definitely shy-away from breaking your JOIN into several smaller queries.

Upvotes: 0

Chris
Chris

Reputation: 28064

This really isn't JDBC specific. Generally speaking, depending on the amount of data being returned, you'll get better performance retrieving everything in a single result set. N+1 queries tends to make for a lot of round trips to the database. Does the result set contain fields you don't need? Can you trim the columns being returned? That would be a first step, if possible.

Upvotes: 1

Related Questions