Problem using IF EXISTS(SELECT * FROM MYVIEW)

Question

Problem using IF EXISTS(SELECT * FROM MYVIEW)

gcresse

SSCrazy

Points: 2286
More actions
June 10, 2011 at 6:07 pm

#239048

I have a view that queries several tables, including a large transaction table, and returns rows if an inventory balance is negative.
If I run this: SELECT * FROM MYVIEW I get 6 rows returned in 12 seconds.
If I run this: If EXISTS (SELECT * FROM MYVIEW)
print 'exists' the statement just hangs and never completes.
I don't understand what the difference is. Has anyone experienced this? Any help is greatly appreciated.
Thanks,
Gina

Viewing 15 posts - 1 through 15 (of 20 total)

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

gcresse SSCrazy Points: 2286 More actions · Answer 1

I was able to resolve my problem by replacing the IF EXISTS with an IN statement. Apparently the exection plan is generally more efficient for IN than IF EXISTS, according to what I read here: http://www.pafumi.net/Tips_for_Effective_Queries.html

Revenant SSC-Forever Points: 42467 More actions · Answer 2

You might also try

IF 0 < ( SELECT COUNT(MyColumn) FROM MyView ) ...

In most cases this is faster than IN.

gcresse SSCrazy Points: 2286 More actions · Answer 3

Thanks for the suggestion. I tried checking the count but still got no results. The code that I'm now using:

IF 0 NOT IN (SELECT ISNULL(LotIngrLotKey,0) FROM va_negingrs)

PRINT 'exists'

returns in just a few seconds.

Revenant SSC-Forever Points: 42467 More actions · Answer 4

That does not sound right. COUNT has to return something, and usually does it pretty fast.

gcresse SSCrazy Points: 2286 More actions · Answer 5

I would agree with you, except that I ran this:

IF (SELECT COUNT(myColumn) FROM myView) > 0

PRINT 'EXISTS'

and I waited several minutes before I gave up. I can't explain it.

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Neither can I. Sorry, I am afraid that to be of able to tell you more, I would have to see the database and possibly the server.

Good luck.

ricer Right there with Babe Points: 722 More actions · Answer 7

That query would run the fastest if there was a nonclustered index on MyColumn.

Otherwise it would have to scan through the entire clustered index to get the count and it can take a while if it's running against a large table.

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Revenant

SSC-Forever

Points: 42467

June 13, 2011 at 6:03 pm

#1338124

Well, that I am taking for granted.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

gcresse (6/13/2011)
I was able to resolve my problem by replacing the IF EXISTS with an IN statement. Apparently the exection plan is generally more efficient for IN than IF EXISTS, according to what I read here: http://www.pafumi.net/Tips_for_Effective_Queries.html

Do you mind posting the actual execution plans for the different methods you tried?

Obfuscating any sensitive names as needed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

taybre SSC Eights! Points: 916 More actions · Answer 10

Did some testing (created table with 270 rows + created view to return a few results)

I ran the following code

if (select COUNT(column) from view) > 0

begin

print 'exists'

end

It took less than a second to run.

How often are the tables that your view uses updated? This could lead to the results from your view changing frequently.

HTH

taybre 😛

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

gcresse SSCrazy Points: 2286 More actions · Answer 11

if (select count(LotIngrLotKey) from va_negingrs) > 0

print 'exists'

Returns this plan:

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE:([Expr1032]=CASE WHEN [Expr1033] THEN (1) ELSE (0) END))

|--Nested Loops(Left Semi Join, DEFINE:([Expr1033] = [PROBE VALUE]))

|--Constant Scan

|--Parallelism(Gather Streams)

|--Filter(WHERE:(round(([Expr1019]*(1000.))/[Expr1024],(5))<(0.000000)))

|--Nested Loops(Left Outer Join, OUTER REFERENCES:([H1].[wkhLotKey], [I1].[wkiPodKey], [I1].[wkiIntKey]))

|--Hash Match(Aggregate, HASH:([H1].[wkhLotKey], [I1].[wkiCompItemID], [I1].[wkiUOM], [I1].[wkiTransDate], [I1].[wkiPodKey], [I1].[wkiIntKey], [dv_wrp].[dbo].[ProdOrd].[PohPlantID], [dv_wrp].[dbo].[ProdOrd].[PohProdOrder]), RESID

| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([H1].[wkhLotKey], [I1].[wkiCompItemID], [I1].[wkiUOM], [I1].[wkiTransDate], [I1].[wkiPodKey], [I1].[wkiIntKey], [dv_wrp].[dbo].[ProdOrd].[PohPlantID],

| |--Hash Match(Inner Join, HASH:([dv_wrp].[dbo].[ProdOrdDtl].[PodKey])=([I1].[wkiPodKey]))

| |--Bitmap(HASH:([dv_wrp].[dbo].[ProdOrdDtl].[PodKey]), DEFINE:([Opt_Bitmap1044]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrdDtl].[PodKey]))

| | |--Hash Match(Inner Join, HASH:([dv_wrp].[dbo].[ProdOrd].[PohKey])=([dv_wrp].[dbo].[ProdOrdDtl].[PodPohKey]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrd].[PohKey]))

