Re: WHERE clause applies to right-hand table of LEFT JOIN

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 06:08:15 -0600

> Yes, this is often true. However, in my case T2 is contrained by a foreign
> key relationship to T1. This means that every record in T1 must have one
or
> more records in T2. This being the case, the exact same result set is
> returned regardless of whether T1 or T2 is on the LEFT of this OUTER join.
> (And an INNER JOIN would also yield the same result set.)

But you may get different results with different data as demonstrated by the
example below. Like Tibor mentioned, it's always best to specify INNER JOIN
unless you have a reason to do otherwise.

CREATE TABLE T1
 (
 T1_PK int NOT NULL
  CONSTRAINT PK_T1 PRIMARY KEY,
 T1A int NOT NULL,
 T1B int
 )

CREATE TABLE T2
 (
 T2_PK int NOT NULL
  CONSTRAINT PK_T2 PRIMARY KEY,
 T2A int NULL
  CONSTRAINT FK_T2_T1 FOREIGN KEY
  (T2A) REFERENCES T1(T1_PK),
 T2B int
 )

CREATE INDEX T2_T2A ON T2(T2A)
CREATE INDEX T2_T2B ON T2(T2B)

INSERT INTO T1 VALUES(1,1,1)
INSERT INTO T1 VALUES(2,2,2)
INSERT INTO T2 VALUES(1,1,1)

SELECT T1.T1A,
               T1.T1B,
               T2.T2A,
               T2.T2B
FROM T1
LEFT JOIN T2 ON T2.T2A = T1.T1A

SELECT T1.T1A,
               T1.T1B,
               T2.T2A,
               T2.T2B
FROM T1
INNER JOIN T2 ON T2.T2A = T1.T1A

> Since we're currently migrating to SQL Server, I suppose I should use the
> query analyzer to see how this would behave against SQL Server. (Since I
> made the change which I mentioned for Interbase, the issue is no longer
> pressing.) If I get a chance to analyze this further I'll report back to
the
> group.

The SQL Server cost-based optimizer is very good at generating efficient
execution plans in most cases. I believe you'll be pleased with the
performance, especially if you formulate your queries properly (e.g. use
INNER JOIN) and create appropriate indexes. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@nospam.com> wrote in message
news:%23GwmpNWEEHA.3344@tk2msftngp13.phx.gbl...
> Hi Dan,
>
> Thanks for your reply. A couple comments inline.
>
> > > So I switched T1 and T2 around and placed T2 on the left. This
> immediately
> > > improved performance in the problem area, but had the effect of
slowing
> > down
> > > the other areas of the application which were previously performing
> > > beautifully.
> >
> > The order in which tables are specified in an OUTER JOIN is significant
so
> > you are asking for potentially different results.
>
> Yes, this is often true. However, in my case T2 is contrained by a foreign
> key relationship to T1. This means that every record in T1 must have one
or
> more records in T2. This being the case, the exact same result set is
> returned regardless of whether T1 or T2 is on the LEFT of this OUTER join.
> (And an INNER JOIN would also yield the same result set.)
>
> > The order does not matter
> > with an INNER JOIN but that is also a different query.  It doesn't
> surprise
> > me that you different performance with these fundamentally different
> > queries.
>
> > Generally speaking, one addresses performance issues by creating useful
> > indexes.  In SQL, you are describing the desired result.  It's up the
> > optimizer to figure out the best way to obtain it based on available
> > indexes, statistics and search algorithms.
>
> This is what surprised me. All columns used in the WHERE clause of any of
my
> SELECT statements are indexed. It really threw me for a loop when some of
my
> SELECTS started really bogging down, until I found the common denominator
> that all poorly performing queries were filtering on columns from the
> right-hand table of the LEFT OUTER JOIN. I was wondering if this is
typical.
> It's really abysmal on the part of the Interbase execution planner. If it
> would, in this case, select from the table on the right first, according
to
> the WHERE criteria, it would immediately narrow down the qualifying
records
> to about half a dozen, from a total set of thousands.
>
> Since we're currently migrating to SQL Server, I suppose I should use the
> query analyzer to see how this would behave against SQL Server. (Since I
> made the change which I mentioned for Interbase, the issue is no longer
> pressing.) If I get a chance to analyze this further I'll report back to
the
> group.
>
> Thanks,
>
> - Joe Geretz -
>
> > "Joseph Geretz" <jgeretz@nospam.com> wrote in message
> > news:uICs1HTEEHA.1456@TK2MSFTNGP09.phx.gbl...
> > > I came across a performance problem recently in my application. I'm
not
> > > using Access, the DB is Interbase, but I tracked down the problem to a
> > > specific query. This may not be a vendor specific issue at all and if
> not,
> > > perhaps you can help with this.
> > >
> > > I have a Table, let's call it T1 which has a one to many relationship
> with
> > a
> > > secondary table, let's call it T2. Within the context of my
application,
> > the
> > > join between these two is so common that I've created a VIEW for it:
> > >
> > > SELECT T1.T1A,
> > >                T1.T1B,
> > >                T2.T2A,
> > >                T2.T2B
> > > FROM T1 LEFT JOIN T2 ...
> > >
> > > Now, at various points in my application I'm selecting * from this
VIEW.
> > > Sometimes I'm selecting WHERE T1B = 'blah'. This yields excellent
> > > performance.
> > >
> > > Other times though, I'm selecting WHERE T2B = 'yada'. In this case,
> > > performance is abysmal. Now this is interesting. The field for my
filter
> > > criteria is from the right hand table of a LEFT JOIN. Now LEFT JOIN
> > > specifies 'All the records from the table on the left and any matching
> > > records from table on the right'. This being the case, it seems that
if
> > the
> > > WHERE clause relates to the table on the right, ALL records from the
> table
> > > T1 are gathered. Then ALL records from the table on the right are
> > gathered.
> > > Then from the resulting recordset, rows which don't fit the WHERE
> criteria
> > > are eliminated. This explains the abysmal performance.
> > >
> > > (But I'd have thought the plan optimizer would see the WHERE criteria
> > > pertains to the table on the right, and use that table as the
launching
> > > point for execution.)
> > >
> > > So I switched T1 and T2 around and placed T2 on the left. This
> immediately
> > > improved performance in the problem area, but had the effect of
slowing
> > down
> > > the other areas of the application which were previously performing
> > > beautifully.
> > >
> > > Ultimately, the only way I could see to address this is by creating
two
> > > VIEWS, one with T1 on the left, and one with T2 on the left, and
taking
> > care
> > > to select from the appropriate VIEW depending on the WHERE filtering
> > > criteria. Is there a better way of doing this with only one VIEW?
> > >
> > > (BTW, I did try an INNER JOIN, but this resulted in mediocre
performance
> > in
> > > all areas.)
> > >
> > > Have you ever encountered this type of scenario? If so, how did you
> > address
> > > this?
> > >
> > > Thanks for any advice which you can provide.
> > >
> > > - Joe Geretz -
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: adp report does not recognize fields from stored procedure
    ... CREATE PROCEDURE dbo.rp_rptLckrAssgn(@plngAcadYrID int) ... declare @lngClssOfHold int,@lngClssOfGrad int, @lngClssOfID1st int, ... select DgObj.lngDgObjID, @strMsgHumGen as strMsg, ... from tblDgObjStts DgObj inner join ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored Procedure CASE
    ... Columnist, SQL Server Professional ... Drew Laing ... > SQL Server MVP ... > C.CostCenterID INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Self Joins and optimization
    ... EMPLOYEE# INT NOT NULL, ... CREATE TABLE TXREF ... INNER JOIN TX AS B ... While this passes the test data, it still needs a little work. ...
    (comp.databases.theory)
  • Re: "EXEC" in SQL Server 2000 Views
    ... Kalen Delaney ... > INNER JOIN b ... > Could not execute query against OLE DB provider 'MSDASQL'. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)