Slow when process 500,000 records using java program

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

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;

somethings wrong ... we process a 100,000 records from a ms sql server db in 45 seconds

>> 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..

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

Yes, it still seems to be normally very slow, but there is some other factor behind it ;-)

Once again, what processing do you do, how do you it (i.e. which algorithm do you use)?

If you're using cursor-oriented methods that provide navigability of result sets, this can slow things down greatly

Yup, try a CachedRowSet. Might help you improve your performance.

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

Well, haven't worked with onstat -gr, so I guess I can't say anything more on this page.

>>
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

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

Did you try a disconnected RowSet like CachedRowSet?

Every resultset once used will be closed except for the main loop of resultset.

You can still use a CachedRowSet for your outer ResultSet too.

>>Obviously an index on field1 will improve select performance

I meant field2 actually, but field1 would be good too

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

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.

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

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.

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

Accept: mayankeagle (last comment)