Hi,
Currenlty I am using lot of inner join's(around 7) in my sp, does it have any impact on sp perfomance. does left outer join gives better perfomance then inner join.
one more thing if i m joining two tables a and b which has column id and id1, both r not nullable. i suppose here i can go for inner join as these columns r indexed.
First of all these two are meant for serving different purpose. So the comparison might not be valid in all cases.
You can read more here.
Outer joins are more expensive than inner joins. What I am about to say is going to be controversial to many. If you tune the database right and if you don't do anything goofy and if you are using a professional strength RDBMS, then 7 inner joins shouldn't be a problem.
What do I mean by database tuning? There is a lot to database tuning but the most obvious thing to check is to make sure that you always join over columns that are indexed.
What do I mean by goofy? Don't use the OR operator in your join condition. Try to keep your joins over a single comparison like a foreign key in one table equaling the primary key in the other table. Try to keep all your key fields typed as integers.
If you do run into performance problems, then be sure to study the execution plan of the offending query. For example, you might run into problems when joining over really large tables, so large that even an index scan is too slow. You may have to denormalize and provide additional filtering to cut down on the scan times. Don't try to anticipate this. Denormalization is best done scarcely and only after you run into real world performance situations.
JOIN is used for a specific purpose & not for performance.
LEFT OUTER JOIN is used to include records, for which there are no matching records in the table on the right. INNER JOIN picks matching records based on some criteria, in both the tables.
To follow up on what Glenn said, if you are joining on the "goofy stuff" then extracting that out into temp tables in advance may also help.
In one database I worked on in the past the join was on a partial key (the tables had composite keys, i.e. a primary key with many columns in it) and there was additional filtering going on in the where clause. The filtering in the where clause took the set of rows to be looked at from several billion down to several thousand on one side of the join. Joining onto a table of serveral thousand rows was much easier than onto several billion. The query time went from 20 minutes down to 7 seconds as I recall.
Also note, we had subqueries and UDFs (user defined functions) in there too - which possibly added to the goofiness.
Left joins give different results than inner joins and so should not be used as a substitute. More than likely it is indexing that you need. While indexes are automatically created when you define a primary key, they are not created when you define a foreign key. So you will need to index all those foregin keys in your joins if you haven't already.
Also check your execution plan to see where the problem is.
For more specific advice about ways to performance tune your query, you need to show it to us.
The reason why joins are typically expensive is that joining may result in a number of tuples larger than the size of either table.
However, sometimes the join attributes in one table functionally determine a unique tuple in another table. in this case, join can be very cheap (but you would need to index on those attributes).
This would be a cheap operation regardless of the number of joins you did -- it's more an issue of the data and data dependencies.
Since you are joining on 2 keys, where it looks like the same key is used for both tables, this should be a cheap operation regardless of what kind of join you use.