Link to home
Start Free TrialLog in
Avatar of Faust
Faust

asked on

System resources(win386.swp)

Allow me to give you a bit of background.
The following code runs within an environment called Lotus Notes(IBM), this is why you have the entry point @
NotesMain(), within the code there is nothing that refers to Lotus Notes classes therefore it should not be of any concern(i hope), anyhow,
THE PROBLEM IS that this code eats up memory like crazy.
The resultset should return about 40K records and go through all of them(checking JDBC performance) and stick them into variables.
I left it running for 30 min after which the swap file size grew to 500MB
I'm new to java development so I know  I'm missing something very trivial.
Also the process of going through the records in the recordset seems to slow down with time(>resources) is there a way to accomodate for deteriorating resources.

Please advise

CODE:
import java.util.*;
import java.sql.*;
import lotus.notes.*;

public class QueryTableJDBC extends AgentBase
{
    public void NotesMain()
    {
        //set connection objects
          String status,no;
          double cost;
          String url="jdbc:odbc:Lotus";
          Connection con;
        Statement stmt;
        ResultSet rs;
        System.err.println("All variables set.");

        //construct query
          String query = "select NO,COST,STATUS from cost_table " +
                     "where STATUS='7'";
        //print out indicator to the console
          System.err.println("Query constructed.");
        try
        {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            System.err.println("sun.jdbc.odbc.JdbcOdbcDriver referenced.");
        }
        catch (java.lang.ClassNotFoundException e)
        {
            System.err.print("ClassNotFoundException: ");
              System.err.println(e.getMessage());
        }
          try
          {
                con=DriverManager.getConnection(url,null,null);
            System.err.println("Connection to source estabilished.");
                stmt=con.createStatement();
            System.err.println("stmt set");
                rs=stmt.executeQuery(query);
                  System.err.println("Query executed,resultset object populated.");
            while(rs.next())
            {
                status=rs.getString("STATUS");
                no=rs.getString("NO");
                cost=rs.getDouble("COST");
            }
           
                stmt.close();
                System.err.println("Closing statement object.");
                con.close();
                System.err.println("Closing connection to data source.");
          }
          catch(SQLException ex)
          {
                System.err.println("SQLException: " + ex.getMessage());
          }
    }
}
Avatar of Faust
Faust

ASKER

Edited text of question.
Avatar of Faust

ASKER

Edited text of question.
Avatar of Faust

ASKER

Edited text of question.
Try this at the end of your NotesMain:

    rs.close();
    System.err.println("Closing resultset");
    stmt.close();
    System.err.println("Closing statement object.");
    con.close();
    System.err.println("Closing connection to data source.");

    System.gc();

This should force Java to do a cleanup of objects and free some resulrces.

How are you rinning the code and when you say you left it running for half an hour how was it being called, and how many times was it called?

It *may* also be to do with reloading the jdbc:odbc driver every time you run the code. The reason I suggest this is that the way Notes handles Java it may be eating unneccessary resources to do this.

It would be better to have a separate class to hold your connection object as a static variable, or a separate method in your above class to initialise the driver.

The point of doing this, is to load the driver just once and not reload it every time the above code executes. The above code can then retrieve the connection from the static object it is stored in.

For example, in the above code make you connection object static:

Try this at the end of your NotesMain:

    rs.close();
    System.err.println("Closing resultset");
    stmt.close();
    System.err.println("Closing statement object.");
    con.close();
    System.err.println("Closing connection to data source.");

    System.gc();

This should force Java to do a cleanup of objects and free some resulrces.

How are you rinning the code and when you say you left it running for half an hour how was it being called, and how many times was it called?

It *may* also be to do with reloading the jdbc:odbc driver every time you run the code. The reason I suggest this is that the way Notes handles Java it may be eating unneccessary resources to do this.

It would be better to have a separate class to hold your connection object as a static variable, or a separate method in your above class to initialise the driver.

The point of doing this, is to load the driver just once and not reload it every time the above code executes. The above code can then retrieve the connection from the static object it is stored in.

For example, in the above code make you connection object static:

Public Static Connection con;
       
and then have a routine to initialise it just once:

  public static void setupConnection() {
{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            System.err.println("sun.jdbc.odbc.JdbcOdbcDriver referenced.");        
   con=DriverManager.getConnection(url,null,null);
            System.err.println("Connection to source estabilished.");
   
  }

Then only call this code once. Let me know how you get on, but you may need to provide more info about how notes calls and executes this code.

listening ...
Avatar of Faust

ASKER

There is only one instance of the class running at a time, meaning Lotus Notes executes the .class once.
What seems to be eating up resources is the while loop. I sent(System.err.println(status + " " + no " " + cost)) to the java debug console   and at the same time I ran the system monitor with all the memory monitors on.
I think for some reason the variable reassignment causes additional memory allocation(that's freaking weird, pardon my French), and it could just as easily be Notes fault since everything seems to be running through the lotus.Notes.*( I assume since the CLASSPATH instance variable "points" to Notes.jar)

Also freeing memory after the while() statement doesn't help because the system croaks way before that and in my opinion the system resources are far sufficient(128MB RAM) and and ~600MB disk space(<-after ~45Min the system croaks due to lack of swap space due to lack of disk space, again this is nuts)
can you run the same sample code outside the LotusNotes VM (use some standard JRE).

(your code is fine ... it seems that LN VM has some problems ... which is still very weird ...)
Avatar of Faust

ASKER

I actually am running it using
"java classname.class" and that is much better as far as swap file and resources
BUT
once i get the resultset object and run it in a loop it only prints out 7 records a second to the standard output
that's way too slow.
Again I am new to java so I'm sure I'm missing something but I am used to being able to process hundreds of records a second.
Suggest please.
Avatar of Faust

ASKER

Oh, also my disk is working like crazy.
Buffer size?
If someone has successfully done this(with performance better than 7 records per second :) ) and is willing to share their source I would be very gratefull.
it may be an ODBC problem ... do you have native JDBC driver ?
Avatar of Faust

ASKER

No, I use whatever was supplied by microsoft.
Where can I get a native JDBC driver for access db.
I don't think Microsoft supports. Maybe I am wrong.
You should have notessql.dll which is the ODBC link to Notes so you can check if there is a more up to date version of this than the one you are using available because I think the memory leak is in the native code and not in your Java code.

The Java grabage collector cannot do anything about memory which is not released by your JDBC-ODBC link to notes and not that I'm saying that Microsoft would make and deliberate efforts to ensure their competitors products were deliberately made to look incapable, but....I'm sure you get my point.

To be honest, this is not just a notes thing as ODBC can be very slow for example even when I was using ODBC to link from Visual Basic to Oracle I had severe performance issues with the standard data controls until we upgraded to a more powerful data access control.

(As a note, when calling native code (like ODBC) you can call a recycle() method if one is implemented to try and free up any resources used by it...)

Bearing this in mind, I would stop trying to chase the leak and try upgrading to the Lotus JDBC-Notes/Domino driver which is available here:

http://www.lotus.com/rw/dlddjdbc.nsf

I used to develop in Notes a while ago and it is a superb tool but you will get a performance hit when accessing data in an SQL type fashion from Notes as Notes does not structure data in a relational table fashion.

So the driver is going to have to map data from Notes internal document model to the relational model which will slow it down a bit.

To sum up, your Java code looks fine, so I think you should try this before going any further - I don't think the bug is in Java.
Avatar of Faust

ASKER

Thanks for your reponses,
Jod, the trouble is not the time it takes to execute a query because I can live with 2 sec per query but why does it take so long to go through the recordset object, is this normal.

PS
No one has tried to post an answer, ie no one to give the points to.
It would be hard for me to give it to any particular person since everyone has given me something to think about.
Is it too much to ask for you guys to decide.
>> I can live with 2 sec per query but why does it take so long to go through the recordset object

It should not take long to go through a recordset. I should ask, how many columns and how much data is in the recordset. Check the column count and what is in the columns to see if you have returned very large blocks of data.


>> Is it too much to ask for you guys to decide.

I may be biased....

and I was only listening ... :)

btw. can anybody search the JavaSoft Bug database for 'Lotus Notes' ?

I get
'Sorry. We couldn't find your document.'
all the time ... :(
I find any bugs relevant to this for Lotus Notes so I don't think there are any noted in particular. this was the only useful document I found:

http://forum.java.sun.com/forum?13@173.CbUmaXp3aBc^0@.ee77e14/0

I did find more info in the newgroups so I would suggest a search on Deja.com (specifying all items, not just recent items). For example, I found this there:

http://x44.deja.com/getdoc.xp?AN=375961801&CONTEXT=944564236.1132068889&hitnum=40

So there is some further info about, Faust, but I think generally you are best to proceed to using a way to access Notes other than ODBC - I think it is ODBC that is slowing you down and causing the memory leak.

Points, points, points...
I find any bugs relevant to this for Lotus Notes so I don't think there are any noted in particular. this was the only useful document I found:

http://forum.java.sun.com/forum?13@173.CbUmaXp3aBc^0@.ee77e14/0

I did find more info in the newgroups so I would suggest a search on Deja.com (specifying all items, not just recent items). For example, I found this there:

http://x44.deja.com/getdoc.xp?AN=375961801&CONTEXT=944564236.1132068889&hitnum=40

So there is some further info about, Faust, but I think generally you are best to proceed to using a way to access Notes other than ODBC - I think it is ODBC that is slowing you down and causing the memory leak.

Points, points, points...
I find any bugs relevant to this for Lotus Notes so I don't think there are any noted in particular. this was the only useful document I found:

http://forum.java.sun.com/forum?13@173.CbUmaXp3aBc^0@.ee77e14/0

I did find more info in the newgroups so I would suggest a search on Deja.com (specifying all items, not just recent items). For example, I found this there:

http://x44.deja.com/getdoc.xp?AN=375961801&CONTEXT=944564236.1132068889&hitnum=40

So there is some further info about, Faust, but I think generally you are best to proceed to using a way to access Notes other than ODBC - I think it is ODBC that is slowing you down and causing the memory leak.

Points, points, points...
Points, points, points...
Points, points, points...
Points, points, points...

:)) greedy Jod
Points, points, points...
Points, points, points...
Points, points, points...
Points, points, points...
Points, points, points...
Points, points, points...

(it's just like being back in the school playground again...great ;-)
Avatar of Faust

ASKER

Would someone post an answer
   >:o
These points are burning a hole in my pocket.
Avatar of Faust

ASKER

Oh, I see you guys are trying to jack me up for more,
Fine
Avatar of Faust

ASKER

So what if I'll have to eat spaghetti with no sauce all week, don't mind me.
:)          
I think that you can accept Jod's comment as answer (are there any radio  buttons / links ?)
ASKER CERTIFIED SOLUTION
Avatar of Jod
Jod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
cheers, faust. Any better performance with the new drivers?