Which one is better way to select some data with mybatis

Question

I'm developing rest api server with java, mybatis.

There is one issue on my mind with select some data. I don't know which one is reasonable or better way for server and developer.

Please let me hear your opinions.

This data has to be returned by a method. First one is using a lot of select methods in mapper.xml and mapper.java like below

<select id="a" resultType="Integer" parameterType="ParameterMap">
  select a from a where a=a
</select>
<select id="b" resultType="Integer" parameterType="ParameterMap">
  select b from b where b=b
</select>
<select id="c" resultType="Integer" parameterType="ParameterMap">
  select c from c where c=c
</select>
.....

And the other way is merging select sentence on one select method like below.

<select id="abc" resultType="Integer" parameterType="ParameterMap">
  select a 
  ,(select b from b where b=b)
  ,(select c from c where c=c)
   ....
  from a where a=a
</select>

I guess connect to database could use lots of resources, so second one is better way. However on the other hand, maybe following second way makes hard to maintain the code.

What do you think is a better way?

Answer 1

Performance topics are important, so it's good that you ask such questions.

You asked a general question so that the answer will be general too.

Generally, you can't achieve both good performance and maintenance; therefore, usually, you have to choose which of these drivers you will follow. Usually, flexibility, maintainability, readability, usage are more preferable drivers than performance.

Coming back to your question ...

Single selects provide good maintenance and freedom for clients of your repository. If they need to select from B only, they will execute select from B only. So if clients need to select single tables at once, the first option is the best.

Note that in the standard configuration of servers/applications, you either do one transaction per the whole service method call or one transaction per HTTP request, so it doesn't really matter how many selects you do inside.

The second option is hard to maintain and will kill the performance if clients need to select single tables. You will force them to select the table they need plus all other tables. The second option is recommended if you have a scenario that clients need to select all of the tables each time(F.ex when you want to map three tables to one java class). Usually, when you have to select several tables, you do join between them.

The second option might improve performance in following

1. You have one connection for all selects when you use the repository in a service or a controller that is not transactional, without enabled one session per HTTP request. In most projects, you make one transaction per a service method call or even one request, so you will not have such gains there.
2. If you use joins (instead of subqueries), then the database should optimize the query and make it as performant as possible
3. It's a kind of bulk query, so some of the operations are done by database only once so there might be small gain here.
4. When you use to solve your N+1 problem with joined tables.

Although this might be an improvement of performance, gains will probably be small compared to standard SQL query optimizations techniques used by DBAs (indexing, etc.)

Wrapping up

1. Define what you need more: Maintenance or performance.
2. If it is maintenance, design your repository to be KISS, SOLID, DRY, etc.
3. If it is performance, do some performance tests to prove that the second solution improves your query before you implement it.
4. In general, favor the first solution other the second one.

Answer 2

If you can make sure that the result is only one row, I will do this

select * from
(
select a from a where a=a
) t1,
(
select b from b where b=b
) t2,
(
select c from c where c=c
) t3

Uses fewer resources and is easy to maintain