Need to TUNE SQL Statement

• Express12

  SSCoach

  Points: 18514

  More actions

  June 20, 2006 at 8:05 am

  #114553

  this sql is taking a long time to execute.  Generically, how can I rewrite this to utilize inner joins?

  fyi, the inner most SQL 5-Union Select runs in less than 1 second stand alone; both predicate columns: effective_date and col_1 -- are indexed

  SELECT 'MyDatabase..TABLE_A rowcount', count(*) FROM MyDatabase..TABLE_A

   WHERE col_1 in

   (SELECT col_1 FROM MyDatabase..TABLE_B WHERE effective_date in

    (SELECT MyDatabase.dbo.fn_PrevQTRDate(GETDATE()) -- this function returns last day of prior qtr - RUNS FAST

      UNION SELECT MyDatabase.dbo.fn_PrevQTRDate(MyDatabase.dbo.fn_PrevQTRDate(GETDATE())) 

      UNION SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)),101)

      UNION SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))), 0)),101)

      UNION SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()), 0)),101) ))

  BT

• stax68

  SSChampion

  Points: 11711

  More actions

  June 20, 2006 at 8:37 am

  #644826

  --I suspect you're comparing chars to dates, which would slow things down.

  --also don't understand why you are looking only at these dates. But you may have your reasons...

  --it may also be that the nested subqueries are confusing the optimiser.

  --try this:

   

  declare

  @getdate_notime datetime, @d1 datetime,@d2 datetime,@d3 datetime,@d4 datetime, @d5 datetime

  select @getdate_notime = datediff(day,0,getdate())

  select @d1 = MyDatabase.dbo.fn_PrevQTRDate(@getdate_notime)

  select

  @d2 = MyDatabase.dbo.fn_PrevQTRDate(@d1)

  ,@d3 = dateadd(year,datediff(year,0,@getdate_notime),0)-1

  ,

  @d4 = dateadd(month,datediff(month,0,@getdate_notime),0)-1

  ,

  @d5 = dateadd(month,datediff(month,0,@getdate_notime)-1,0)-1

  select

  count(distinct a.PK) [MyDatabase..TABLE_A rowcount]

  FROM

  MyDatabase..TABLE_A a

  JOIN

  MyDatabase..TABLE_B b

  on a.col1 = b.col2

  WHERE

  b.effective_date in (@getdate_notime, @d1, @d2, @d3, @d4, @d5)

  --depending on the numbers of relevant records in tables a and b, it might be worth distincting the col1 values from b in an inline view, before joining to table a. You then wouldn't need the distinct in 'count(distinct PK)'.

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant