• Post Reply Bookmark Topic Watch Topic
  • New Topic

Writing the results of this ResultSet to File is VERY slow (SOLVED)  RSS feed

 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I hope I'm posting in the right place. Since I can't tell whether my problem is in my use of ResultSet or with the OutputStream, I'm posting in general.

This output of this code is running very slowly. There are about 10,000 records for the period in question that my ResultSet object is returning. It is then formatting and writing the results to a BufferedWriter "out".



When this code is first executed, the time it takes the cursor to move to the next row in the while loop is less than a second. It then slows down considerably as it continues to run. I just timed it at record 122 (of 10,000) and it took more than 15 seconds for the cursor to move to the next row. Thanks for helping me figure out what I'm doing wrong.
 
Rob Spoor
Sheriff
Posts: 21094
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to know if it is the writing that is causing the slowness or not, simply take out the actual writing (and flushing) code and try again. If it still is slow then it's not caused by the I/O.
 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd be a little surprised if it's the IO, but you're flushing between each chunk, which may somewhat defeat the purpose of using a buffered writer (I'm not sure of the internals off the top of my head).
 
Ernest Friedman-Hill
author and iconoclast
Sheriff
Posts: 24217
38
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's almost certainly the ResultSet.TYPE_SCROLL_SENSITIVE flag, which instructs the driver to re-check the database every time a row is fetched. This flag can drastically reduce your performance.
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! Earnest, you are correct. It was the SENSITIVE flag. I changed it to INSENSITIVE and the report was done in about 15 seconds. 15 seconds versus a day to get through 1,000 records is pretty good.
 
Paul Clapham
Sheriff
Posts: 22509
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually you would be still better off using a FORWARD_ONLY result set, since that's what your program actually does. It never does any scrolling (reading back old records again) so there's no point in saying it does. If you say you want a scrolling result set, the JDBC driver has to make every single record available in case you might go back and ask for it again. Whereas if you ask for a forward-only result set, the JDBC driver can happily discard a record the instant you ask for the next one. Much easier on the memory if you do that, then.

In fact that's the case for about 99% of JDBC applications in real life, I would guess, so I don't know why the tutorials persist in starting with anything but FORWARD_ONLY examples.
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That makes a lot of sense. I changed the ResultSet to Type.FORWARD_ONLY. I'll go back through my code and change all appropriate ResultSets to that flag.

Thanks for your help.

Best,
Al
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!