Clever Geek Handbook

Most efficient way to query the database using JDBC?

I need to get data from multiple tables, so I used a query with N outer outer joins. It seems to me that this might be a waste of performance, since I am getting a cartesian product of a large amount of data. What's the preferred way to do this for better performance? I'm going to make N + 1 small queries. Am I on the right track?

I know it has little to do with JDBC specifics. I want to get data from one table and do left outer joins for other N tables. The result set gets very large because I am getting a Cartesian product. For example:

table1data1, table2data1, table3data1

table1data1, table2data2, table3data1

table1data1, table2data1, table3data2

table1data1, table2data2, table3data2

I know that if I make multiple queries to the database (for example, in my example I get 1 record for table1, 2 records for table 2 and 2 records for table 2), I will make a lot of database calls, But I tested this way and it looks much faster.

+3


source to share


2 answers


This is really not JDBC specific. Generally speaking, depending on the amount of data returned, you will get the best performance by getting everything in the same result set. N + 1 queries tend to make many round trips to the database. Does the result set have fields that you don't want? Can you truncate the returned columns? This would be the first step, if possible.



+1


source


I think your current approach from getting a lot of data in one trip to the database is the correct approach. However, if you find yourself executing the same query many times with different parameters, it is more convenient to write it as a stored procedure using bind variables. But I am definitely avoiding hacking your JOIN into somewhat smaller requests.



0


source






More articles:


All Articles