Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need Help to Speed up my Stored Procedure

Author  Topic 

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-22 : 16:17:24
I have created a stored procedure which is quite complex, It seems to work efficiently, but it seems to take 20 minutes to produce results. Any expert would like to help me to speed it up, it will be better to have a running time in 10 minutes. I pasted a copy of the query below any help with this would be gratefully appreciated.


(3714124 row(s) affected)
1187 (running time)
(1559410 row(s) affected)
24 (running time)


ALTER proc [dbo].[proc_Accelerate] @inforcedate as smalldatetime
as

IF OBJECT_ID('inforcebuild.dbo.tbl_inf_TAS_NEW_controls') IS NOT NULL
DROP table inforcebuild.dbo.tbl_inf_TAS_NEW_controls

IF OBJECT_ID('inforcebuild.dbo.tbl_inf_TAS_NEW') IS NOT NULL
DROP table inforcebuild.dbo.tbl_inf_TAS_NEW

SELECT DISTINCT a.Policy AS PolicyNumber,
a.PersonSequence AS PersonNumber,
a.CoveredPerson AS PersonCode,
PhaseBenCat = case when a.BENEFITPACKAGE = 'NAV' AND a.BenefitCatSubstituted = 'FOO' AND a.PLANCODE LIKE 'C%RNFC9' THEN 'NHO'
when a.BENEFITPACKAGE = 'NAV' AND a.BenefitCatSubstituted = 'CCO' AND a.PLANCODE LIKE 'C%HNFC9' THEN 'HHO'
else a.BenefitCatSubstituted end,

a.BenefitCatFixed as phaseBenFixed,
n.Coverage AS BenefitCoverage,
a.Phase AS PhaseNumber,
a.Company,
a.PolicyStatus,
UWAge = isnull(p.UWAge,a.UWAge),
a.PhaseGender AS Gender,
a.MaritalStatus,
a.APIF AS PolicyAPIF,
a.PhaseAPIF,
UWDate = isnull(p.uwdate,a.UWDate),
a.PlanCode,
a.PhaseStatus,
a.Units,
a.Options AS OptionCode,
a.Mode,
a.MaritalRule,
a.BioOptions,
a.BenefitPeriod,
a.BenefitAmount,
a.AltUnits AS AlternateUnits,
BenefitPackage = case when a.BENEFITPACKAGE = 'NAV' AND a.BenefitCatSubstituted = 'FOO' AND a.PLANCODE LIKE 'C%RNFC9' THEN 'NH+ALF+HHC'
when a.BENEFITPACKAGE = 'NAV' AND a.BenefitCatSubstituted = 'CCO' AND a.PLANCODE LIKE 'C%HNFC9' THEN 'NH+ALF+HHC'
else a.BenefitPackage end,
a.NHFlag AS NHPoolingFlag,
a.NHPoolUnits AS NHUnits,
a.AlfPoolUnits AS ALFUnits,
a.HHCPoolUnits AS HHCUnits,

case
when a.BenefitCatSubstituted = 'FOO'
AND a.Units >= .005
AND a.NHPoolUnits > 0
then convert(smallint,100*a.AlfPoolUnits/a.NHPoolUnits)
else convert(smallint,-1)
end AS [ALF%],
a.ExtraSpouse,
a.PremiumPerson,
case
when a.ExtraSpouse = '99'
or a.PremiumPerson = a.CoveredPerson
THEN 'N'
ELSE 'Y'
END AS FreeSpouseFlag,
case a.PolicyStatus when '4'
then 'P'
when '7'
then 'S'
else 'N'
end AS PaidUpFlag,
a.DeceasedSpouse AS DeadSpouseFlag,
a.SurvivorAvailablePolicy AS SurvivorshipIndic,
IssueYear = Isnull(year(p.uwdate),year(a.UWdate)),
IssueMonth = ISNULL(MONTH(p.uwdate),MONTH(a.UWdate)),
a.ShortDescription,
a.BaseRiderFlag AS BaseRider,

