Home » SQL & PL/SQL » SQL & PL/SQL » Do joins really improve query performance?
icon6.gif  Do joins really improve query performance? [message #218699] Fri, 09 February 2007 06:36 Go to next message
Messages: 2
Registered: February 2007
Location: INDIA
Junior Member
i am new to databases.
during our training we were told joins improve query performance.

But recently what i heard is that joins affect query performance.
it's b'coz first it does a cross product and then implements the join....

So i am a bit confused.

please help me in clearing this doubt.......
Re: Do joins really improve query performance? [message #218704 is a reply to message #218699] Fri, 09 February 2007 07:05 Go to previous messageGo to next message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although there are queries which do the job using only one table, frequently you'll have to join a few tables in order to accomplish the job. Joins are ... inevitable. They should be done properly - join columns as needed, possibly create indexes, gather statistics etc. in order for this query to work as it should.

Saying that joins are or are not good is out of question, and doesn't make much sense to me.
Re: Do joins really improve query performance? [message #218711 is a reply to message #218699] Fri, 09 February 2007 08:34 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This might be stretching it a bit, but one possible explanation for what the OP heard is what I've seen some application developers doing (this particular case was ASP developers).

They would use a series of nested for loops to "simulate" a join at the application layer itself, with each for loop doing a select * from a different table, and then inside the loop doing some processing where the values were equal.

In this case, simply writing the query as a single join, even if you were to then loop through that a row at a time (not good, I know), would be better than the 2, 3, or more nested for loop single table query approach.
Previous Topic: Granting privileges on trigger
Next Topic: unique constraint violated
Goto Forum:

Current Time: Mon Aug 21 22:43:16 CDT 2017

Total time taken to generate the page: 0.09995 seconds