Post reply

Help with Optimization

  • June 14, 2010 at 12:19 am

    #233730

    select /*+Rule*/

    b.pssubscribernr "Customer Nr", b.psaccnr "Account Nr", p.productuserkey "DCC Product", a.totalamount "Price", psnr,

    t.substypename "Customer Type", g.entityname "Account Type", totalbal, afterdue130, afterdue3160, afterdue6190, afterdue91120,

    afterdue121150, afterdue151180, afterdue181plus, mopuserkey mop

    from x_dcc_price_3 a, prodsubs b, products p, suinvoic i, genentty g, subscrib c, substype t, methopay m

    where decode

    (

    a.priceinvoicetext1,

    :"SYS_B_00", :"SYS_B_01",

    :"SYS_B_02", :"SYS_B_03",

    :"SYS_B_04", :"SYS_B_05",

    :"SYS_B_06", :"SYS_B_07",

    :"SYS_B_08", :"SYS_B_09",

    :"SYS_B_10", :"SYS_B_11"

    ) = p.productuserkey

    and b.psproductnr = p.productnr

    and b.psaccnr = i.accountnr

    and b.psstatus = :"SYS_B_12"

    and i.accaccounttype = g.a30egregioncode

    and g.entitytypenr = :"SYS_B_13"

    and i.accmop = m.mopkey

    and i.totalbal >= :"SYS_B_14"

    and i.afterdue130 >= :"SYS_B_15"

    and i.afterdue3160 >= :"SYS_B_16"

    and i.afterdue130 = i.afterdue3160

    and c.cutype = t.subscribertype

    and b.pssubscribernr = c.cucustnr

    and b.pssubscribernr = i.accsubscribernr

    and exists

    (select null

    from prodsubs a

    where a.psproductnr in (:"SYS_B_17", :"SYS_B_18", :"SYS_B_19", :"SYS_B_20", :"SYS_B_21", :"SYS_B_22")

    and a.psstatus = :"SYS_B_23"

    and b.pssubscribernr = a.pssubscribernr)

    and not exists

    (select null

    from prodsubs a

    where a.psproductnr not in (:"SYS_B_24", :"SYS_B_25", :"SYS_B_26", :"SYS_B_27", :"SYS_B_28", :"SYS_B_29")

    and a.psstatus = :"SYS_B_30"

    and b.pssubscribernr = a.pssubscribernr)

    order by b.pssubscribernr

  • June 14, 2010 at 1:56 am

    #1179695

    Please post DDL etc as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


    Clear Sky SQL
    My Blog[/url]

  • June 14, 2010 at 2:12 am

    #1179698

    Do you have indexs where you are making the joins and the condicions?

  • June 14, 2010 at 2:15 am

    #1179699

    Quite Honestly i have no idea on the Database properties.

    This is a user just requesting help. Let me find out more about the table properties.

  • June 14, 2010 at 2:19 am

    #1179700

    If you do not have indexs on does fields where you are making comparations, the time to retrive the results will be much larger.

  • June 14, 2010 at 2:25 am

    #1179703

    Is this even sqlServer ? Decode is not a sqlserver function


    Clear Sky SQL
    My Blog[/url]

  • June 14, 2010 at 2:28 am

    #1179705

    ??? didn't understood your question..

  • June 14, 2010 at 3:23 am

    #1179717

    DECODE is an Oracle function, not a SQL Server one. If this is an Oracle database, you'll get far better answers on an Oracle forum than a MS SQL Server forum. May I suggest http://www.dbforums.com

    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

  • June 14, 2010 at 3:25 am

    #1179720

    yap, correct

Viewing 9 posts - 1 through 8 (of 8 total)

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