Re: Select Statement: Join vs Inner Select

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 10:16:09 -0600

Srinivas,

>> The JOIN will definitely perform much better. In (2), the SELECT
statement will be executed for each row, which is huge overhead. <<

In general, there is no guarantee that the query with JOIN will perform
better. Also, it is not correct that correlated subqueries are executed for
each row. One major component of query optimization involves query analysis
& if SQL Server cannot find an efficient plan in the first stage (trivial
optimization), it may perform further simplifications like syntactical
transformations, rearrangement of operations etc. This could result in
better plans for subqueries over joins (and vice versa).

Here is a quick example where subquery beats a join :
--#1
SELECT a1.au_lname, a1.au_fname, SUM( a2.royaltyper)
  FROM Authors a1
  LEFT OUTER JOIN TitleAuthor a2
    ON a1.au_id = a2.au_id
 WHERE a1.State = 'CA'
 GROUP BY a1.au_lname, a1.au_fname ;

--#2
SELECT a1.au_lname, a1.au_fname,
       (SELECT SUM(a2.royaltyper)
          FROM TitleAuthor a2
         WHERE a1.au_id = a2.au_id )
  FROM Authors a1
 WHERE a1.State = 'CA';

One cannot assure that the execution plans will be simpler and optimal when
using a join relative to a subquery. The query optimizer estimates a cost
for each combination of join/subquery strategy, join order, and indexes and
that is why in some cases, changing the order of table references in a join,
especially outer joins, changes the plan & cost

Since efficiency depends on physical models, the only way Jason can find out
if one construct performs better than the other is by testing both of them
against his tables and comparing the results.

>> Also, if you are executing this query from your VB app each time, SQL
Server will have to prepare an execution plan for each call. <<

While this may happen, in certain cases auto-parameterization can occur with
ad hoc statements where the statements are parameterized and plan is cached.
Turn on a Profiler trace with "SP:CacheHit" event, send an EXEC or an ad-hoc
SQL statements from client and you may see the cache usage. In such cases
there is no need for plan preparation & the efficiency may be comparable to
a stored procedure. However in general, I do agree that stored procedures
are definitely a better option than ad hoc queries. 

-- 
Anith

Relevant Pages

  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)
  • Re: Why I/Oread is a low Num on the exec. Plan but very high on tr
    ... If by "trace of the query analyzer" you mean the *estimated* ... execution plan, it sounds quite possible you are seeing what I ... >>undermine the query optimizer's cost estimation. ...
    (microsoft.public.sqlserver.server)
  • Re: Select Statement: Join vs Inner Select
    ... Anith, yes, there is no guarantee about consistent performance with JOINS vs ... simple query using EXEC, ... > & if SQL Server cannot find an efficient plan in the first stage (trivial ... > Server will have to prepare an execution plan for each call. ...
    (microsoft.public.sqlserver.programming)
  • Re: Finally which ORM tool?
    ... Also, if you pass a variable to the query, the value the ... you have a linq query and by changing the variable's value, ... q is affected if I change foo AFTER this query and BEFORE execution. ... And it is a declaration, but one which captures the variables. ...
    (microsoft.public.dotnet.languages.csharp)