| | | |--Index Scan(OBJECT:([dv_wrp].[dbo].[ProdOrd].[idx_ProdOrd]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrdDtl].[PodPohKey]))

| | |--Index Scan(OBJECT:([dv_wrp].[dbo].[ProdOrdDtl].[idx_ProdOrdDtl]))

| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiPodKey]))

| |--Hash Match(Inner Join, HASH:([H1].[wkhHdrID])=([I1].[wkiHdrID]))

| |--Bitmap(HASH:([H1].[wkhHdrID]), DEFINE:([Opt_Bitmap1043]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([H1].[wkhHdrID]))

| | |--Hash Match(Inner Join, HASH:([dv_wrp].[dbo].[Lot].[LotKey])=([H1].[wkhLotKey]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[Lot].[LotKey]))

| | | |--Index Scan(OBJECT:([dv_wrp].[dbo].[Lot].[idx_Lot2]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([H1].[wkhLotKey]))

| | |--Index Scan(OBJECT:([DataWhse].[dbo].[WorkHdr].[idx_WorkHdrUnique] AS [H1]))

| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiHdrID]))

| |--Hash Match(Inner Join, HASH:([dv_wrp].[dbo].[Item].[ItmItemCode])=([I1].[wkiCompItemID]))

| |--Bitmap(HASH:([dv_wrp].[dbo].[Item].[ItmItemCode]), DEFINE:([Opt_Bitmap1042]))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[Item].[ItmItemCode]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES:([dv_wrp].[dbo].[Variety].[VarVarietyCode], [Expr1045]) WITH UNORDERED PREFETCH)

| | |--Parallelism(Distribute Streams, RoundRobin Partitioning)

| | | |--Clustered Index Scan(OBJECT:([dv_wrp].[dbo].[Variety].[PK_Variety]))

| | |--Index Seek(OBJECT:([dv_wrp].[dbo].[Item].[idx_ItmVariety]), SEEK:([dv_wrp].[dbo].[Item].[ItmVarietyCode]=[dv_wrp].[dbo].[Variety].[VarVarietyCode]) ORDERED FORWARD)

| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiCompItemID]))

| |--Index Seek(OBJECT:([DataWhse].[dbo].[WorkIngr].[idx_WorkIngr1] AS [I1]), SEEK:([I1].[wkiTransDate] > '2009-12-31 00:00:00.000'), WHERE:(([DataWhse].[dbo].[WorkIngr].[wkiCompQty] as [I1]

|--Stream Aggregate(DEFINE:([Expr1024]=SUM([DataWhse].[dbo].[WorkPcts].[wkpCompQty] as [P2].[wkpCompQty])))

|--Nested Loops(Inner Join, OUTER REFERENCES:([H2].[wkhHdrID]))

|--Index Seek(OBJECT:([DataWhse].[dbo].[WorkHdr].[idx_WorkHdr1] AS [H2]), SEEK:([H2].[wkhLotKey]=[DataWhse].[dbo].[WorkHdr].[wkhLotKey] as [H1].[wkhLotKey]) ORDERED FORWARD)

|--Index Seek(OBJECT:([DataWhse].[dbo].[WorkPcts].[idx_WorkPcts2] AS [P2]), SEEK:([P2].[wkpHdrID]=[DataWhse].[dbo].[WorkHdr].[wkhHdrID] as [H2].[wkhHdrID] AND [P2].[wkpIntKey]=[DataWhse].[dbo].[WorkIngr].[wkiIntKey] as

(39 row(s) affected)

StmtText

-----------------

print 'exists

if exists(select LotIngrLotKey from va_negingrs)

print 'exists'

Returns this plan, which appears to be the same plan as above:

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE:([Expr1031]=CASE WHEN [Expr1032] IS NULL THEN (1) ELSE (0) END))

|--Nested Loops(Left Semi Join, DEFINE:([Expr1032] = [PROBE VALUE]))

|--Constant Scan

|--Parallelism(Gather Streams)

|--Filter(WHERE:(round(([Expr1019]*(1000.))/[Expr1024],(5))<(0.000000)))

|--Nested Loops(Left Outer Join, OUTER REFERENCES:([I1].[wkiPodKey], [I1].[wkiIntKey]))

|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiPodKey], [I1].[wkiIntKey]), ORDER BY:([I1].[wkiPodKey] ASC, [I1].[wkiIntKey] ASC))

| |--Stream Aggregate(GROUP BY:([I1].[wkiPodKey], [I1].[wkiIntKey], [I1].[wkiCompItemID], [I1].[wkiUOM], [I1].[wkiTransDate], [dv_wrp].[dbo].[ProdOrd].[PohPlantID], [dv_wrp].[dbo].[ProdOrd].[PohProdOrder]) DEFINE:([Expr1019]=S

| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiPodKey], [I1].[wkiIntKey], [I1].[wkiCompItemID], [I1].[wkiUOM], [I1].[wkiTransDate], [dv_wrp].[dbo].[ProdOrd].[PohPlantID], [dv_wrp].[db

| |--Nested Loops(Inner Join)

| |--Parallelism(Distribute Streams, Broadcast Partitioning)

| | |--Clustered Index Seek(OBJECT:([dv_wrp].[dbo].[Lot].[PK_Lot]), SEEK:([dv_wrp].[dbo].[Lot].[LotKey]=(0)) ORDERED FORWARD)

| |--Nested Loops(Inner Join, WHERE:([dv_wrp].[dbo].[Item].[ItmVarietyCode]=[dv_wrp].[dbo].[Variety].[VarVarietyCode]))

| |--Nested Loops(Inner Join, OUTER REFERENCES:([I1].[wkiCompItemID], [Expr1043]) WITH ORDERED PREFETCH)

| | |--Sort(ORDER BY:([I1].[wkiPodKey] ASC, [I1].[wkiIntKey] ASC, [I1].[wkiCompItemID] ASC, [I1].[wkiUOM] ASC, [I1].[wkiTransDate] ASC, [dv_wrp].[dbo].[ProdOrd].[PohPlantID] ASC, [dv_wrp].[dbo].[ProdOrd]

| | | |--Hash Match(Inner Join, HASH:([dv_wrp].[dbo].[ProdOrdDtl].[PodPohKey])=([dv_wrp].[dbo].[ProdOrd].[PohKey]))

| | | |--Bitmap(HASH:([dv_wrp].[dbo].[ProdOrdDtl].[PodPohKey]), DEFINE:([Bitmap1042]))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrdDtl].[PodPohKey]))

| | | | |--Hash Match(Inner Join, HASH:([I1].[wkiPodKey])=([dv_wrp].[dbo].[ProdOrdDtl].[PodKey]))

| | | | |--Bitmap(HASH:([I1].[wkiPodKey]), DEFINE:([Bitmap1041]))

| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiPodKey]))

| | | | | |--Hash Match(Inner Join, HASH:([H1].[wkhHdrID])=([I1].[wkiHdrID]))

| | | | | |--Bitmap(HASH:([H1].[wkhHdrID]), DEFINE:([Opt_Bitmap1036]))

| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([H1].[wkhHdrID]))

| | | | | | |--Index Seek(OBJECT:([DataWhse].[dbo].[WorkHdr].[idx_WorkHdr1] AS [H1]), SEEK:([H1].[wkhLotKey]=(0)) ORDERED FORWARD)

| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([I1].[wkiHdrID]))

| | | | | |--Index Seek(OBJECT:([DataWhse].[dbo].[WorkIngr].[idx_WorkIngr1] AS [I1]), SEEK:([I1].[wkiTransDate] > '2009-12-31 00:00:00.000'), WHERE:(([DataWhse].[d

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrdDtl].[PodKey]))

| | | | |--Index Scan(OBJECT:([dv_wrp].[dbo].[ProdOrdDtl].[idx_ProdOrdDtl]), WHERE:(PROBE([Bitmap1041],[dv_wrp].[dbo].[ProdOrdDtl].[PodKey],N'[IN ROW]')))

| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([dv_wrp].[dbo].[ProdOrd].[PohKey]))

| | | |--Index Scan(OBJECT:([dv_wrp].[dbo].[ProdOrd].[idx_ProdOrd]), WHERE:(PROBE([Bitmap1042],[dv_wrp].[dbo].[ProdOrd].[PohKey],N'[IN ROW]')))

| | |--Index Seek(OBJECT:([dv_wrp].[dbo].[Item].[idx_Item2]), SEEK:([dv_wrp].[dbo].[Item].[ItmItemCode]=[DataWhse].[dbo].[WorkIngr].[wkiCompItemID] as [I1].[wkiCompItemID]) ORDERED FORWARD)

| |--Clustered Index Scan(OBJECT:([dv_wrp].[dbo].[Variety].[PK_Variety]))

|--Table Spool

|--Stream Aggregate(DEFINE:([Expr1024]=SUM([DataWhse].[dbo].[WorkPcts].[wkpCompQty] as [P2].[wkpCompQty])))

|--Nested Loops(Inner Join, OUTER REFERENCES:([P2].[wkpHdrID]))

|--Index Seek(OBJECT:([DataWhse].[dbo].[WorkPcts].[idx_WorkPcts3] AS [P2]), SEEK:([P2].[wkpIntKey]=[DataWhse].[dbo].[WorkIngr].[wkiIntKey] as [I1].[wkiIntKey]), WHERE:([DataWhse].[dbo].[WorkPcts].[wkpPodKey] as [P

|--Index Seek(OBJECT:([DataWhse].[dbo].[WorkHdr].[idx_WorkHdr1] AS [H2]), SEEK:([H2].[wkhLotKey]=(0) AND [H2].[wkhHdrID]=[DataWhse].[dbo].[WorkPcts].[wkpHdrID] as [P2].[wkpHdrID]) ORDERED FORWARD)

(38 row(s) affected)

IF 0 NOT IN (SELECT ISNULL(LotIngrLotKey,0) FROM va_negingrs)

print 'exists'

Produces this plan, and is the only one that ever returns any rows, I presume because there are fewer index scans and more index seeks than the previous plans: