🚀 go-pugleaf

Oliver wrote:
> Hi
> 
> i have in one special project a HSQL database which in principle 
> works fine, but my user complained that it gets slow once it has 
> only 100 or more entries. Heres the getValueAt method which might
> have a performance problem.

Well, I guess first, what is the question..?

Second, I can not find the code you posted in the DX or dbswing source.. 
So.. Where did you get this code..??


John..


> 
> @SuppressWarnings
> ({"ReturnOfNull", "LoopStatementThatDoesntLoop"})
> public Object getValueAt(int rowIndex, int columnIndex) {
>         Statement stat = null; 
>         try {  stat = MyDBConn.createStatement(); }
>         catch (SQLException e) {  return null;  }
>         String Order = getOrder(SetAscending);  
>         String sqlCmd = "SELECT * FROM Res"+Order;  
>         if (SetFiltered) sqlCmd = "SELECT * FROM Res WHERE "+sqlCmdFiltered+Order;
>         ResultSet rs = null;
>         try {  rs = stat.executeQuery(sqlCmd);}
>         catch (SQLException e) { return null; }
>         try {  while (rs.next())
>                {  rs.absolute(rowIndex + 1);
>                   if (columnIndex==0) return SpecialFormat(rs.getObject(1));
>                   return NumberFormat(rs.getObject(columnIndex + 1));
>                }
>         } catch (SQLException e) { return null; }
>         return "";
> }
> 
> regards
> 
> Oliver


-- 
=============================================
TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
http://www.microps.com/mps/paperFAQ.html
====================================================

Oliver wrote:
> @SuppressWarnings
> ({"ReturnOfNull", "LoopStatementThatDoesntLoop"})

Does this actually work?  I haven't been able to get JBuilder to honor 
@SuppressWarnings annotations.  I also haven't found a good list of 
accepted values anywhere.

>                {  rs.absolute(rowIndex + 1);

My guess is that the slowness is related to this.  My understanding is 
that some (most?) drivers still scroll through the entire result set 
when the absolute method is called.  So, let's say rowIndex is 100. 
Then you're reading through 100 rows of data before you get to the one 
you want.

If you can alter your query so the query only returns the desired row, 
that might help.

It also looks like you could trim down your result set quite a bit. 
You're doing a "SELECT *" but you're only using one column (columnIndex) 
in your code.  A more streamlined query might be faster.

-- 
Gillmer J. Derge [TeamB]

Hi

hey, thanks! 

Now i use a LIMIT x 1 command in order to get only one row, and later the first() method of the ResultSet. That made things really somewhat faster. 

But the main mistake i made in the prepareRenderer method which i didnt show.

Oliver

public Object getValueAt(int rowIndex, int columnIndex) {
        Statement stat = null;
        try {  stat = MyDBConn.createStatement(); }
        catch (SQLException e) {  return null;  }
        String Order = getOrder(SetAscending);
        String sqlCmd = "SELECT LIMIT "+rowIndex +" 1 "+ "* FROM Res"+Order;
        if (SetFiltered) sqlCmd = "SELECT LIMIT "+rowIndex +" 1 "+"* FROM Res WHERE "+sqlCmdFiltered+Order;
        ResultSet rs = null;
        try {  rs = stat.executeQuery(sqlCmd);}
        catch (SQLException e) { return null; }
        try {     rs.first();
                  if (columnIndex==0) return SpecialFormat(rs.getObject(1));
                  return NumberFormat(rs.getObject(columnIndex + 1));
        } catch (SQLException e) { return null; }
   }