Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > pgsql.general > #36

Advice on fetching large number of rows with jdbc client with fixed setFetchSize property

Show all headers | View raw

I am using Postgres version 9.4.14 with Cent OS 6.9 . My core tables have hundreds of millions of records and have large web clients which access postgres using jdbc client. As part of our client needs we export last 7 days data from postgres db . We have common framework using in house ORM based on JDBC. There are 5 steps we do as part of export,

   1. Set JDBC property, where setFetchSize set to 10K
   2. Formulate the SQL SELECT query with necessary where criteria
   3. Prepare the SELECT query 
   4. Send bind param to this query
   5. Fetch result of the above query in batches 


 In my case above results in fetching approximate 780K records, and I end up doing 78 iterations. Out of 78,  most iterations finishes in matter of seconds. But there are few iterations which takes like 30-40 MINUTES. And I do not understand why.

 I need advice,
   1.  first is this the right way to fetch large datasets from db ? what other alternatives I have? 
   2.  Since I am bringing results in batches, does postgres repeats the query for every batch or how is the pagination done at db level ?  
   3. How can I debug slowness in iterations where it take 30-40 minutes.


Sincerely,
Bhushan

{code}
duration: 3353.494 ms  bind <unnamed>/C_255:
 duration: 136616.504 ms  execute <unnamed>/C_255:
 duration: 4044.062 ms  execute fetch
 duration: 5604.555 ms  execute fetch
 duration: 6739.133 ms  execute fetch
 duration: 5515.646 ms  execute fetch
 duration: 4787.372 ms  execute fetch
 duration: 5981.755 ms  execute fetch
 duration: 5347.695 ms  execute fetch
 duration: 5234.821 ms  execute fetch
 duration: 66140.751 ms  execute fetch
 duration: 259259.782 ms  execute fetch
 duration: 5021.827 ms  execute fetch
 duration: 5321.758 ms  execute fetch
 duration: 3974.821 ms  execute fetch
 duration: 7140.601 ms  execute fetch
 duration: 3444.857 ms  execute fetch
 duration: 2501.242 ms  execute fetch
 duration: 10085.228 ms  execute fetch
 duration: 6526.959 ms  execute fetch
 duration: 7748.472 ms  execute fetch
 duration: 5011.091 ms  execute fetch
 duration: 3654.584 ms  execute fetch
 duration: 6310.684 ms  execute fetch
 duration: 11663.159 ms  execute fetch
 duration: 4335.106 ms  execute fetch
 duration: 5802.454 ms  execute fetch
 duration: 4728.780 ms  execute fetch
 duration: 4905.621 ms  execute fetch
 duration: 2395.726 ms  execute fetch
 duration: 4717.428 ms  execute fetch
 duration: 4860.125 ms  execute fetch
 duration: 5566.658 ms  execute fetch
 duration: 5812.773 ms  execute fetch
 duration: 4269.370 ms  execute fetch
 duration: 6532.245 ms  execute fetch
 duration: 4880.061 ms  execute fetch
 duration: 3142.641 ms  execute fetch
 duration: 5027.824 ms  execute fetch
 duration: 6409.005 ms  execute fetch
 duration: 7131.011 ms  execute fetch
 duration: 4428.909 ms  execute fetch
 duration: 7018.613 ms  execute fetch
 duration: 3998.722 ms  execute fetch
 duration: 5925.292 ms  execute fetch
 duration: 6600.967 ms  execute fetch
 duration: 6491.373 ms  execute fetch
 duration: 3610.761 ms  execute fetch
 duration: 2461.907 ms  execute fetch
 duration: 2106.069 ms  execute fetch
 duration: 2338.225 ms  execute fetch
 duration: 2439.384 ms  execute fetch
 duration: 6560.654 ms  execute fetch
 duration: 6129.915 ms  execute fetch
 duration: 6232.821 ms  execute fetch
 duration: 5273.512 ms  execute fetch
 duration: 5170.681 ms  execute fetch
 duration: 3366.137 ms  execute fetch
 duration: 3110.553 ms  execute fetch
 duration: 3637.209 ms  execute fetch
 duration: 3329.326 ms  execute fetch
 duration: 6291.210 ms  execute fetch
 duration: 5444.250 ms  execute fetch
 duration: 5209.395 ms  execute fetch
 duration: 5307.647 ms  execute fetch
 duration: 4630.627 ms  execute fetch
 duration: 4978.659 ms  execute fetch
 duration: 2616.587 ms  execute fetch
 duration: 5931.865 ms  execute fetch
 duration: 5932.119 ms  execute fetch
 duration: 6713.955 ms  execute fetch
 duration: 3623.926 ms  execute fetch
 duration: 4356.517 ms  execute fetch
 duration: 5908.876 ms  execute fetch
 duration: 5683.423 ms  execute fetch
 duration: 2897838.851 ms  execute fetch
 duration: 42130.725 ms  execute fetch
 duration: 764191.431 ms  execute fetch
 duration: 8673.684 ms  execute fetch
 duration: 20050.957 ms  execute fetch
{code}

Back to pgsql.general | Previous | Next | Find similar

Thread

Advice on fetching large number of rows with jdbc client with fixed setFetchSize property bhushan.uparkar@gmail.com - 2018-10-13 08:49 -0700

csiph-web