11

I have the following SQL:

 IF EXISTS
 (
    SELECT
        1
    FROM
        SomeTable T1
    WHERE
        SomeField = 1
    AND SomeOtherField = 1
    AND NOT EXISTS(SELECT 1 FROM SomeOtherTable T2 WHERE T2.KeyField = T1.KeyField)
)
    RAISERROR ('Blech.', 16, 1)

The SomeTable table has around 200,000 rows, and the SomeOtherTable table has about the same.

If I execute the inner SQL (the SELECT), it executes in sub-second time, returning no rows. But, if I execute the entire script (IF...RAISERROR) then it takes well over an hour. Why?

Now, obviously, the execution plan is different - I can see that in Enterprise Manager - but again, why?

I could probably do something like SELECT @num = COUNT(*) WHERE ... and then IF @num > 0 RAISERROR but... I think that's missing the point somewhat. You can only code around a bug (and it sure looks like a bug to me) if you know that it exists.

EDIT:

I should mention that I already tried re-jigging the query into an OUTER JOIN as per @Bohemian's answer, but this made no difference to the execution time.

EDIT 2:

I've attached the query plan for the inner SELECT statement:

Query Plan - inner SELECT statement

... and the query plan for the whole IF...RAISERROR block:

Query Plan - whole IF statement

Obviously these show the real table/field names, but apart from that the query is exactly as shown above.

Gary McGill
  • 26,400
  • 25
  • 118
  • 202
  • Interesting question. Can you provide us the execution plans? – Fabian Barney Mar 26 '13 at 11:10
  • Forcing the system to *count* the number of rows, rather than allowing it to return as soon as it has determined that *any* row exists can only ever be a pessimization - even if it did improve the current performance issue, it would be an accident rather than addressing the underlying issue. – Damien_The_Unbeliever Mar 26 '13 at 11:10
  • How long does it take to run the query: `select case when exists (SELECT 1 FROM SomeTable T1 WHERE SomeField = 1 AND SomeOtherField = 1 AND NOT EXISTS(SELECT 1 FROM SomeOtherTable T2 WHERE T2.KeyField = T1.KeyField) ) then 1 else 0 end` ? (By using `case when exists`, this should enable it to short circuit instead of doing the full count.) –  Mar 26 '13 at 11:14
  • 1
    @MarkBannister: Ages :-) But see usr's answer - he seems to have nailed the cause of the "pessimization" (new word I learned today from Damien_The_Unbeliever). – Gary McGill Mar 26 '13 at 11:52

3 Answers3

6

The IF does not magically turn off optimizations or damage the plan. The optimizer just noticed that EXISTS only needs one row at most (like a TOP 1). This is called a "row goal" and it normally happens when you do paging. But also with EXISTS, IN, NOT IN and such things.

My guess: if you write TOP 1 to the original query you get the same (bad) plan.

The optimizer tries to be smart here and only produce the first row using much cheaper operations. Unfortunately, it misestimates cardinality. It guesses that the query will produce lots of rows although in reality it produces none. If it estimated correctly you'd just get a more efficient plan, or it would not do the transformation at all.

I suggest the following steps:

  1. fix the plan by reviewing indexes and statistics
  2. if this didn't help, change the query to IF (SELECT COUNT(*) FROM ...) > 0 which will give the original plan because the optimizer does not have a row goal.
