BorlandTalk.com Forum Index BorlandTalk.com
Borland discussion newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

speeding up HSQL database

 
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> JBuilder Databases
View previous topic :: View next topic  
Author Message
Oliver
Guest
PostPosted: Mon Nov 13, 2006 8:36 pm    Post subject: speeding up HSQL database Reply with quote


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.

@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
Back to top
John Moore (TeamB)
Guest
PostPosted: Tue Nov 14, 2006 1:26 am    Post subject: Re: speeding up HSQL database Reply with quote


Oliver wrote:
Quote:
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..


Quote:

@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
====================================================
Back to top
Gillmer J. Derge [TeamB]
Guest
PostPosted: Tue Nov 14, 2006 1:40 am    Post subject: Re: speeding up HSQL database Reply with quote


Oliver wrote:
Quote:
@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.

Quote:
{ 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]
Back to top
Oliver
Guest
PostPosted: Thu Nov 16, 2006 6:38 pm    Post subject: Re: speeding up HSQL database Reply with quote
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; }
}
Back to top
Display posts from previous:   
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> JBuilder Databases All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.