(case a.Mode when 'A' then a.PhaseAPIF
when 'S' then a.PhaseAPIF/2
when 'Q' then a.PhaseAPIF/4
when 'M' then a.PhaseAPIF/12
end) AS ModePremium,
case
when a.ShortDescription = 'uniprod'
and len(ltrim(rtrim(a.options))) = 8
then case
when substring(ltrim(rtrim(a.options)),4,1) in ('3','4')
then 2
when substring(ltrim(rtrim(a.options)),4,1) in ('5','6','7')
then 1
else 0
end
else 0
end as BioType,
'N' as PolicyPoolingFlag, --By default, it is 'N'
case
when (a.ShortDescription = 'FC1' and n.Coverage = 'HHO')
or a.PlanCode = 'BF63'
then substring(ltrim(rtrim(a.options)),Len(ltrim(rtrim(a.options)))-1,1)
else right(ltrim(rtrim(a.options)),1)
end DiscOption,
a.subphase,
IGflag = isnull(q.[Ind or Group Flag],'i'),
a.state,
a.ValBasePlan,
a.ValBaseSub,
accumprem = isnull(a.accumprem - a.waivedprem,0),
uwdate_check = a.uwdate,
a.class,
ISNULL(dd.[PERSON EFFECTIVE DATE],'1980-01-01') as Person_effective_date,
a.policy,
a.personstatus,
nflflag = case when a.rlob12 = '1f' then 'y' else 'n' end,
case
when b.sgtype is null then case substring(a.ServiceGroup,3,1)
when 'a' then 'A'
when 'i' then 'N'
else 'W' end
else b.sgtype
end as ServiceGroupIndic ,
Case
when year(a.survivoreligdate) = 9999 then null
else year(a.survivoreligdate) end as survivoreligYr,

rrShort = isnull(d.shortDescMap,a.shortdescription),
rrbioMap = case left(a.bioOptions,1)
when 'U'
then 'N'
else left(a.bioOptions,1)
end,
MoSesPlan = cast(case when a.SHORTDESCRIPTION = 'LTC2GRP' AND a.PLANCODE IN ('EA27','EA28','EA29','EB28','EB29','EB27') then 'LTC4'
when a.SHORTDESCRIPTION = 'INP' AND a.COMPANY = 3 then 'PROT'
when a.SHORTDESCRIPTION = 'FCPOOL' AND a.COMPANY = 3 AND n.Coverage = 'CCO' then 'PROT'
when a.SHORTDESCRIPTION = 'FCPOOL' AND a.COMPANY = 3 then 'PRO3'
when a.SHORTDESCRIPTION = 'FC2' AND a.PLANCODE LIKE 'N2%' then 'FC2G'
when a.SHORTDESCRIPTION = 'FC2' AND a.PLANCODE LIKE 'N1%' then 'FC2RG'
when a.SHORTDESCRIPTION = 'FC2' AND (a.PLANCODE LIKE 'F1%' OR a.PLANCODE LIKE 'H1%') then 'FC2R'
when a.SHORTDESCRIPTION = 'DG' AND a.PLANCODE IN ('N19HC999','N19NH999') then 'FC2RG'
when a.SHORTDESCRIPTION = 'COLTC' AND a.COMPANY = 8 then 'TCTQ'
when a.SHORTDESCRIPTION = 'COLTC' AND a.COMPANY = 3 then 'PRO2'
when a.SHORTDESCRIPTION = 'COLTC' AND a.COMPANY = 7 then 'LTC5'
when a.company = 6 then 'PC1'
else f.moseplan end as varchar(20)),

benefitCode = case
when a.company =4 and f.MoSePlan = 'ADVAN' and (n.Coverage = 'LTO' or n.Coverage = 'NHO') then 'L'
when a.Company = 8 and [ACForHHCPercentage]='050' and n.Coverage = 'HHO' then 'I'
when a.company = 6 then 'L'
when n.Coverage = 'HAL' then 'A'
when n.Coverage = 'CCO' then 'C'
when n.Coverage = 'FOO' then 'F'
when n.Coverage = 'HHO' and a.benefitpackage = 'HHO' and f.moseplan not in ('LACERA','PRO3','TCCOMP','TCTQ','LTC2') and (a.SHORTDESCRIPTION != 'FCPOOL' OR a.COMPANY != 3 OR n.Coverage = 'CCO') and (a.SHORTDESCRIPTION != 'COLTC' OR a.COMPANY != 8) then 'H'
when n.Coverage = 'HHO' and a.benefitpackage = 'HHO' then 'S'
when n.Coverage = 'HHO' then 'H'
when n.Coverage = 'NAL' and f.MoSePlan = 'UNIPROD' then 'A'
when n.Coverage = 'NAL' then 'K'
when n.Coverage = 'LTC' then 'L'
when n.Coverage = 'LTO' then 'N'
when n.Coverage = 'NHO' then 'N'
when n.Coverage = 'PLU' then 'P'
when n.Coverage = 'XNH' then 'Z'
when n.Coverage in ('ROP','NHR','HHR') then 'R'
else null end,

elimPeriod =case
when a.elimPeriod = 'NAV ' then '0030'
when a.elimperiod = '9999' then '0'
when a.ElimPeriod='-1' then convert(varchar(4), '0')
else a.ElimPeriod end,
trueEP = case when a.elimperiod = 'NAV' then '0030'
when a.elimPeriod like '%k' then (case
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits > 150
then '90'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 60
then '20'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 150
then '60'
end)
when f.MoSePlan = 'UNIPROD' and n.Coverage = 'HHO' and a.elimPeriod > 30
then 90
when f.MoSePlan = 'UNIPROD' and n.Coverage = 'HHO' and a.elimPeriod <= 30
then 0
when (f.MoSePlan = 'PROT' or (a.SHORTDESCRIPTION = 'FCPOOL' AND a.COMPANY = 3 AND n.Coverage = 'CCO')) and n.Coverage = 'CCO' and a.elimPeriod =100
then '90'
when f.MoSePlan in('LTC','LTC2') and n.Coverage = 'HHO' and a.elimPeriod in (60,90,100)
then '0'
when f.MoSePlan in('LTC','LTC2') and n.Coverage = 'HHO' and a.elimPeriod= 20
then '0'
when (f.MoSePlan='PRO3' or (a.SHORTDESCRIPTION = 'FCPOOL' AND a.COMPANY = 3 and n.Coverage <> 'CCO'))
and n.Coverage = 'HHO' and a.benefitperiod between 41 and 77
then '0'
when f.MoSePlan = 'LTC2' and n.Coverage = 'HHO' and a.elimPeriod = 30
then '0'
when f.MoSePlan = 'FC2' and (a.plancode not like 'N2%' and a.plancode not like 'N1%' and a.plancode not like 'F1%' and a.plancode not like 'H1%') and (a.company <> 3 or a.shortdescription <> 'INP') and (n.Coverage = 'HHO'or n.Coverage = 'CCO') and a.elimPeriod <> 20
then '20'
when (f.MoSePlan = 'FC2RG' or (a.shortdescription = 'FC2' and a.plancode like 'N1%') or (a.SHORTDESCRIPTION = 'DG' AND a.PLANCODE IN ('N19HC999','N19NH999'))) and (n.Coverage = 'HHO'or n.Coverage = 'CCO') and a.elimPeriod = 0
then '20'

when f.MoSePlan in('LTC','LTC2') and (n.Coverage = 'LTO'or n.Coverage = 'NHO' or n.Coverage = 'XNH') and a.elimPeriod in (60,90)
then '100'
when f.MoSePlan in('LTC','LTC2') and (n.Coverage = 'LTO'or n.Coverage = 'NHO' or n.Coverage = 'XNH') and a.elimPeriod = 20
then '30' ---only BC='N' no'Z'

when f.MoSePlan = 'TCCOMP' and a.elimPeriod = 20
then '30'

when (a.PLANCODE IN ('EA27','EA28','EA29','EB28','EB29','EB27') or f.MoSePlan='LTC4')
and n.Coverage = 'HHO' and a.elimPeriod = 0
then '10'
when (f.MoSePlan in ('PROT','TC2') or (a.SHORTDESCRIPTION = 'INP' AND a.COMPANY = 3)) and n.Coverage = 'HHO' and a.elimPeriod = 60 and a.benefitPeriod <= 17
then '10'
when a.elimPeriod not like '%k' and convert(int,a.elimPeriod) < 10 then 0
when a.elimPeriod not like '%k' then convert(int,a.elimPeriod)
else a.elimPeriod end,

truebp=case
when ((a.SHORTDESCRIPTION = 'FCPOOL' AND a.COMPANY = 3 and n.Coverage <> 'CCO')or f.MoSePlan='PRO3')
and n.Coverage = 'HHO' and a.benefitperiod between 42 and 53
then '2'
when f.MoSePlan= 'UNIPROD' and a.benefitperiod <= 17 and a.benefitAmount = 25000
then '6'
when f.MoSePlan = 'LTC2' and (a.SHORTDESCRIPTION <> 'LTC2GRP' or a.PLANCODE not IN ('EA27','EA28','EA29','EB28','EB29','EB27')) and n.Coverage = 'HHO' and a.benefitperiod >17
then '1'
when f.MoSePlan= 'LTC' and n.Coverage = 'HHO' and a.benefitperiod >17
then '1'
when a.company = 3 and e.paymentType <> '04' and (n.Coverage = 'LTO'or n.Coverage = 'NHO'or n.Coverage = 'PLU'or n.Coverage = 'NAL')
and a.benefitperiod <= 17
then '2'
when a.company = 3 and (a.benefitperiod between 30 and 41) then '2'
when a.company = 3 and (a.benefitperiod between 54 and 65) then '4'

when a.ElimPeriod like '%k'
then (case
when a.benefitperiod <= 17
then '1'
when a.benefitperiod <= 29
then '2'
when a.benefitperiod <= 41
then '3'
when a.benefitperiod <= 53
then '4'
when a.benefitperiod <= 65
then '5'
when a.benefitperiod <= 77
then '6'
when a.benefitperiod <= 89
then '7'
when a.benefitperiod <= 101
then '8'
when a.benefitperiod <= 113
then '9'
when a.benefitperiod <= 126
then '10'
else '99' end)
when a.elimPeriod not like '%k'
then(case
when a.benefitperiod <= 17
then '1'
when a.benefitperiod <= 29
then '2'
when a.benefitperiod <= 41
then '3'
when a.benefitperiod <= 53
then '4'
when a.benefitperiod <= 65
then '5'
when a.benefitperiod <= 77
then '6'
when a.benefitperiod <= 89
then '7'
when a.benefitperiod <= 101
then '8'
when a.benefitperiod <= 113
then '9'
when a.benefitperiod <= 126
then '10'
else '99' end)
end,

cctrueep = case
when a.elimPeriod = 'NAV ' then '0030'
when a.elimPeriod like '%k'
then (case
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 10
then '0'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 20
then '10'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 30
then '20'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 60
then '30'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 90
then '60'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits < 100
then '90'
when convert(integer,substring(a.elimPeriod,1,3))*1000.0/a.AltUnits > 100
then '150 '
else '100' end)
when a.elimPeriod not like '%k'
then (case when convert(int,a.elimPeriod) > 100
then '150'
else convert(int,a.elimPeriod)
end)
else convert(int,a.elimPeriod)
end,

cctruebp = case
when f.MoSePlan= 'UNIPROD' and a.benefitperiod <= 17 and a.benefitAmount = 25000
then '6'
when a.elimPeriod not like '%k'
then (case
when convert(int,a.benefitperiod/12) = 0
then '1'
when convert(int,a.benefitperiod/12) >=6 and convert(int,a.benefitperiod/12) < 10
then '6'
when convert(int,a.benefitperiod/12) >=10 and convert(int,a.benefitperiod/12) < 30
then '10'
when convert(int,a.benefitperiod/12) = 30
then '99'
else convert(int,a.benefitperiod/12)
end)
when a.elimPeriod like '%k'
then (case
when convert(int,a.benefitperiod/12) = 0
then '1'
when convert(int,a.benefitperiod/12) >=6 and convert(int,a.benefitperiod/12) < 10
then '6'
when convert(int,a.benefitperiod/12) >=10 and convert(int,a.benefitperiod/12) < 30
then '10'
when convert(int,a.benefitperiod/12) = 30
then '99'
else convert(int,a.benefitperiod/12)
end)
else convert(int,a.benefitperiod/12)
end,

rateclass = case when n.Coverage in ('ROP','NHR','HHR') then cast('Q' as varchar(5))
when a.shortdescription = 'UNIPROD' then uni.rateClass
when a.company = 4 and len(non7.[rt class]) = 2 then
case when a.UWAGE > 50 then substring(non7.[rt class],2,1)
else substring(non7.[rt class],1,1) end
when a.company = 7 then pfl.[rt class]
else non7.[rt class] end

into inforcebuild.dbo.tbl_inf_TAS_NEW_controls
FROM ltcpdbw.PolicyMasterFile.dbo.TBL_PMFW_DATA_POLICYMASTERFILE_ARCHIVE a WITH (NOLOCK)
INNER JOIN policmasterfile.dbo.BenefitCategoryDescription n WITH (NOLOCK)
ON a.BenefitCatSubstituted = n.BenefitCategory
left outer join inforcebuild.dbo.tbl_inf_UWErrorForTAS p
ON a.company = p.company
AND a.Certificate = p.policy
AND a.PersonSequence = p.personSequence
AND a.phase = p.phase
left outer JOIN policmasterfile.dbo.tbl_rlob_values q
on a.rlob12 = q.[rlob 1]
and a.rlob34 = q.[rlob 2]
Left outer join PolicMasterFile.dbo.NFD_ActVersion dd
on a.Policy=dd.[POLICY NUMBER]
and a.Company=dd.COMPANY
and a.PersonSequence=dd.[PERSON SEQUENCE]
left join policmasterfile.dbo.sgtype b WITH (NOLOCK)
on a.ServiceGroup = b.ServiceGroup
inner join ccengine.dbo.aegonpoolingfactorsmap bb
on bb.bpmo = a.BenefitPeriod
and bb.poolingflag = 'Y'
inner join policmasterfile.dbo.benefitMapping c
on a.benefitPackage = c.benefitPackage
inner join policmasterfile.dbo.shortDescMapping d
on a.company = d.company
and isnull(q.[Ind or Group Flag],'i') = d.indOrGroup
and a.shortDescription = d.shortDesc
left outer join ltcpdbw.policymasterfile.dbo.TBL_PMFW_DATA_LINEOFBUSINESS e
on a.Options = e.[optioncode]
and a.plancode = e.[plancode]
and valuationdate = '06/30/2011'
left outer join policmasterfile.dbo.RATE_CLASS_FOR_NON_comp7 non7
on a.company = non7.comp2
and a.shortDescription = non7.[SHRT DESC]
and a.MaritalStatus = non7.MARITALSTATUS
and a.Options = non7.[DISCOPT]
and a.company != 7
and non7.[DISC TYPE] = e.[DiscountType]
left outer join policmasterfile.dbo.pfl_rate_class pfl
on e.[DiscountType] = pfl.[Discount Type]
and e.[DiscountFactor] = pfl.[Discount Factor]
and a.MaritalStatus = pfl.MARITALSTATUS
and a.company = 7
left outer join policmasterfile.dbo.Uniprod_rate_class uni
on right(rtrim(a.Options),1) = uni.DiscCode
AND a.shortDescription = 'UNIPROD'
left outer join policmasterfile.dbo.moseplan_shortDescription f
on a.shortDescription = f.shortDescription
/*left outer join inforcebuild.dbo.tbl_inf_miniValTableForTAS h
ON a.company = h.company
And a.certificate = h.Cert
And a.phase =h. Phase
And a.gender =h.Sex
left outer join policmasterfile.dbo.valMethLookUpTable g ---WITH (NOLOCK)
on h.ValMeth =g.valMeth*/
WHERE '6/30/2011' between a.fromDate and a.thruDate


SELECT a.*,
h.valMeth,
h.GAAPBenefitReserve,
h.GAAPMaintenanceReserve,
h.GAAPAcquistionReserve,
h.TaxALRReserve,
h.StatALRReserve,
h.V_dac00CM,
h.V_dac00NCom,
h.Stat_Reserve_1207,
h.p_benres,
h.p_maintres,
h.P_dac00CM,
h.p_dac00NCom,
h.p_dacCM,
h.p_dfp,
h.UnearnedPremium,
h.V_dfp,
taxPT =case
when convert(int,e.[paymentType]) = 1 then 0
when g.taxpt = 'A' then 1
when g.taxpt = 'A' then (case
when a.shortDescription = 'UNIPROD'then 1 else 0 end)
else g.taxPT end,
statPT= case
when convert(int,e.[paymentType]) = 1 then 0
when g.STATpt = 'A' then 1
when g.taxPT = 'A' then (case
when a.shortDescription = 'UNIPROD' then 1 else 0 end)
else g.statpt end

into inforcebuild.dbo.tbl_inf_TAS_NEW
FROM inforcebuild.dbo.tbl_inf_TAS_NEW_controls a WITH (NOLOCK)
left outer join inforcebuild.dbo.tbl_inf_miniValTableForTAS h
ON a.company = h.company
And a.PolicyNumber = h.Cert
And a.PhaseNumber =h. Phase
And a.gender =h.Sex
left outer join policmasterfile.dbo.valMethLookUpTable g ---WITH (NOLOCK)
on h.ValMeth =g.valMeth
left outer join ltcpdbw.policymasterfile.dbo.TBL_PMFW_DATA_LINEOFBUSINESS e
on a.OptionCode = e.[optioncode]
and a.plancode = e.[plancode]
and valuationdate = '06/30/2011'
WHERE a.PolicyStatus IN ('1','3','4','6','7','8')
AND a.PhaseStatus IN ('1','3','4','6','7','8')
AND a.Class NOT IN ('M','R')
And a.Personcode in ('00','03','04')
and a.subphase = 0
AND @inforcedate >= a.uwdate_check


update inforcebuild.dbo.tbl_inf_TAS_NEW
set PolicyPoolingFlag =
(select case
when sum( case
when nhpoolingflag = 'Y'
then 1
else 0
end
) > 0
then 'Y'
else 'N'
end
from inforcebuild.dbo.tbl_inf_TAS_NEW B
where inforcebuild.dbo.tbl_inf_TAS_NEW.policyNumber = b.policyNumber
)
where exists (select 1 from inforcebuild.dbo.tbl_inf_TAS_NEW b
where inforcebuild.dbo.tbl_inf_TAS_NEW.policyNumber = b.policyNumber
)

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-09-22 : 17:40:58
You've posted an enormous stored procedure. I am not sure that we will be able to help you with such a beast. We offer free help, and this is looking like it would take quite a bit of time.

Have you viewed the execution plan to verify if there are scans, nested loops, or anything bad in there? Does SSMS indicate any missing indexes for this? How about the missing indexes report?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-23 : 02:25:32
"... this is looking like it would take quite a bit of time."

I found the "WITH (NOLOCK)" scary, no error checking, no SET NOCOUNT ON, and I glazed over a bit at that point
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-23 : 10:41:09
Thanks a lot Kristen and tkizer!

I will take a look at the indexes first and that might do it. but where can I see the the execution plan in SQL Server?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-23 : 11:22:53
If you use SSMS you can Right Click and ask select the Actual Query Plan (and that will include suggestions for any indexes that are missing)

If you are just using a raw SQL tool then:

-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON
BEGIN TRANSACTION

 ... put query here ...

ROLLBACK
SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO

and remove the comment from either the SHOWPLAN_TEXT ON or the SET STATISTICS IO depending on which you want to see.

it can be handy to see the plan in Text, as it is searchable, but its quite "dense" to read - so up to you which you feel more comfortable with. I fine the GUI plans need a lot of clicking and scrolling to find what I need ... but I have the familiarity of using them for many years of course.
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-23 : 12:06:43
Great appreciated for the quick response, Kristen. I will give it try... we will have a company picnic this afternoon.... Fun stuff, I will let you know the process next Monday, my teacher!! Have a wonderful Weekend!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-24 : 07:47:06
"company picnic"