usr
  • 168,620
  • 35
  • 240
  • 369
  • This sounds very convincing and logical. However, I tried converting from `IF EXISTS(...)` to `IF (SELECT COUNT(*) FROM ...) > 0` and it made no difference. Perhaps the optimizer is being "clever" about the fact that the result of the COUNT is only used as an existence test :-) – Gary McGill Mar 26 '13 at 11:21
  • Changing the inner query to `SELECT TOP 1 1 FROM ...` does indeed make it run very slowly. Kinda going in the wrong direction, though :-) – Gary McGill Mar 26 '13 at 11:23
  • Good that you tested it. I don't think it would "get" the existence test with COUNT so I'm at a loss why the plan would not change. Can you post the plan as an image? Maybe you can indeed use the local variable trick that you posted in the question or try other rewrites. – usr Mar 26 '13 at 11:26
  • Wait - when you suggested I change the `EXISTS`, did you mean the outer `EXISTS` or the inner one? I tried changing the outer one, but not the inner one... I'll try that now. – Gary McGill Mar 26 '13 at 11:32
  • Nope, that made no difference either :-( – Gary McGill Mar 26 '13 at 11:35
  • Damn it! It really saw through the count(*) test (as proven by the absence of aggregation)! I'm sure this optimization was not present some time ago. – usr Mar 26 '13 at 11:41
  • Ok, here's a workaround: `(count_query_here) + 1 > 1`. I tested it. What's your SQL Server version? – usr Mar 26 '13 at 11:44
  • 1
    Well, that worked. Kudos to you! (I'm using SQL Server 2008 R2). – Gary McGill Mar 26 '13 at 11:47
  • Thanks for your help, and the following isn't aimed at you, but this still seems nuts to me - I can't go peppering my code with this sort of voodoo! How the heck am I supposed to know when to write SQL and when to write voodoo?! – Gary McGill Mar 26 '13 at 11:49
  • That's why I recommended fixing the cardinality estimation as a first step. That might not be possible, but if it is, it is the least fragile option. I *hate* using plan hints (or hacks like this one for that matter) because they cause so much maintenance work in the future. I really try to fix cardinalities.; For testing reasons you could try to create lots of indexes on all relevant filter and join columns to see if this helped. If it did, statistics might be enough. Remember, the key issue is that SQL Server thought the join would produce lots of rows while it produced none. – usr Mar 26 '13 at 11:51
  • OK, thanks. My problem is that my personal toolbox contains SQL language skills, but is light on plan optimization skills. Like many people, I would guess. What I'm actually going to do is "optimize" it by removing that query entirely, since it's merely a sanity-check :-) – Gary McGill Mar 26 '13 at 11:57
2

It's probably because the optimizer can figure out how to turn your query into a more efficient query, but somehow the IF prevents that. Only an EXPLAIN will tell you why the query is taking so long, but I can tell you how to make this whole thing more efficient... Indtead of using a correlated subquery, which is incredibly inefficient - you get "n" subqueries run for "n" rows in the main table - use a JOIN.

Try this:

IF EXISTS (
  SELECT 1
  FROM SomeTable T1
  LEFT JOIN SomeOtherTable T2 ON T2.KeyField = T1.KeyField
  WHERE SomeField = 1
  AND SomeOtherField = 1
  AND T2.KeyField IS NULL
) RAISERROR ('Blech.', 16, 1)

The "trick" here is to use s LEFT JOIN and filter out all joined rows by testing for a null in the WHERE clause, which is executed after the join is made.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • `Left join ... where null` definitely works faster in MySQL than `not exists`, but does it do so in SQLServer? (For example, my understanding is that in Oracle `not exists` is faster.) –  Mar 26 '13 at 10:54
  • Thanks, but I already tried this - and unfortunately it makes no difference :-( – Gary McGill Mar 26 '13 at 10:58
  • @MarkBannister could well be. My guess is that the IF is causing the opyimizer to be bypassed. Without an explain output, we can't know. I admit I've little experience with SQL Server - if you say it, I believe it. – Bohemian Mar 26 '13 at 11:00
  • @Bohemian: In SQLServer, I don't know (which is why I was asking). I have had a quick google, and this question: http://stackoverflow.com/questions/6777910/ and this blogpost: http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/ seem to indicate that where a suitable index is available, a `not exists` will be faster in SQLServer. –  Mar 26 '13 at 11:05
0

Please try SELECT TOP 1 KeyField. Using primary key will work faster in my guess.

NOTE: I posted this as answer as I couldn't comment.

Maximus
  • 792
  • 9
  • 19
  • Changing `IF EXISTS( SELECT 1 FROM ...` to `IF EXISTS( SELECT TOP 1 KeyField FROM ...` just makes the inner query much slower, rather than speeding up the `IF`. – Gary McGill Mar 26 '13 at 11:44
  • I just did some anylysis reagrding this issue. Usually Exists using semi join, that is the query will terminate after find the first record. In my guess semi join is not seems to be applied while using exists inside an exists. when you run the inner query you have only one exists statement, so the semi join is applied. But when you use if exists only the outer exists will use semi join so the inner query needs to be run fully. – Maximus Mar 26 '13 at 12:36