Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77

    Unanswered: sp3 slowing down query???

    hello, I would like to know your opinion, we run several scripts every night and night after application of sp3 to our sql server, one of them runs for 2 hours instead of 6 minutes as before, is it possible or is it just coincidence? I checked number of data in tables involved and it's almost unchanged, I post the query. thanks mojza


    /*select
    2, -- IS SCALA
    isnull(a.IDZ_ACCOUNT,-1),
    isnull(i.ID_INVOICE_TYPE, -1), -- invoice type
    isnull(b.ID_BRANCH,-1),
    isnull(p.ID_PRODUCT,-1),
    i.ID_INVOICE,
    g.GL06003, -- transaction date
    case g.GL06017 when '1' then case when (-g.GL06004 > 0) then g.GL06004 else 0 end
    else case when (g.GL06004 > 0) then g.GL06004 else 0 end
    end, -- DEBIT_CZK
    case g.GL06017 when '1' then case when (-g.GL06004 < 0) then -g.GL06004 else 0 end
    else case when (g.GL06004 < 0) then -g.GL06004 else 0 end
    end, -- CREDIT_CZK
    right('x00'+g.GL06007,10), -- invoice nb
    g.GL06006, -- contract nb
    g.GL06002 -- transaction nb
    from SP_SG..S_GL06 g
    inner join wrk_which_inv which on right('x00'+g.GL06007,10) = which.INVOICE_NB and g.GL06006 = which.CONTRACT_NB
    inner join DW_F_INVOICE i on i.id_invoice=which.id_invoice
    left join DW_VD_ACCOUNT a on g.ACC = a.ACCOUNT_NB and g.GL06003 between a.VALID_FROM and a.VALID_TO
    left join DW_D_BRANCH b on g.BRANCH = b.CODE
    left join DW_D_PRODUCT p on g.PRODUCT = p.CODE_SCALA
    where g.SCCOMP = '01' -- Sodexho Pass CR only
    -- and (g.GL06012 = 0x33 or g.GL06012 = 0x35) -- Customer or Supplier Invoice records only (asi take kod 34 a proforma fac)
    OPTION (MAXDOP 1)*/

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: sp3 slowing down query???

    Run the UPDATE STATISTICS with FULLSCAN option.

    This is Microsoft recomandation after a major update (data or SP)


    Originally posted by mojza
    hello, I would like to know your opinion, we run several scripts every night and night after application of sp3 to our sql server, one of them runs for 2 hours instead of 6 minutes as before, is it possible or is it just coincidence? I checked number of data in tables involved and it's almost unchanged, I post the query. thanks mojza


    /*select
    2, -- IS SCALA
    isnull(a.IDZ_ACCOUNT,-1),
    isnull(i.ID_INVOICE_TYPE, -1), -- invoice type
    isnull(b.ID_BRANCH,-1),
    isnull(p.ID_PRODUCT,-1),
    i.ID_INVOICE,
    g.GL06003, -- transaction date
    case g.GL06017 when '1' then case when (-g.GL06004 > 0) then g.GL06004 else 0 end
    else case when (g.GL06004 > 0) then g.GL06004 else 0 end
    end, -- DEBIT_CZK
    case g.GL06017 when '1' then case when (-g.GL06004 < 0) then -g.GL06004 else 0 end
    else case when (g.GL06004 < 0) then -g.GL06004 else 0 end
    end, -- CREDIT_CZK
    right('x00'+g.GL06007,10), -- invoice nb
    g.GL06006, -- contract nb
    g.GL06002 -- transaction nb
    from SP_SG..S_GL06 g
    inner join wrk_which_inv which on right('x00'+g.GL06007,10) = which.INVOICE_NB and g.GL06006 = which.CONTRACT_NB
    inner join DW_F_INVOICE i on i.id_invoice=which.id_invoice
    left join DW_VD_ACCOUNT a on g.ACC = a.ACCOUNT_NB and g.GL06003 between a.VALID_FROM and a.VALID_TO
    left join DW_D_BRANCH b on g.BRANCH = b.CODE
    left join DW_D_PRODUCT p on g.PRODUCT = p.CODE_SCALA
    where g.SCCOMP = '01' -- Sodexho Pass CR only
    -- and (g.GL06012 = 0x33 or g.GL06012 = 0x35) -- Customer or Supplier Invoice records only (asi take kod 34 a proforma fac)
    OPTION (MAXDOP 1)*/
    Steve

  3. #3
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77

    Re: sp3 slowing down query???

    thanks a lot, it helped, I found out by studying execution plan that sql server estimated 1.5 million rows in a table that actually has 1500 rows, so it probably chose a little inappropriete strategy to process the query, I think

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •