-2

Let's say I need to get 10 entities from the database using the listIds. If I get from DB each entity by id using listIds.stream -> repository.findById(id), it works faster than findAllById(listIds) why is this happening?

val listIds = List.of(1,2,3,4,5,6,7,8,9,10);

// example faster:
val entityList = listIds
            .stream()
            .map(id -> repository.findById(id).orElseThrow())
            .collect(Collectors.toList());


// example slower
val entityList = repository.findAllById(listIds);

the two examples are not executed in the same transaction, they are just different implementations of the method

Ilya Slezkin
  • 1
  • 1
  • How much faster? Like milliseconds or like minutes? What kind of numbers are we talking about here? – Federico klez Culloca Feb 08 '23 at 09:15
  • I would suggest tracing the generated SQL. Apparently ten quick primary key reads are faster than one more complex IN query. The SQL and possibly database plans might tell you why. – ewramner Feb 08 '23 at 09:17
  • "How much faster? Like milliseconds or like minutes? What kind of numbers are we talking about here?" "milliseconds" – Ilya Slezkin Feb 08 '23 at 09:23
  • @IlyaSlezkin go on... please give us actual numbers. Also, how did you measure them? – Federico klez Culloca Feb 08 '23 at 09:23
  • There are hundreds of factors that could be at play depending on your test. Entity caching within the client (first/second level caches), query and statement caches, database operations, table setup, amount of data. First guess is that you might have run this test a few time and so your findById isn't really hitting the database. – Chris Feb 08 '23 at 16:03

1 Answers1

1

Perhaps most of the time is spent communicating with the server where the database is located. In the first case, 10 times are allowed, and in the second case, although there are many queries, the communication is once.

Imagine if I were the server and we were separated by 100 meters. Every time you request data from me, you need to run to me. If you ask me to tell you the temperature of each hour yesterday, you only need to run back and forth once. But if, every time you ask me what is the temperature at 1:00 and what is the temperature at 2:00.. you need to run back and forth 24 times！

时间只会一直走
  • 813
  • 8
  • 18