Where is it best to place "where conditions" in the join or inthe final where statement

Question

Where is it best to place "where conditions" in the join or inthe final where statement

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

Looking at this query I'm not sure why you don't just use a self join. Maybe I don't know the data very well, but it seems that doing a Not Exists with a Not like could be re-written as an inner join.

select rb.*

from rebate rb

join rebate rb2 ON rb.group = rb2.group

and rb.unit = rb2.unit

and rb.enterprisecode = rb2.enterprisecode

and rb.programcode = rb2.programcode

and rb2.incentiverevcode = 0

and rb2.sys_nam like '%odsbeta%'

and rb.unit > ' '

and rb.file_dte = '2004-05-02'

and rb.sys_nam like '%odsbeta%'

and rb.incentiverevcode = 1

I agree wholeheartedly with what Kenneth said above. You really don't want to use the like with a leading wildcard unless absolutly nessecary as it always causes a table scan.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

bjure Ten Centuries Points: 1086 More actions · Answer 2

When I tested it on two of my biggest table (one i 665 000 rows and the other 489 000), "not exists" was faster.

Regarding "not in"; I have had huge problems with it in the past, I find that I always get unreliable results, so "not exists" is the way to go imo.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

BJure,

When you say you tested "IT" and NOT EXISTS was faster, what is "IT"? In other words, what was "NOT EXISTS" faster than? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bjure Ten Centuries Points: 1086 More actions · Answer 4

Oh, sorry.

My previous post was a reply to your long post (post nr.9) regarding outer joins. I have never thougt of trying that, so I did some testing. But my conclusion was that NOT EXISTS did the job better.

But I never realized that there was a second page, so I just repeated what everyone else had already said .

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

As already been said, there is no definite 'way to go' - it always depends. It may not be 50-50, but sometimes not exists is better, other times a not in will do the stuff faster.

The major players are your data and what you want to get from it in conjunction with the indices present.

/Kenneth