Why would wrapping a TSQL query in an if statement increase its runtime significantly?

• mickyT

  SSChampion

  Points: 10360

  More actions

  March 19, 2014 at 2:51 pm

  #1698525

  triynko22 (3/19/2014)

  ---

  mickyT: Which query do you think is a "winner", because those are not logically equivalent queries to my original query. For example, query 6 is using an "exists" in the where clause, which is incorrect. Of course that will short circuit because there exists 2.3 million matching rows. We're looking for existence of non-matching rows (i.e. where not exists or where ResponseID is null in the 2nd table) When you change it to "not exists" (since we're looking for rows that exist in one table but not the other), then it exhibits the exact same performance issue.

  GilaMonster: The "top(1)" query you suggested does exhibit the same query plan and same performance issue.

  Sorry ... I lost sight of the logic in there:crying:

  And I'm going to have to go with it depends. On the real tables that I am trying this against the HAVING clause works bestIF NOT EXISTS (

  SELECT COUNT(*)

  FROM Table1 c

  WHERE NOT EXISTS ( SELECT 1 FROM Table2 z WHERE z.ID = c.ID)

  HAVING COUNT(*) > 0

  )

  PRINT ' Result 0'

  Against the generated tables, the NOT EXISTS/NOT EXISTS works best if there is a non zero count and about the same for a zero count

  IF NOT EXISTS (

  SELECT 1

  FROM Table1 c

  WHERE NOT EXISTS ( SELECT 1 FROM Table2 z WHERE z.ID = c.ID)

  )

  PRINT ' Result 0'

  I think you'll have to try it and see which works best for you.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 20, 2014 at 2:42 am

  #1698593

  Confirmed then, the problem is the row goal (of 1) which the EXISTS is forcing into the plan. Bloody hard to work around to be honest.

  It's usually not the efficient option, but try this (and comment the hell out of it because otherwise someone will 'optimise' it later)

  DECLARE @CountOfRows INT;

  SELECT @CountOfRows = COUNT(*)

  FROM Responses r

  WHERE r.ResponseID NOT IN ( SELECT ResponseID

  FROM data.GamingReport_Computerized )

  IF ( @CountOfRows = 0 )

  BEGIN

  SELECT 'update will be skipped to save time'

  END

  ELSE

  BEGIN

  SELECT 'missing rows will be inserted'

  END

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass