Hi All,
I want to know many things about Joins and subqueries...I am working
as a DBA for 3-4 years now and in my current company, there are
several projects where I have observed that many Stored Procedures
used in the respective Databases actually uses several no of joins on
several tables and in some Stored Procedures there are Subqueries that
are highly used .....that hits the performance...

I have been researching for last couple of months on this as this is
really a thing that I want to know in very much depth to
Developers to make their code and execution performant ......and have
read various articles on Joins including the Merge, Hash, Nested Loop
joins which Optimizer actually uses and some articles on Subqueries as
well...I found Craig Freedman's articles very knowledgeable.....

Anyways, I am posting here because you guys are very practical and I
know the in-depth knowledge you guys have on each and every thing
thats related with SQL Server...

I expect the details of:
1)What Joins the Optimizer should use (internally) and in what
conditions.....Can you please provide with examples...?
2)differences betweeen Joins and Subqueries...when to use Joins and
when Subqueries?
3)What are various ways to improve the Performance of Joins and
Subqueries..(I have read some on the SQL-Server-
Performance.com)...can you please provide some examples?

4)When there are various Joins used in my particular Stored proc..are
there any ways to improve the Performance at some extent without going
into much details of the code? like using indexes on the Tables used
in Joins etc...?a

And anything that you think would be useful for Joins and
Subqueries...

Thanks you very much in advance....
SVicky

Hi
http://blogs.msdn.com/craigfr/Default.aspx



"SVicky" <sqlvisit@gmail.com> wrote in message
news:5d9d581f-8b2d-40bb-8d27-9e6763577dca@25g2000prz.googlegroups.com...
> Hi All,
> I want to know many things about Joins and subqueries...I am working
> as a DBA for 3-4 years now and in my current company, there are
> several projects where I have observed that many Stored Procedures
> used in the respective Databases actually uses several no of joins on
> several tables and in some Stored Procedures there are Subqueries that
> are highly used .....that hits the performance...
>
> I have been researching for last couple of months on this as this is
> really a thing that I want to know in very much depth to
> Developers to make their code and execution performant ......and have
> read various articles on Joins including the Merge, Hash, Nested Loop
> joins which Optimizer actually uses and some articles on Subqueries as
> well...I found Craig Freedman's articles very knowledgeable.....
>
> Anyways, I am posting here because you guys are very practical and I
> know the in-depth knowledge you guys have on each and every thing
> thats related with SQL Server...
>
> I expect the details of:
> 1)What Joins the Optimizer should use (internally) and in what
> conditions.....Can you please provide with examples...?
> 2)differences betweeen Joins and Subqueries...when to use Joins and
> when Subqueries?
> 3)What are various ways to improve the Performance of Joins and
> Subqueries..(I have read some on the SQL-Server-
> Performance.com)...can you please provide some examples?
>
> 4)When there are various Joins used in my particular Stored proc..are
> there any ways to improve the Performance at some extent without going
> into much details of the code? like using indexes on the Tables used
> in Joins etc...?a
>
> And anything that you think would be useful for Joins and
> Subqueries...
>
> Thanks you very much in advance....
> SVicky