Solved

Slow when process 500,000 records using java program

Posted on 2004-04-12
27
472 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
  • 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
 
LVL 30

Expert Comment

by:mayankeagle
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:mayankeagle
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:mayankeagle
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:mayankeagle
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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:mayankeagle
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:mayankeagle
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:mayankeagle
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:mayankeagle
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:
mayankeagle earned 250 total points
ID: 11201452
0
 
LVL 30

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now