This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL INNER JOIN improves performance?

 
Andres Delrotti
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jelle Klap
Bartender
Posts: 1951
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Andres Delrotti
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what could be the reason then? its really in the volume of the return data?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic