May 5, 2004 at 6:16 pm
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.
May 6, 2004 at 5:47 am
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.
May 6, 2004 at 6:05 am
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
Change is inevitable... Change for the better is not.
May 6, 2004 at 6:13 am
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 .
May 6, 2004 at 6:49 am
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply