aspose file tools*
The moose likes JDBC and the fly likes SQL INNER JOIN improves performance? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL INNER JOIN improves performance?" Watch "SQL INNER JOIN improves performance?" New topic
Author

SQL INNER JOIN improves performance?

Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 117
Hi all,

SQL is really not my specialty. I am more of an architect/designer. Since we lacked personel in my team, my boss told gave me the task of trying to rewrite a long sql statement to improve performance because the time it takes to retrieve the result is getting longer and longer.

The sql basically consist of long and repetetive conditions which joins records from two or more tables.

Example:

Select id, field1, field2, field3 from TableA, TableB, TableC........Table Z where
TableA.id = TableB.id ans TableA.id = TableC.id and TableA.id = TableD.id.........TableA.id = TableZ.id

Would it improve performance if I used INNER JOIN instead? I am also planning to make it a stored procedure instead of it as a JDBC sql statement? would that make it faster too?


Thanks,
Andres



manoj r patil
Ranch Hand

Joined: Jun 06, 2002
Posts: 181
You can check whatever joins you have, if those fields are indexed. You can also use explain plan feature of the database to check the access path and tune the SQL further.


love your job and not your company;
...because you never know when your company will stop loving you!
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1667
    
    7

The example query you provided already uses inner join operations, albeit without the explicit join notation.
Replacing the implicit notation won't improve the performance of the query. At all.
Though it would improve readability, in my opinion.


Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 117
So what could be the reason then? its really in the volume of the return data?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL INNER JOIN improves performance?
 
Similar Threads
PreparedStatement v/s Statement
Performance Tuning
design question
EJB-QL question
JDBC ResultSet Size