Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using EXISTS
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 01/21/2005 :  01:34:55  Show Profile  Reply with Quote
The problem with byrmol's last post
(Even though, I like him the most)
Was his fatal assumption
That other yaks' brain consumption
Is a match for the one that he boasts!

in other words - I have no idea what he just said - but I think he said this...

In an updateable view definition
(to avoid subsequent contrition)
use the keyword 'exists'
if the 'check option', by twist
can't be enforced by a joining condition.

but maybe he could clarify if I understood.

PS yaks' is used advisedly over yak's - but I'm up for a discusion if anyone wants to take me on about it...
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 01/21/2005 01:37:09
Go to Top of Page

andyw97236
Starting Member

USA
1 Posts

Posted - 03/25/2010 :  10:23:30  Show Profile  Reply with Quote
Hey guys we've run into several performance issues with if exists in our application.

Here's a snippet of code we had to replace within a stored procedure. I've only changed the names of the columns and views.

This if exists took CPU 4875 READS 113934 Duration 4875 shown via profiler and unfortunately can be called thousands of times because surrounding cursor code. The join is on the primary key of the tmpP table and the join columns in tmpD are contained in it's primary key but it has other columns in it's primary key as well.
@O is a tiny int @C is a varchar(8)

if exists (select top 1 1 from tmpP with (nolock)
join tmpD with (nolock) on tmpP.O=tmpD.O and tmpP.B=tmpD.B and
tmpP.G = tmpD.G and tmpP.P = tmpD.P
where tmpP.O=@O and tmpP.C = @C and
tmpD.T not in ('A','B','C','D'))
--do something


We rewrote it to be

declare @test int
set @test=0
select top 1 @test = 1 from tmpP with (nolock)
join tmpD with (nolock)
on tmpP.O=tmpD.O and tmpP.B=tmpD.B and
tmpP.G = tmpD.G and tmpP.P = tmpD.P
where tmpP.O=@O and tmpP.C = @C and
tmpD.T not in ('A','B','C','D')
if @test=1 --do something

profiler than showed CPU 0 READS 7 DURATION 0

We've been scratching our heads wondering if there's a bug within if exists or some undocumented performance issue when combining if exists with not in or nolock. We know the if exists is choosing a bad query plan for this query but we don't know why. I added with recompile option to the stored procedure prior to modifying the if exists statement to no effect.
Any ideas?

tmpD in this example has about 1 million records and 80 columns tmpP has a couple thousand with 15 columns.
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/05/2010 :  10:32:59  Show Profile  Reply with Quote
Hi Guys, i can't seem to get the results i want here using the EXISTS clause, any ideas...?

I know that there are 5 records here that should be returned, basically i need to compare the 2 tables and then only show the full details from the one table on the difference between these 2 tables.

If i use a right join with these same conditions then i get the rows back, but obviously only the field on which i have created the joins, what i need is to be able to get all columns back but based on this retrieved 'compare' datam, any ideas as i could do this Simples in Oracle, but unfortunately im not on Oracle atm and dont know how i'd do this in SQL Server?

Thanks in advance, i'd really appreciate any and all help you could offer.

The below is what i currently have

SELECT
*
FROM
dbo.Rate a
WHERE
EXISTS
(
SELECT

ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
(
SELECT
ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
dbo.Rate
EXCEPT
SELECT
ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
dbo.STG_Rate
) c
WHERE
a.ChargeTypeId = c.ChargeTypeId
AND a.BusinessUnitId = c.BusinessUnitId
AND a.BrochureId = c.BrochureId
AND a.CustomerId = c.CustomerId
AND a.ProductId = c.ProductId
AND a.ServiceId = c.ServiceId
AND a.ZoneId = c.ZoneId
AND a.BasisId = c.BasisId
AND a.UnitId = c.UnitId
AND a.ConditionId = c.ConditionId
AND a.ConditionValue = c.ConditionValue
AND a.RateValue = c.RateValue
AND a.RateValue2 = c.RateValue2
AND a.ChargeMinAmount = c.ChargeMinAmount
AND a.ChargeMaxAmount = c.ChargeMaxAmount
AND a.RateBandId = c.RateBandId
AND a.RateBandType = c.RateBandType
AND a.IsLive = c.IsLive
AND a.QuoteId = c.QuoteId
AND a.Status = c.Status
)

Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 08/05/2010 :  11:15:35  Show Profile  Reply with Quote
Question re-asked here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148334
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000