While/If conditionals neither true nor false when using "EXISTS"

Question

While/If conditionals neither true nor false when using "EXISTS"

Doug Perlich

SSC Journeyman

Points: 76
More actions
March 23, 2007 at 3:26 pm

#369929

This started as a WHILE loop and then I switched to IF to see if it made any difference. I do not understand why this conditional "won't finish".
IF EXISTS (SELECT *
FROM adm_daily_user_activity (NOLOCK)
WHERE EXISTS
  (SELECT * FROM bad_users (NOLOCK)
  WHERE adm_daily_user_activity.user# = bad_users.user#))
BEGIN
select getdate()
END
An alternative method also "does not finish":
IF EXISTS (SELECT *
FROM adm_daily_user_activity (NOLOCK), bad_users (NOLOCK)
WHERE adm_daily_user_activity.user# = bad_users.user#))
BEGIN
select getdate()
END
The above SQL will run and run and run - never finishing and never showing the date. Yet the following finishes just fine:
IF (SELECT COUNT (*)
FROM adm_daily_user_activity (NOLOCK)
WHERE EXISTS
  (SELECT * FROM bad_users (NOLOCK)
   WHERE adm_daily_user_activity.user# = bad_users.user#)) > 0
BEGIN
select getdate()
END
The adm_ table has 600K rows and bad_users has 41K rows. The weird thing is that if I reduce bad_users to 11K rows - the condition completes immediately. I bump it to 12K rows and it runs for over 5 minutes without completing. I let the 46K rows run overnight and it didn't finsh.
EM shows nothing blocking, the process just appears to run and run!
Huh?!!!
I did some other experimenting and it seems that as long as the EXISTS part just deals with one table and no sub-queries or >1 table then it finishes just fine. Seems like a "capacity" issue but I searched around and can't find anyone else complaining of something similar!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Best way to think about it is this: It acts like a cursor, and you have them nested, each of the 600K rows has the potential of running through all 41K rows of the other table to see if the EXISTS is true or not.

That works out to about 24.6 million iterations for the select statement. That is way it takes forever.

Doug Perlich SSC Journeyman Points: 76 More actions · Answer 2

Thank you for your reply but I'm still not sure I understand. Since I am joining the two tables, it should have to loop through all 24.6M iterations - at max there should be 41K iterations because as soon as there's a match in the join, the EXISTS should be true and it should drop out of the loop for that row...

In other words, I think my SQL boils down to:

IF (SELECT COUNT (*)

FROM adm_monthly_user_activity a (NOLOCK), bad_users (NOLOCK)

WHERE a.user# = bad_users.user#) > 0

Is more efficient than:

IF EXISTS (SELECT *

FROM adm_monthly_user_activity a (NOLOCK), bad_users (NOLOCK)

WHERE a.user# = bad_users.user#)

Yet everything I read says that "EXISTS" is highly efficient and good for performance.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I have used EXISTS in queries, but I have found that in many cases an inner join actually worked better than the EXISTS. It really depends on the queries you are running.

Doug Perlich SSC Journeyman Points: 76 More actions · Answer 4

Thanks Lynn, I guess I'm learning that these performance "rules" really are not hard and fast. I don't expect them to be but it sure is causing me frustration trying to make my apps a little faster! 🙂 Thank you for your comments.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Best suggestion I can give, and that I have seen from others on this site, is test, test, and test some more. You will find that some times the EXISTS works better, some times it doesn't.