• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Slow when process 500,000 records using java program

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
dekoay
Asked:
dekoay
  • 10
  • 6
  • 3
  • +3
1 Solution
 
JavatmCommented:

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
 
Tommy BraasCommented:
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
 
bkfirebirdCommented:
somethings wrong ... we process a 100,000 records from a ms sql server db in 45 seconds
0
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!

 
Mayank SAssociate Director - Product EngineeringCommented:
>> 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
 
bkfirebirdCommented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
Yes, it still seems to be normally very slow, but there is some other factor behind it ;-)
0
 
Tommy BraasCommented:
Once again, what processing do you do, how do you it (i.e. which algorithm do you use)?
0
 
CEHJCommented:
If you're using cursor-oriented methods that provide navigability of result sets, this can slow things down greatly
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Yup, try a CachedRowSet. Might help you improve your performance.
0
 
dekoayAuthor Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
Well, haven't worked with onstat -gr, so I guess I can't say anything more on this page.
0
 
CEHJCommented:
>>
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
 
dekoayAuthor Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
Did you try a disconnected RowSet like CachedRowSet?
0
 
dekoayAuthor Commented:
Every resultset once used will be closed except for the main loop of resultset.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
You can still use a CachedRowSet for your outer ResultSet too.
0
 
CEHJCommented:
>>Obviously an index on field1 will improve select performance

I meant field2 actually, but field1 would be good too
0
 
dekoayAuthor Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
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
 
dekoayAuthor Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
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
 
dekoayAuthor Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Accept: mayankeagle (last comment)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now