Re: WHERE clause applies to right-hand table of LEFT JOIN
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/24/04
- Next message: Hari: "Re: SQL Job Agent audit trail"
- Previous message: Tibor Karaszi: "Re: Restore question"
- In reply to: Joseph Geretz: "Re: WHERE clause applies to right-hand table of LEFT JOIN"
- Next in thread: Dan Guzman: "Re: WHERE clause applies to right-hand table of LEFT JOIN"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Mar 2004 10:41:58 +0100
> 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.)
Which of course begs the question why you do an outer join in the first
place? All you do is limit the possibilities that the optimizer has to
optimize the query. And make the person after you who is reading the query
really confused.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp "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 - > > > > > > > > > > > >
- Next message: Hari: "Re: SQL Job Agent audit trail"
- Previous message: Tibor Karaszi: "Re: Restore question"
- In reply to: Joseph Geretz: "Re: WHERE clause applies to right-hand table of LEFT JOIN"
- Next in thread: Dan Guzman: "Re: WHERE clause applies to right-hand table of LEFT JOIN"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
