1

i am a bit confused about the :

Query q = entityManager.createNativeQuery( sql ).setHint( QueryHints.HINT_FETCH_SIZE, "1000" );

what does QueryHints.HINT_FETCH_SIZE, "1000" exacly do does it start fetching rows with a size of 1000 untill its done so i would not run out of db memory or it will only give u 1000 rows?

PWL
  • 23
  • 3
  • Tunes the prefetch value in the JDBC driver. A performance optimization that fetches 1000 rows at a time (instead of 1). – Elliott Frisch Jun 16 '21 at 07:08
  • so if i have huge db and fetch it with the size of 1000 i will still likely run out of db memory . – PWL Jun 16 '21 at 07:13
  • No. I don't know why you would think that it consumes db memory to FETCH rows to a client. It consumes client memory though. – Elliott Frisch Jun 16 '21 at 07:14
  • i will get the sql error so i tracked the error to the kubernetes db pod with a memory of 500mb and when i increase the memory it will run the sql query so it consumes db memory . – PWL Jun 16 '21 at 07:21
  • 1
    The fetch size optimizes network roundtrips to the database. 100 rows with fetch size 1 = 100 fetches, 100 rows with fetch size 100 = 1 fetch. The database uses the same amount of memory regardless, but the client can use less memory by tuning the fetch size **if** it is processing the results in a way that takes advantage of it. If you're loading all the results in a `List`, the client memory use is the same and you might as well fetch them all at once. – Kayaman Jun 16 '21 at 07:54
  • Thanx for the answer :) – PWL Jun 16 '21 at 09:40

0 Answers0