May 21, 2008 at 1:39 am
The store procedure write about 6 million records in a table and is taking alomst 36 min to process. Please advise me if there is any way where i can reduce the time consumed marginally. The sp i wrote is as follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE spSOA_DetailBah
(
@strCustID varchar(15)
,@strCustIDTo varchar(15)
,@dtmBegPeriod datetime
,@dtmEndPeriod datetime
,@bitIncludeComm bit
,@strUserid varchar(20)
,@strApp_Type char(1) = 'I'
,@bitCreateBegBal bit
)
AS
DECLARE @dtmPrevDate datetime
, @CODE_SURVEY varchar
,@CODE_SETTLE varchar
,@CODE_RECOVRY varchar
,@TYPE_TRANSFER varchar
,@CANCEL_OR varchar
,@CANCEL_CPV varchar
,@OFFSET varchar
,@WRITEOFF varchar
,@ADJUSTMENT varchar
,@NONCASH varchar
,@strPayee varchar(15)
,@ErrMsg varchar(100)
,@dtmDateProc datetime
DECLARE @VB6_UW char(1)
,@VB6_ENDOR char(1)
,@VB6_COMM char(1)
,@VB6_RECOV char(1)
,@VB6_SETT char(1)
,@VB6_SUR char(1)
,@ARAP_UW_ENDOR char(1)
,@ARAP_CLAIM char(1)
,@ARAP_COMM char(1)
DECLARE @RPT_SOA bit
,@RPT_AGING bit
SELECT @RPT_SOA = 1
,@RPT_AGING = 2
SELECT @CODE_SETTLE = '6'
,@TYPE_TRANSFER = '0'
,@CANCEL_OR = '3'
,@CANCEL_CPV = '1'
,@OFFSET = '4'
,@TYPE_TRANSFER = '0'
,@WRITEOFF = '5'
,@ADJUSTMENT = '2'
,@NONCASH = '6'
--debit/credit note type coding between VB6 and ARAP
SELECT @VB6_UW = '1' --underwriting VB6
,@VB6_ENDOR = '8' --endorsement VB6
,@VB6_COMM = '3' --commission VB6
,@VB6_RECOV = '5' --recovery VB6
,@VB6_SETT = '6' --settlement VB6
,@VB6_SUR = '7' --survey VB6
,@ARAP_UW_ENDOR = '1' --underwriting/endorsement ARAP
,@ARAP_CLAIM = '6' --survery/recovery/settlement ARAP
,@ARAP_COMM = '7' --commission ARAP
SET @dtmPrevDate = @dtmBegPeriod - 1
IF LTRIM(ISNULL(@strCustID,'')) = ''
SET @strCustID = NULL
IF LTRIM(ISNULL(@strCustIDTo,'')) = ''
SET @strCustIDTo = @strCustID
--
-- After processing, the reporting module reads the output file then zap it. Sometimes, data might not be deleted due to
-- unknown system error. This procedure will check the date and the time difference since its creation.
-- If the data is more than at least 5 minutes old, it means the output file was not purge since the last processing,
-- delete the data before processing again
--
Select Top 1 @dtmDateProc = dateproc From SOA_DetailBah
IF datediff(mi,@dtmDateProc,getdate()) >= 5 --time limit in minutes
Truncate Table SOA_DetailBah
Else
IF @dtmDateProc Is Not Null
BEGIN
SELECT @ErrMsg = 'Data is currently being process by another user. Please try again after few seconds. '
GOTO ENDERROR
END
INSERT INTO SOA_DetailBah(
Payee
,insuredid
,polnum
,docdate
,doctype
, refdoc
,docno
,descr
,amt
,comm
,or_pv_jv_amt
,ClmInvNo
,NetBal
,userid
,DnCn_rec
,dateproc
)
SELECT RefDocCustId
,insuredid
,polnum
,docdate
,doctype
,refdoc
,docno
,descr
,Amt
,Comm
,OR_PV_JV_amt
,ClmInvNo
,NetBal = amt+comm+OR_PV_JV_amt
,userid= @strUserid
,DnCn_rec
,getdate()
FROM
(
SELECT RefDocCustId = dbo.ARAP.ARP_BillTo_Payee
,insuredid = dbo.ARAP.ARP_Insured
,polnum = dbo.ARAP.ARP_Polnum
,docdate = dbo.ARAP.arp_notedate
,doctype = 'DC'
,RefDoc = dbo.ARAP.arp_num
,docno = CASE WHEN ISNULL(dbo.ARAP.ARP_ClmNum,'') <> '' AND dbo.ARAP.ARP_ClmNum <> '0'
THEN dbo.ARAP.ARP_ClmNum
ELSE
CASE WHEN ISNULL(dbo.ARAP.ARP_RefNum,'') <> '' AND dbo.ARAP.ARP_RefNum <> '0'
THEN dbo.ARAP.ARP_RefNum
ELSE dbo.ARAP.ARP_Polnum
END
END
,descr = ISNULL( dbo.ARAP.ARP_Particulars,'')
,Amt = CASE WHEN SUBSTRING(dbo.ARAP.ARP_Num,3,1) <> @CODE_SETTLE
THEN ISNULL(dbo.ARAP.ARP_DB_AMOUNT, 0) - ISNULL(dbo.ARAP.ARP_CR_AMOUNT, 0)
ELSE 0 END
,Comm = CASE WHEN @bitIncludeComm = 1 THEN ISNULL(dbo.ARAP.ARP_DB_COMM,0) - ISNULL(dbo.ARAP.ARP_CR_COMM,0) ELSE 0 END
,OR_PV_JV_amt = CASE WHEN SUBSTRING(dbo.ARAP.ARP_Num,3,1) = @CODE_SETTLE THEN ISNULL(dbo.ARAP.ARP_DB_AMOUNT, 0) - ISNULL(dbo.ARAP.ARP_CR_AMOUNT, 0) ELSE 0 END
,ClmInvNo = dbo.ARAP.arp_clminvno
,DnCn_rec = 'Y'
FROM dbo.ARAP
WHERE ((dbo.ARAP.ARP_BillTo_Payee BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
AND Convert(char(10),dbo.ARAP.ARP_NoteDate,102) <= Convert(char(10), @dtmEndPeriod,102)
UNION ALL
------------------------------------------detailed OR
SELECT RefDocCustId = dbo.ORDetail.OrDet_AccntBillId --or_accntbillid
,insuredid = Null
,polnum = Null
, or_date --Debit/credit note date should show instead of the OR date.
,doctype = 'OR'
,RefDoc= CASE WHEN ISNULL(dbo.ORDetail.Ordet_NoteNo,'') = '' THEN dbo.ORDetail.Ordet_refnum ELSE
CASE WHEN Substring( dbo.ORDetail.Ordet_NoteNo,3,1) IN ('5','7') AND ISNULL(dbo.ORDetail.ordet_cr_appliedcomm,0) + ISNULL(dbo.ORDetail.ordet_db_appliedcomm,0) = 0
THEN Left(dbo.ORDetail.Ordet_NoteNo,2) + '6' + Substring(dbo.ORDetail.Ordet_NoteNo,4,11)
WHEN Substring( dbo.ORDetail.Ordet_NoteNo,3,1) = '8'
THEN Left(dbo.ORDetail.Ordet_NoteNo,2) + '1' + Substring(dbo.ORDetail.Ordet_NoteNo,4,11)
ELSE dbo.ORDetail.Ordet_NoteNo END
END
,docno = ltrim(str(dbo.ORMaster.OR_FormNo)) -- 'OR#' + ltrim(str(OR_FormNo))
,descr = dbo.ORMaster.OR_Remarks
,Amt = 0
,Comm = CASE WHEN @bitIncludeComm = 1 THEN ISNULL(dbo.ORDetail.ORDet_CR_AppliedComm,0) - ISNULL(dbo.ORDetail.ORDet_DB_AppliedComm, 0) ELSE 0 END
,OR_PV_JV_amt = ISNULL(dbo.ORDetail.ORDet_CR_AppliedAmt,0) - ISNULL(dbo.ORDetail.ORDet_DB_AppliedAmt, 0) -- ,OR_PV_JV_amt = SUM( ISNULL(ORDet_CR_AppliedAmt,0) - ISNULL(ORDet_DB_AppliedAmt, 0) )
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.ORDetail
LEFT JOIN dbo.ORMaster ON dbo.ORMaster.OR_Num = ORDet_Num
WHERE ((dbo.ORDetail.Ordet_AccntBillId BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
AND Convert(char(10), dbo.ORMaster.OR_Date, 102) <= Convert(char(10),@dtmEndPeriod,102)
AND dbo.ORMaster.OR_Status = 'A'
AND ISNULL(dbo.ORMaster.or_creditrev,0) = 1
UNION ALL
--------------------------OR payments on accounts / partially allocated
SELECT RefDocCustId = dbo.ORMaster.Or_AccntBillId --OR_CustId
,insuredid = null
,polnum = null
,or_date
,doctype = 'OR'
,RefDoc = ltrim( str(dbo.ORMaster.OR_Formno))
,docno = 'On Account'
,descr = dbo.ORMaster.OR_Remarks
,Amt = 0
,Comm = 0
,OR_PV_JV_amt = ( ISNULL(dbo.ORMaster.OR_amountpaid,0) - ISNULL(dbo.ORMaster.OR_Amountapplied,0) ) * -1
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.ORMaster
WHERE (( dbo.ORMaster.Or_AccntBillId BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
AND Convert(char(10), dbo.ORMaster.OR_Date, 102) <= Convert(char(10), @dtmEndPeriod, 102)
AND dbo.ORMaster.OR_Status = 'A'
AND ISNULL(dbo.ORMaster.or_creditrev,0) = 1
AND ISNULL(dbo.ORMaster.OR_Amountpaid,0) - ISNULL(dbo.ORMaster.OR_Amountapplied,0) <> 0
UNION ALL
---------PV detailed payments
SELECT RefDocCustId = PD_Payee
,insuredid = null
,polnum = null
, pm_date
,doctype = 'CP' --cheque payment
,refdoc = dbo.PAYDETAIL.pd_notenum
,docno = dbo.PAYMASTER.PM_pvnum
,descr = dbo.PAYMASTER.PM_Remarks
,Amt = 0
,COMM = CASE WHEN @bitIncludeComm = 1 THEN ISNULL(dbo.PAYDETAIL.PD_CR_AppliedComm,0) - ISNULL(dbo.PAYDETAIL.PD_DB_AppliedComm, 0) ELSE 0 END
,OR_PV_JV_amt = ISNULL(dbo.PAYDETAIL.PD_CR_AppliedAmt,0) - ISNULL(dbo.PAYDETAIL.PD_DB_AppliedAmt, 0)
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.PAYDETAIL
LEFT JOIN dbo.PAYMASTER ON dbo.PAYMASTER.PM_PVNum = dbo.PAYDETAIL.PD_PVNum
WHERE ((dbo.PAYDETAIL.PD_Payee BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
AND Convert(char(10), PM_Date, 102) <= Convert(char(10),@dtmEndPeriod, 102)
AND dbo.PAYMASTER.PM_Status = 'A'
UNION ALL
--PV payments on account
SELECT RefDocCustId =dbo.PAYMASTER.PM_AccountOf
,insuredid = null
,polnum = null
,pm_date
,doctype = 'CP'
,refdoc = dbo.PAYMASTER.PM_PVNum
,docno ='On Account'
,descr = dbo.PAYMASTER.PM_Remarks
,Amt = 0
,COMM = CASE WHEN @bitIncludeComm = 1 THEN ISNULL(dbo.PAYMASTER.PM_CommPaid,0) - ISNULL(dbo.PAYMASTER.pm_commapplied,0) ELSE 0 END
,OR_PV_JV_amt = ISNULL(dbo.PAYMASTER.PM_AmountPaid,0) - ISNULL(dbo.PAYMASTER.pm_amountapplied,0)
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.PAYMASTER
WHERE ((dbo.PAYMASTER.PM_AccountOf BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
AND Convert(char(10), PM_Date, 102) <= Convert(char(10),@dtmEndPeriod,102)
AND PM_Status = 'A' AND (ISNULL(dbo.PAYMASTER.PM_AmountPaid,0) - ISNULL(dbo.PAYMASTER.pm_amountapplied,0) <> 0 OR ISNULL(dbo.PAYMASTER.PM_CommPaid,0) - ISNULL(dbo.PAYMASTER.pm_commapplied,0) <> 0)
UNION ALL
---------------------JV without allocation/partially allocated------------------------
SELECT RefDocCustId = dbo.JVDetail.JD_AccountOf
,insuredid = null
,polnum = null
,jv_date
,doctype = 'JV'
,RefDoc= dbo.JVMaster.JV_FormNo
,docno ='On Account'
,descr = dbo.JVDetail.JD_Remarks
,Amt = 0
,Comm = CASE WHEN ISNULL(appliedcomm,0) > 0 and @bitIncludeComm = 1 THEN
CASE WHEN Jv_type = @TYPE_TRANSFER
THEN
ISNULL( dbo.JVDetail.JD_Cr_Amt, 0) - ISNULL( dbo.JVDetail.JD_Db_amt, 0) --+ AppliedAmt_Transfer
ELSE
CASE WHEN ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0) > 0
THEN (ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0)) - ISNULL(appliedamt,0)
ELSE (ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0)) + ISNULL(appliedamt,0)
END
END
ELSE 0
END
,OR_PV_JV_amt = CASE WHEN dbo.JVMaster.Jv_type = @TYPE_TRANSFER THEN
ISNULL( dbo.JVDetail.JD_Cr_Amt, 0) - ISNULL( dbo.JVDetail.JD_Db_amt, 0) --+ AppliedComm_Transfer
WHEN dbo.JVMaster.JV_Type = @NONCASH THEN
(ISNULL(AppliedDBAmt,0) + ISNULL(AppliedDBComm,0) - ISNULL( dbo.JVDetail.JD_Cr_amt, 0) ) -
( (ISNULL(AppliedCRAmt,0) + ISNULL(AppliedCRComm,0)) - ISNULL( dbo.JVDetail.JD_Db_amt, 0) )
WHEN dbo.JVMaster.JV_Type = @WRITEOFF THEN
((ISNULL(AppliedDBAmt,0) + ISNULL(AppliedDBComm,0)) - ISNULL( dbo.JVDetail.JD_Db_amt, 0)) +
( ISNULL( dbo.JVDetail.JD_CR_amt, 0) - (ISNULL(AppliedCRAmt,0) + ISNULL(AppliedCRComm,0)))
WHEN dbo.JVMaster.JV_Type = @CANCEL_OR OR JV_Type = @CANCEL_CPV THEN
( ISNULL( dbo.JVDetail.JD_Db_amt, 0) - (ISNULL(AppliedDBAmt,0) + ISNULL(AppliedDBComm,0)) ) +
( (ISNULL(AppliedCRAmt,0) + ISNULL(AppliedCRComm,0)) - ISNULL( dbo.JVDetail.JD_CR_amt, 0) )
ELSE
CASE WHEN ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0) > 0
THEN (ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0)) - ISNULL(appliedcomm,0)
ELSE (ISNULL( dbo.JVDetail.JD_Db_amt, 0) - ISNULL( dbo.JVDetail.JD_Cr_Amt, 0)) + ISNULL(appliedcomm,0)
END
END
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.JVDetail
LEFT JOIN dbo.JVMaster ON dbo.JVDetail.JD_Num = dbo.JVMaster.JV_Num
LEFT JOIN (
SELECT Jsd_Num, JSD_itemno
,AppliedAmt = SUM( ISNULL(Jsd_db_appliedamt, 0) - ISNULL( jsd_cr_appliedamt, 0) )
,AppliedComm = SUM( ISNULL(Jsd_db_appliedcomm,0) - ISNULL( jsd_cr_appliedcomm, 0) )
,AppliedCRAmt = SUM( ISNULL( jsd_cr_appliedamt, 0) )
,AppliedCRComm = SUM( ISNULL( jsd_cr_appliedcomm, 0) )
,AppliedDBAmt = SUM( ISNULL( jsd_db_appliedamt, 0) )
,AppliedDBComm = SUM( ISNULL( jsd_db_appliedcomm, 0) )
FROM dbo.JVSubdetail
GROUP BY jsd_num, jsd_itemno
)SubDet ON dbo.JVDetail.JD_num = JSD_Num AND dbo.JVDetail.JD_ItemNo = JSD_ItemNo
WHERE ISNULL(dbo.JVMaster.JV_status,'') = 'A' AND ISNULL(JSD_Num,'') = '' --AND JV_DetailClosed = 1
AND Convert(char(10), dbo.JVMaster.JV_Date, 102) <= Convert(char(10), @dtmEndPeriod, 102)
AND ( ISNULL( dbo.JVDetail.JD_Db_amt, 0) + ISNULL( dbo.JVDetail.JD_Cr_Amt, 0) - ( ISNULL(AppliedDBAmt,0) + ISNULL(AppliedDBComm,0) +
ISNULL(AppliedCRAmt,0) + ISNULL(AppliedCRComm,0) ) ) <> 0
AND ( (dbo.JVDetail.JD_AccountOf BETWEEN @strCustID AND @strCustIDto) OR ISNULL(@strCustID,'') = '' OR ISNULL(@strCustIDto,'') = '')
UNION ALL
---------------------JV with allocation------------------------
SELECT RefDocCustId = JSD_Payee
,insuredid = null
,polnum = null
,jv_date
,doctype = 'JV'
,RefDoc = case when rtrim( isnull(jsd_noteno,'')) = '' then jsd_refnbr else JSD_NoteNo end
,docno = dbo.JVMaster.JV_FormNo
,descr = dbo.JVDetail.JD_Remarks
,Amt = 0 ,COMM = CASE WHEN dbo.JVMaster.JV_Type = @CANCEL_OR OR dbo.JVMaster.JV_Type = @CANCEL_CPV OR dbo.JVMaster.JV_Type = @OFFSET OR dbo.JVMaster.JV_Type = @ADJUSTMENT
THEN CASE WHEN @bitIncludeComm = 1 THEN ISNULL( JSD_Db_AppliedComm, 0) - ISNULL( JSD_Cr_AppliedComm, 0) ELSE 0 END
ELSE CASE WHEN @bitIncludeComm = 1 THEN ISNULL( JSD_Cr_AppliedComm, 0) - ISNULL( JSD_Db_AppliedComm, 0) ELSE 0 END
END
,OR_PV_JV_amt = CASE WHEN convert(char(10),dbo.JVMaster.JV_DATE,102) <= '2005.01.31' AND dbo.JVMaster.JV_Type = 2 --default migration is Adjustment JV, filter date is for migrated data up to 31-Jan-05
THEN
ISNULL( JSD_Db_Appliedamt, 0) + ISNULL( JSD_Cr_Appliedamt, 0)
ELSE
CASE WHEN dbo.JVMaster.JV_Type = @CANCEL_OR OR dbo.JVMaster.JV_Type = @CANCEL_CPV OR dbo.JVMaster.JV_Type = @OFFSET
THEN ISNULL( JSD_Db_Appliedamt, 0) - ISNULL( JSD_Cr_Appliedamt, 0)
ELSE ISNULL( JSD_Cr_Appliedamt, 0) - ISNULL( JSD_Db_Appliedamt, 0)
END
END
,ClmInvNo = Null
,DnCn_rec = 'N'
FROM dbo.JVSubdetail
LEFT JOIN dbo.JVMaster ON JSD_Num = dbo.JVMaster.JV_Num
LEFT JOIN dbo.JVDetail ON dbo.JVDetail.JD_num = JSD_Num AND jsd_itemno = dbo.JVDetail.jd_itemno
WHERE ISNULL(dbo.JVMaster.JV_status,'') = 'A' AND ISNULL(JSD_Num,'') <> '' --AND JV_DetailClosed = 1
--do not show Offset JV type since its balance = 0
--AND JV_Type <> @OFFSET
AND Convert(char(10), dbo.JVMaster.JV_Date, 102) <= Convert(char(10), @dtmEndPeriod, 102)
AND ((JSD_Payee BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
) A
--Update partially allocated dn/cn with their proper dn/cn date and not the date of the documents that allocated it.
Update soa_detailbah set docdate = coalesce(docdate,arp_notedate) from arap
-- Update soa_detailbah set docdate = coalesce(arp_notedate,docdate) from arap
where userid = @strUserid
and (isnull(arp_db_amount,0) + isnull(arp_db_comm,0) - isnull(arp_db_appliedamt,0) - isnull(arp_db_appliedcomm,0)) +
(isnull(arp_cr_amount,0) + isnull(arp_cr_comm,0) - isnull(arp_cr_appliedamt,0) - isnull(arp_cr_appliedcomm,0)) <> 0
and arp_num = case when left(refdoc,2) = 'MO' and ( substring(refdoc,3,1) = @VB6_COMM or comm = 0 ) then
left( refdoc, 2) + case substring(refdoc ,3 ,1)
when @VB6_UW then @ARAP_UW_ENDOR
when @VB6_ENDOR then @ARAP_UW_ENDOR
when @VB6_COMM then @ARAP_COMM
when @VB6_RECOV then @ARAP_CLAIM
when @VB6_SETT then @ARAP_CLAIM
when @VB6_SUR then @ARAP_CLAIM
when @ARAP_UW_ENDOR then @ARAP_UW_ENDOR
else substring(refdoc ,3 ,1) end
+ substring(refdoc,4,11)
else refdoc
end
-- delete items where value is zero as per Mr. Mufiz; done on 26 Sept 06 by Edgar
Delete from soa_detailbah where (Amt+Comm+OR_PV_JV_amt)=0
IF @bitCreateBegBal = 1
BEGIN
--Add a beginning balance record by summing all records before the report date
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO SOA_DetailBah
(
Payee
,Amt
,Comm
,OR_PV_JV_amt
,NetBal
,UserId
)
SELECT Payee
,Amt=SUM(Amt)
,Comm=SUM(Comm)
,OR_PV_JV_amt=SUM(OR_PV_JV_amt)
,Netbal = SUM(amt + comm + OR_PV_JV_amt)
,userid
FROM SOA_DetailBah WHERE Convert(char(10),docdate,102) < Convert(char(10),@dtmBegPeriod,102) AND userid = @strUserid
GROUP BY Payee,userid
--set the record added (which is the summed amount) as beginning balance record
UPDATE SOA_DetailBah SET docno= 'BEG.BALANCE'
WHERE ISNULL(refdoc,'') = '' AND userid = @strUserid
DELETE FROM SOA_DetailBah WHERE docdate <@dtmBegPeriod and
ISNULL(docno,'') <> 'BEG.BALANCE' AND userid = @strUserid
END
GOTO END_PROC
ENDERROR:
BEGIN
RAISERROR 25000 @ErrMsg
RETURN 1
END
END_PROC:
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
May 21, 2008 at 4:09 am
A lot of UNION ALL going on!
1) Insert all UNION ALL queries into a temp table
2) Index properly
3) Use rest of query as is, but referenec to temp table instead
N 56°04'39.16"
E 12°55'05.25"
May 21, 2008 at 5:06 am
May 21, 2008 at 6:09 am
Have you looked at the execution plan? You need to identify where the bottlenecks are occuring in order to know where to go to work. The others have pointed out some useful stuff. There is an awful lot of UNION ALL going on here and functions in the WHERE clause can certainly prevent indexes from being used. As attractive as it is and as easy as it makes maintenance, you might want to test whether losing the variables within the code and simply using the hard-coded values doesn't make it perform better. The hard coded values are easier for the optimizer to work with. You're doing UPDATES and DELETES to the data after you insert it. Why not just build those into the SELECT criteria? That will reduce the work done because it reduces the number of times the data has to be "touched" by the query. You're using '<>' and '<=' and other stuff like this alot in the WHERE logic. These all count as OR statements which generally requires SCANS to resove. You've got code like this in several places:
WHERE ((dbo.PAYMASTER.PM_AccountOf BETWEEN @strCustID AND @strCustIDTo) OR @strCustID IS NULL OR @strCustIDTo IS NULL)
Since you can control what's in the variables @strCustId & SstrCustIdTo, why not set it these to a value and go with positive AND statemenets and not have to cover the IS NULL value queries.
Those are just a few items, but I'd start by simply breaking down the query and looking at the execution plans to identify the places that need to be tuned.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2008 at 6:17 am
I tried running a portion of this sp just fetching for Arap Table records with out any Union and it took 4 min 20s for fetching 336,212 records which is still unacceptable i hope
May 21, 2008 at 6:23 am
Check the execution plan. I'll be you're seeing tons of table or index scans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2008 at 7:53 am
Quite a lengthy stored proc this one...
:blink:
Agreed:
- check the access plan, select statement by select statement and eliminate those table scans
- Creating a temp table is really not a bad idea. Then instead of a union all, insert into ... select from... will do the same
Maybe combining the SQL statement into a singular one will spare you a lot of reads
- Between is one of the most cursed keywords out there when dealing with performance.
- Delete from ... is going to kill your performance if its on millions of rows, so adding them when doing the select statements would be far wiser
~PD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply