SQL Server Performance

Parallelism ... what triggers it ?

Discussion in 'T-SQL Performance Tuning for Developers' started by homebrew01, May 18, 2005.

  1. homebrew01 New Member

    I have a query (QUERY A) that runs in 6+ minutes, and generates an execution plan that doesn't use parallelism.

    Then, I used an optimization tool, and it re-wrote my query (QUERY B), and it runs twice as fast, and does use parallelism in the execution plan. (A & B are simplified somewhat for clarity here)

    What are the pros & cons of writing the query as it is in QUERY B, and why doesn't QUERY A make use of parallelism ?? Query B removed the "join" statements. What do people think of that style of coding ??

    QUERY A

    select ctm.zip_cde as 'Zip Cpde',
    ctm.atn_end as 'Last Name',
    ctm.cmp_nmeas 'Comp. Name'
    from arpcop_m cop
    innerjoin arpdoc_m doc on(cop.ctg_dte = doc.ctg_dte and
    cop.ctg_grp = doc.ctg_grp)
    innerjoin arpuch_m uch on (uch.crx_vch = doc.dbt_nbr)
    inner join cdsadr_m ctm on (ctm.ctm_nbr = uch.ctm_nbr and
    ctm.adr_flg = '0')
    where cop.ctg_dte > @dateCutoff

    union
    select ctm.zip_cde as 'Zip Cpde',
    ctm.atn_end as 'Last Name',
    ctm.cmp_nmeas 'Comp. Name'
    from arpcop_m cop
    innerjoin arpdoc_m doc on(cop.ctg_dte = doc.ctg_dte and
    cop.ctg_grp = doc.ctg_grp)
    innerjoin arppch_m pch on (pch.crx_vch = doc.dbt_nbr)
    inner join cdsadr_m ctm on (ctm.ctm_nbr = pch.ctm_nbr and
    ctm.adr_flg = '0')
    where cop.ctg_dte > @dateCutoff and(pch.crd_amt - pch.crd_bal) <> 0


    QUERY B

    select CTM1.zip_cde as 'Zip Cpde',
    CTM1.atn_end as 'Last Name',
    CTM1.cmp_nme as 'Comp. Name'
    from arpcop_m COP1 (nolock) ,
    arpdoc_m DOC1 (nolock) ,
    arpuch_m uch (nolock) ,
    cdsadr_m CTM1 (nolock)
    WHERE COP1.ctg_dte > @dateCutoff
    AND DOC1.ctg_dte > @dateCutoff
    AND CTM1.ctm_nbr = uch.ctm_nbr
    and CTM1.adr_flg = '0'
    AND uch.crx_vch = DOC1.dbt_nbr
    AND COP1.ctg_dte = DOC1.ctg_dte
    and COP1.ctg_grp = DOC1.ctg_grp

    union
    select CTM2.zip_cde as 'Zip Cpde',
    CTM2.atn_end as 'Last Name',
    CTM2.cmp_nme as 'Comp. Name'

    from arpcop_m COP2 (nolock) ,
    arpdoc_m DOC2 (nolock) ,
    arppch_m pch (nolock) ,
    cdsadr_m CTM2 (nolock)
    WHERE COP2.ctg_dte > @dateCutoff
    AND DOC2.ctg_dte > @dateCutoff
    and pch.crd_amt - pch.crd_bal <> 0
    AND CTM2.ctm_nbr = pch.ctm_nbr
    and CTM2.adr_flg = '0'
    AND pch.crx_vch = DOC2.dbt_nbr
    AND COP2.ctg_dte = DOC2.ctg_dte
    and COP2.ctg_grp = DOC2.ctg_grp

Share This Page