question SQL joins and performance

[thattayas]

I have a SQL query which joins data from 4 tables and returns about 680 rows. There are 3 join conditions.

I was wondering if running a query with multiple joins such as the above will affect the performance of the database server more in comparison to retriving the same result set from a single table.

I am new to SQL performance and any help would be greatly appreciated.

Thanks
Rukmal

 

[Golom]

It may ... although performance issues are much more at the physical implementation level and those are specific to each DBMS implementation.

This is the ANSI SQL forum and ANSI SQL takes a logical view of manipulating data without regard to physical implementation issues.

I suggest that you post your question in the forum specific to your DBMS.

 

[thattayas]

thanks

 

[PHV]

Hopefully the joined columns are indexed ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLSister]

In general, you cannot retreive the same result set from one table that you can with the joins. Otherwise there would be no reason to do the joins.

Yes there can be a performance hit from using joins; however if you have indexed the fields you are joining on and the fields in the where clause then performance issues are minimized. Incorrect joins, unneeded joins or poorly created joins that force the user to use the distinct keyword to avoid duplicated results can cause bigger performance issues.

Using select * when performing a join is always a bad choice as there are at least two fields containing the same data (the join field in each table), but as a rule you should never select more fields that you intend to use anyway. This is a performance issue because returning uneeded data uses up network assets unnecessarily. Why send 200mg across a network in response to a query when you are really only interested in 20mg of the data? This is a sneaky one as the network is often not all that busy at the time the query was built and so no performance hit was noticed. But 10,000 queries later (all using that select *) and 50000 users later (1000 using accessing the database at any one time), those mg add up to a slow response time. Better to avoid anything you can.



"NOTHING is more important in a database than integrity." ESquared