Damn! Read this too late to come and join you, but thanks for the invite
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-26 : 12:02:52
what dose this statement mean... which table I should put in the field 'tempdb..#pmf'...

IF OBJECT_ID('tempdb..#pmf', 'U') IS NOT NULL
DROP TABLE #pmf ;
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-26 : 12:06:38
Dunno. Where did you see that ?
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-26 : 12:14:19

I did asked my IT team for query plan detail to look at the index missing, the guy give me some advice for my problem issue. see below. How do you think...

the two big table have those records below... 1st one has 2,270,442 rows, the 2nd one has 15.000,000 rows.

The main reason that made this stored procedure run very slow is you are scanning a 148-million-row table. I suggest that you break the big query into some smaller chunks like the examples below (see the highlighted queries). In addition, you don’t need to use linked server LTCPDBW, just use the tables in PolicyMasterFile in LTCPDB1. Anything running on linked server will be single thread…meaning very slow.


IF OBJECT_ID('tempdb..#pmf', 'U') IS NOT NULL
DROP TABLE #pmf ;
SELECT *
INTO #pmf
FROM PolicyMasterFile.dbo.TBL_PMFW_DATA_POLICYMASTERFILE_ARCHIVE WITH (NOLOCK)
WHERE '20110630' BETWEEN FromDate AND ThruDate ;


IF OBJECT_ID('tempdb..#lob', 'U') IS NOT NULL
DROP TABLE #lob ;
SELECT *
INTO #lob
FROM policymasterfile.dbo.TBL_PMFW_DATA_LINEOFBUSINESS WITH (NOLOCK)
WHERE valuationdate = '20110630' ;
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-26 : 12:26:38
Ah, understand it now.

You are being advised to make a query into temporary tables #pmf & #lob

The command:

IF OBJECT_ID('tempdb..#pmf', 'U') IS NOT NULL 
DROP TABLE #pmf ;

is just ensuring that if the temporary table already exists it should be dropped.

If this is in a stored procedure you won;t need to do that, because the "scope" will only be within the Sproc, so the table won't exist at the start, and will be automatically dropped at the end (unless this is running in a LOOP)

I suggest you try changing the LTCPDBW table to your local one first. That is likely to make a significant difference.

I'm not very keen on breaking down a complex query into portions that are run separately (the lack of isolation is likely to mean that rows may be excluded that should be subsequently included, or are included in the #TempTables but then not available when the actual query is run). But it would be worth trying to see what the impact is.
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-26 : 12:56:30
I agree with you, that's what I am doing and try to change LTCPDBW table to your local one first and see what will happen, actually, I created some temp tables, it already speed up a little bit... see what will happen this afternoon, Lunch time, Enjoy your lunch break, Kristen
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-26 : 16:54:52
Eventually, I found only this table --ltcpdbw.PolicyMasterFile.dbo.TBL_PMFW_DATA_POLICYMASTERFILE_ARCHIVE a (2,270,442 rows) caused the long running time (around 15 minutes when created a temp table only from this one). It will be almost same time if I changed ltcpdbw to local one

any advice on how to speed it up when use this table...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-26 : 18:01:39
"It will be almost same time if I changed ltcpdbw to local one"

Why? Your IT team seemed to think the local table would be much quicker, I think ?
Go to Top of Page

CoolMoon2011
Starting Member

31 Posts
Posted - 2011-09-27 : 10:37:58
Helllllo, Kristen. it is AMAZING when I run this procedure again this morning after changing ltcpdbw to local one,(surely it was an effective way to solve this problem). it only took around 6 minutes for the whole process. Totally different with yesterday. Another reason that caused this problem is that the database was very slow and crowdy a few days ago due to month end. In a word, you help me out and I have learned more about Database...
A happy day... THANK YOU Kristen....Have a happy day too...~~
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-27 : 12:55:36
Glad you got it fixed
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site