A question on JOIN

Hi, guys,

I am confused by JOIN s. It said JOIN would hurt queryperformance. So is there some limit of number of JOIN s in a query, say if yourquery has more than 3 joins, the performance would be hurt? And why?

Is there an alternate method of JOIN? I tried sub query, itseems the execution plans are the same.

another option to join would be to use UNION, but I think the performance would be the same or possibly worse using that.

Your joins will be affected less if you can specify how it joins on more than one parameter (If I recall correctly)..

SELECT * FROM maintable mt

INNER JOIN table t ON t.ID= mt.ID AND t.another_parameter=mt.another_parameter AND t.secondparam=mt.second_param AND t.thisfield='whatever'

WHERE mt.thisfield='whatever'

The more you filter it down, the less the query will have to scan the dataobject for results.

|||

Well the maximum number of tables per select statement is 256 for SQL Server. NORMALLY joins will have better performance than sub-queries as joins use relational algebra. Some people will say they only use joins if they need a field from the table they are joining into, but i normally use them over a sub-query(there are situations where a subquery is fatser or needed).

Also it matters what you are joining on, if you join on a text field or a non indexed field it will hurt query performance.

|||

The speed of a JOIN is more influenced by the number of rows in both sources of the join, and the number of rows in the result. Using the correct indexes can speed up the JOIN. I've seen multi-minute queries sped up by a single index to sub-second responses by the correct use of indexes, and I'm sure there is even better results (and worse).

|||

As Motley points out, what matters are proper indexing and the actual number of rows being returned by tables in each join condition. I have had 8 table joins with sub-second response time where all the tables are being selected based on a primary key value.

There is no alternative to a join in the sense that if you have normalized data, you're going to have to join the tables to get the data. There is NO reason to avoid joins, even ones involving many tables.

|||

Got it.

Guys, Thanks.

|||

how bout creating the joins in a view as opposed to creating it in a stored procedure? will this improve performance?

|||

Not unless it's an indexed/materialized view, no.