Post reply

Need to TUNE SQL Statement

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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