-1

I'm having problems in generating a report the result reaches more than 500,000 lines. Believe me, this result is already filter. The query (DB2) runs almost instantly, but the the interaction in resultSet is absurdly slow.

I'm doing several tests to try to improve this process but so far without success. - At first was converting the direct data for the bean (used for report generation), but is very slow and the database gives timeout. - I tried to turn into a simpler process for testing (resultSet to HashMap) unsuccessfully - Used the setFetchSize configuration (2000) for the statement - I looked on the possibility of using thread safe, but does not support resultSet

Already modified the timeout of the bank to increase the processing time, but my problem was not resolved.

Anyway, already tried several possibilities. Does anyone have any tips or solution to my problem?

Luiz Paulo
  • 189
  • 1
  • 1
  • 6
  • How long does it take? 500,000 rows is a lot of data... – Thilo Dec 17 '14 at 01:55
  • In other words, you are trying to process 500K rows in Java. You should find a way to process them in the database instead. – mustaccio Dec 17 '14 at 02:25
  • We need way more details, like the code you're trying to run. I'm with mustaccio, though - pulling that many rows into Java is going to run into problems. – Clockwork-Muse Dec 17 '14 at 09:37

1 Answers1

0

First of all let me clear, Reporting, Report Generation task should never be done on application DB.

Application DB, Transactional DBs are designed for fast transactions which doesnt involve heavy result fetching, processing. Those tasks should be handled on DW server or standby replicas.

Second,

Reporting application logic should be processed in less crowded hours (when system is not used by users i.e. nights)

  1. If possible put your processing logic on DB side in form of procedures (maths part) with efficient queries to improve the performance in terms of processing and data transfer.
  2. Try to collect reports periodically using triggers/scheduled jobs etc. and while creating reports use those intermediate reports instead of DB (As you said your query execution is not a problem, but this will save iterating over a large set.) You can use values from intermediate reports thus iterating frequency will be less.
Nachiket Kate
  • 8,473
  • 2
  • 27
  • 45
  • ... the division is really more of one relating to different tables, although certainly separate physical boxes helps. It's not completely clear what the OP is doing, although it looks like the db itself isn't likely the issue. For all we know, he could be doing the process touching the reporting db anyways. – Clockwork-Muse Dec 17 '14 at 09:34