SQL Server Performance Forum – Threads Archive

Parallelism … what triggers it ?

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

---

]]>