Parallelism & Optimization of queries
-
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_nme as 'Comp. Name'
from arpcop_m cop
inner join arpdoc_m doc on (cop.ctg_dte = doc.ctg_dte and cop.ctg_grp = doc.ctg_grp)
inner join 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_nme as 'Comp. Name'
from arpcop_m cop
inner join arpdoc_m doc on (cop.ctg_dte = doc.ctg_dte and cop.ctg_grp = doc.ctg_grp)
inner join 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
-
Allow me to differ! ... well a bit...
The "optimized" didn't removed the joins it simply added one more filter to the equation
Take QUERY A and add that too and you will get the same speed:
ex:
select ctm.zip_cde as 'Zip Cpde',
ctm.atn_end as 'Last Name',
ctm.cmp_nme as 'Comp. Name'
from arpcop_m cop
inner join arpdoc_m doc on (cop.ctg_dte = doc.ctg_dte and cop.ctg_grp = doc.ctg_grp)
inner join 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 and DOC1.ctg_dte > @dateCutoff
union
select ctm.zip_cde as 'Zip Cpde',
ctm.atn_end as 'Last Name',
ctm.cmp_nme as 'Comp. Name'
from arpcop_m cop
inner join arpdoc_m doc on (cop.ctg_dte = doc.ctg_dte and cop.ctg_grp = doc.ctg_grp)
inner join 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 and DOC1.ctg_dte > @dateCutoff
hth
* Noel -
Hey!
I also use Advantage CRM, which version do you use?
Which company you are in?
-MP
-
manub22 (12/28/2009)
Hey!I also use Advantage CRM, which version do you use?
Which company you are in?
You did notice that this thread is 4 and a half years old...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply