SQL Server Performance

Correlated subqueries Vs Inner Joins

Discussion in 'SQL Server 2005 General Developer Questions' started by jaybee, Feb 14, 2008.

  1. jaybee New Member

    Any guidance for which one you'd use in what circumstances? I (should be!) writing a lot of ad-hoc queries in the next few months.
    Another question, possbly amateurish; why are views created, when Stored Procedures can do the same thing, and can be cached?

    Thanks,
    Jaybee.
  2. susanthab New Member

    All depends on the situation. You have to analyze the query plan and then decide the best.
    Stored procedures and views are there for deferent purposes. In summary, views can combine one or more tables and create a virtual table.
    Stored procedures can be used to encapsulate your business rules and store them in SQL Server database.
    Use below links for more details;
    http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx
    http://www.sql-server-performance.com/articles/dev/views_in_sql_server_p1.aspx
  3. FrankKalis Moderator

    [quote user="susanthab"]
    All depends on the situation. You have to analyze the query plan and then decide the best.
    Stored procedures and views are there for deferent purposes. In summary, views can combine one or more tables and create a virtual table.
    Stored procedures can be used to encapsulate your business rules and store them in SQL Server database.
    Use below links for more details;
    http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx
    http://www.sql-server-performance.com/articles/dev/views_in_sql_server_p1.aspx
    [/quote]
    This is related to another question, and not this one here, right? [:)]
  4. FrankKalis Moderator

    [quote user="jaybee"]
    Any guidance for which one you'd use in what circumstances? I (should be!) writing a lot of ad-hoc queries in the next few months.
    Another question, possbly amateurish; why are views created, when Stored Procedures can do the same thing, and can be cached?
    [/quote]
    As a general rule of thumb are RDBMS optimized for JOIN operations. Any decent query optimizer will try to rewrite statement to use JOIN operations behind the scenes and you will find that in the majority of cases you are better off using the JOIN approach. But, as always, it depends on the particular situation... [:)]
  5. satya Moderator

    Jaybee
    To answer your first question, say if u have a particular query that can be written either way, try both and look at execution plan with releventset statistics io on and statistics time on to see if one or the other is more expensive in terms of reads and cpu utilization. As it said that is true but again it depends on the query. A simple sub-query is often the same process as a nested loop join so you may see the plan show the join when you wrote a sub-query. This is very common infact.
    Then coming to views and stored procedure, views are best to select only relevant columns and mask them off by hiding the original column name to the end-user. In any case both of them depends upon the statistics on the table & relevant indexes.

Share This Page