This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Journey To Enterprise Agility and have Daryl Kulak & Hong Li on-line!
See this thread for details.
Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

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

 
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.
 
Sheriff
Posts: 21421
94
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.
 
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).
 
author and iconoclast
Sheriff
Posts: 24220
40
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.
 
Sheriff
Posts: 23640
48
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
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!