Java Answers Forum - Swing Client Database Connection

Articles |
News |
Weblogs |
Books |
Forums

Artima Forums | Articles | Weblogs | Java Answers | News

Sponsored Link •

Java Answers Forum
Swing Client Database Connection

7 replies on 1 page. Most recent reply: Dec 6, 2005 1:36 AM by Kondwani Mkandawire

Welcome Guest
Sign In

Back to Topic List

Reply to this Topic

Search Forum

Threaded View


Previous Topic		Next Topic

Flat View: This topic has 7 replies on 1 page

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Swing Client Database Connection

Posted: Dec 5, 2005 11:57 PM

Matthias or anyone with a fair bit Swing experience.
I'm trying to read from an extremely large database.
Some of the modules in my application use Hibernate
and I hardcoded some Readers to use straight JDBC
(jdbc-postgres driver). The readers especially the
ones with Joins statements are taking extremely long
to retrieve data (to return the result set), is hibernate
any quicker (I din't code the hibernate modules), should
I switch my readers to hibernate - i.e. would it be
worth learning it in great detail to actually account
for the joins etc? i.e. is it going to save me some
execution time?

Matthias Neumair

Posts: 660
Nickname: neumi
Registered: Sep, 2003

Re: Swing Client Database Connection

Posted: Dec 6, 2005 12:21 AM

I'm sorry I can't help you with that, I allways use standard JDBC.

But try to post one of your statements here.

I'm sure it can be optimized.

Usually sql statements only take a long time if the filters are used after the joins.

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Swing Client Database Connection

Posted: Dec 6, 2005 12:43 AM

Hi Matthias, thanks a mill for the response. I have an SQL Statement which I expect to execute via a PreparedStatement:

public static String SQL_STATEMENT = 
     "   SELECT column1, column2 from table1 join table2 using (column2) join table3 using (column3) where column4 like ?  ";
 
public PreparedStatement getPreparedStatement() throws SQLException {
    ps = conn.prepareStatement(SQL_STATEMENT);
    return ps;
}
 
In a different class I set ?  to some value:
 
PreparedStatement ps = getPreparedStatement().setString(1, s+"%");
ResultSet rs = ps.executeQuery();

The ResultSet takes for ever to get retrieved.

Thanks again.

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Swing Client Database Connection

Posted: Dec 6, 2005 1:07 AM

Would using a regular expression cut down on the time?

Matthias Neumair

Posts: 660
Nickname: neumi
Registered: Sep, 2003

Re: Swing Client Database Connection

Posted: Dec 6, 2005 1:16 AM

That much is clear.

You join all recordsets from every table before applying the "where" part. Some database servers optimize a statement like this in the compiler , but most database servers don't.

I assume you have the following structure.

table1.column1
table1.column2

table2.column2
table2.column3

table 3.column3
table3.column4

I don't know exactly the SQL dialect which you are using, but since they all are vers close, you shouldn't have a problem to apply my code to your db.

1. Since you join 3 tables you should start with a sub-query in which we only get the filter table3

select distinct column2 from table2 join (select distinct column3 from table3 where column4 like ? ) using (column3)

could be that you must give a name to the sub-query, that would be something like

select distinct column2 from table2 join (select distinct column3 from table3 where column4 like ? ) as temp_table using (column3)

2. If you don't neet anyting else from table 2 you can use the "in" syntax, you don't even have to join.

select column1, column2 from table1 where column2 in (select distinct column2 from table2 join (select distinct column3 from table3 where column4 like ? ) using (column3))

If not, use the standard inner join

select column1, column2 from table1 join (select distinct column2 from table2 join (select distinct column3 from table3 where column4 like ? ) using (column3)) using (column2)

Finally:
The prepared statement is a nice thing to use, but in this case rather useless, since compiling the query does not take nearly as long as executing. It's up to you if you want to read it or not.

Which DB Server are you using, by the way?

Matthias Neumair

Posts: 660
Nickname: neumi
Registered: Sep, 2003

Re: Swing Client Database Connection

Posted: Dec 6, 2005 1:20 AM

Correction: you "use" the prepared statement, you don't "read" it.

A prepared statement normally makes only sense if you do the same thing over and over again. For single queries it does not do any good.

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Swing Client Database Connection

Posted: Dec 6, 2005 1:32 AM

Thanks a mill, I'll give it a shot. Its a sku lookup
so it will be used multiple times. Thanks again.

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Swing Client Database Connection

Posted: Dec 6, 2005 1:36 AM

Using Postgresql 8 on Mandrake.

Flat View: This topic has 7 replies on 1 page

Previous Topic

Next Topic