?
Solved

Slow when process 500,000 records using java program

Posted on 2004-04-12
27
Medium Priority
?
483 Views
Last Modified: 2008-02-26
Dear Experts,

I am using map and arrays in my Java program to process a reading and verification from database which consists of 500,000 rows and from multiple tables and it takes almost a week to complete the verification process.... Therefor I am looking for solutions from the experts on how to rectify this and reason for the cause of slow process...


Thank you in advance.
0
Comment
Question by:dekoay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
  • +3
27 Comments
 
LVL 15

Expert Comment

by:Javatm
ID: 10810318

Hi Friend;

I'm not sure what the exact problem is but you can use a Java Profiler for you to know the
exact problem of your program. In any case like memory usage and so-on.

You can download JProfiler which is free for 10 days after that you need to pay for it :

http://www.ej-technologies.com/products/jprofiler/overview.html
http://www.ej-technologies.com/products/jprofiler/java-profiler-tour.html

There is also one which is free :

http://ejp.sourceforge.net

Hope that helps . . .
Javatm
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10810478
What type of processing and verification are you doing on the data?

A simple way to do 'profiling' is to add timing statements at various junctures in the code.

long startTime = System.currentTimeMillis();

<... processing ...>

long endTime = System.currentTimeMillis();

long elapsedTime = endTime - startTime;
0
 
LVL 4

Expert Comment

by:bkfirebird
ID: 10810490
somethings wrong ... we process a 100,000 records from a ms sql server db in 45 seconds
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:Mayank S
ID: 10810554
>> somethings wrong ... we process a 100,000 records from a ms sql server db in 45 seconds

Depends upon various factors. How many users do you have connected to the database at one point of time? How many processes/ threads are running? How much available memory do you have? And so on..
0
 
LVL 4

Expert Comment

by:bkfirebird
ID: 10810581
i agree with you mayan ... it depends on a lot of factors, i just wanted to emphasize that a week for 500K records is pretty slow
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10810609
Yes, it still seems to be normally very slow, but there is some other factor behind it ;-)
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10810640
Once again, what processing do you do, how do you it (i.e. which algorithm do you use)?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10812115
If you're using cursor-oriented methods that provide navigability of result sets, this can slow things down greatly
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10813736
Yup, try a CachedRowSet. Might help you improve your performance.
0
 

Author Comment

by:dekoay
ID: 10838608
Well, I have only one user connected to Informix Server ver 9 which is running on Unix platform. The process I am running is Month End Run process which is perform a verification and calculation on certain amount based on certain conditions and update the status back to the respective table.

For your information, the codes are written by someone else and I an to improve the performance of the code. However, I notice one part that cause it slow is the SQL statement which is

SELECT unique(field_1) from table1 WHERE field2 = 'L'.

The above statement is the main loop for the verification and updating.

On the other hand, I went to unix to run

   onstat -gr sql

Usually, a normal execution of sql statement which run on the onstat command will produce:
Current SQL statement:

   Select ....... FROM ....

Last Parsed SQL statement:
   SELECT ..... FROM .....

However, there are certain record executed by sql statement remain stuck in a few loop (b4 it jumped to next statement)which produced result with only Last parsed SQL statement as below:

SELECT totaltpdlimit from msrisfacpol where polno = 12345678 and facind = '1'

Any one know what does it mean?

Thank you in advance
dekoay





0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10839273
Well, haven't worked with onstat -gr, so I guess I can't say anything more on this page.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10841973
>>
SELECT unique(field_1) from table1 WHERE field2 = 'L'.

The above statement is the main loop for the verification and updating.
>>

Obviously an index on field1 will improve select performance. When you say 'loop' do you mean that this is executed in a loop with different parameters for field2? If so, using a PreparedStatement will also improve performance
0
 

Author Comment

by:dekoay
ID: 10853622
Hello CEHJ,

No, what I mean is the main program loop... exmple:

while (rs.next(){
...
...
...

}

However, the statement above takes quite a while to return the resultset. WHen I use group by field_1, it takes just a few second to get the resultset.

On the other hand,  u know any reason why there are certain statement stuck and shown as below

Last Parsed SQL statement:
   SELECT ..... FROM .....


Thank you in advance
dekoay
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10856437
Did you try a disconnected RowSet like CachedRowSet?
0
 

Author Comment

by:dekoay
ID: 10856825
Every resultset once used will be closed except for the main loop of resultset.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10856904
You can still use a CachedRowSet for your outer ResultSet too.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10858463
>>Obviously an index on field1 will improve select performance

I meant field2 actually, but field1 would be good too
0
 

Author Comment

by:dekoay
ID: 11011590
Hi Mayankeagle,

U mention about CachedRowSet..... Sounds very interesting... However, I try to used it but it prompt me error when I run the code.

The message is NoDefClassFound in sun/jdbc/rowset/CachedRowSet.

Eventualy, I have download the rowset.jar from sun and set the classpath to it and use import statement like 'import sun.jdbc.rowset.CachedRowSet' in my program. It sill prompt me the message. The funny thing is I can compile the program but I cannot run the program.

It happen at the line
in the code: CachedRowSet crs = new CachedRowSet();

So, pls advise me what other thing that I should set or put in my program to ensure the program able to execute. Frankly speaking this CachedRowSet is still an early access stage according to the sun.


Thanks in advance and yur help will be really appreciate.



dekoay

0
 
LVL 30

Expert Comment

by:Mayank S
ID: 11012383
Are you using any IDE for running it? If so, it will use its own classpath and not the system classpath. I hope you added the entire JAR file to the classpath and not just the containing directory.
0
 

Author Comment

by:dekoay
ID: 11036545
Hello Mayan,

I wsn't use IDE tools to wirte the code... just a noremal editor. If you could, show me some syntax how you compile the code t=with rowset.jar that makes the program executable...


Thx
Dekoay
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 11047836
If its giving you exceptions, then perhaps you don't have the javax.sql package (not java.sql but javax.sql). CachedRowSet extends from javax.sql.RowSet as far as I remember.
0
 

Author Comment

by:dekoay
ID: 11201342
hello mayan,


where can I get this package? I search everywhere but ould not find it.... eventually,. Mind if you could show me where I can this lib will be appreciate...



thank for the help mayan.



regrds
dekoay
0
 
LVL 30

Accepted Solution

by:
Mayank S earned 1000 total points
ID: 11201452
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 11683291
Accept: mayankeagle (last comment)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question