I have a function which is being called in 3 different Stored procedure, but I am working on one Stored procedure and trying to improve its performance from 3 minutes to like within seconds.
I could be wrong, but I believe the problems which are causing the issues are that
1) The function is using Table variables and
2)it is returning more information than I need for the Stored Procedure I am working on. I am doing
select distinct
in my sp to select only the fields I need, but since the function is used for other SPs, I cannot change the function.
The way I thought of fixing it is by creating a new stored procedure with temp tables instead of table variable, and calling the new SP in the original one I was working in. I am having some issues getting it to work, so I wanted to know if there are other ways to improve the performance of Stored procedure which is calling a resuable function?
I just want to learn different ways or options to improve the performance of that procedure.
Any help or information will be very much helpful and appreciated. Thank you.
What I have tried:
Original Stored Procedure
Select Distinct
osParticipantPK,
osEntityPK,
VendorTaxID,
VendorEntityID,
VendorFullName,
VendorFileNameAs,
IsActive,
LastPmtDate,
TotalAmountPaid,
TotalCommitmentToPayBalance,
MailingAddress
From
dbo.apfGetVendorComitments(@osCompanyModulePK,
@ReferenceDate,
@ParticipantStatus,
@ShowCmmToPayBalanceOnly,
@osParticipantPKs)
Order By VendorFullName
The Function's Return table variable returns all of this
Returns @apVendorsToReturn Table
(
tnHeaderPk Int Null,
tnSummaryTranPK Int Null,
tnSummaryTranlktnTranOriginal Int Null,
tnTranPK Int Null,
osParticipantPK Int Null,
osEntityPK Int Null,
VendorTaxID Varchar (15) Null,
VendorEntityID Varchar (20) Null,
VendorFullName Varchar (128) Null,
VendorFileNameAs Varchar (128) Null,
IsActive Varchar (3) Null,
InvoiceNumber Varchar (20) Null,
PurchaseOrderNumber Varchar (20) Null,
LastPmtDate DateTime Null,
TotalAmountPaid Numeric(19,4) Null,
CommitmentToPayBalance Numeric (19,4) Null,
TotalCommitmentToPayBalance Numeric (19,4) Null, -- (Total By Vendor)
DueDate DateTime Null,
tnTranDescription Varchar(50) Null,
IsAdjustment Varchar(3) Null,
tnHeaderfkosCompanyModule Int Null,
tnHeaderfkMLStnHeaderType Int Null,
tnHeaderOpenStatus Varchar(50) Null,
tnHeaderPostStatus Varchar(50) Null,
tnHeaderPaymentStatus Varchar(50) Null,
tnTranStatus Varchar(50) Null,
CommitmentStatus Varchar(50) Null,
apTranIsMinorityOwned Varchar (3) Null,
apTranIsWomanOwned Varchar (3) Null,
MailingAddress Varchar(255) Null,
-- Commitments Header (CH) :
-- 5 more fields
)
Could the multiple joins in this Select statement be the issue?
tnHeader.pk,
tnSummaryTran.pk As tnSummaryTranPK,
tnSummaryTran.lktnTranOriginal As tnSummaryTranlktnTranOriginal,
tnTran.pk As tnTranPK,
osParticipant.PK As osParticipantPK,
osEntity.PK As osEntityPK,
VendorTaxID =
Case
When @UseDisguisedTaxID = 'Yes' Then osEntity.DisguisedTaxID
Else osEntity.TaxID
End,
osEntity.EntityID As VendorEntityID,
osEntity.FullName As VendorFullName,
osEntity.FileNameAs As VendorFileNameAs,
IsNull(osParticipant.IsActive, 'No') As IsActive,
......... many other fields.....
MailingAddress = Null, -- Populate later using function
From
tnSummaryTran WITH (NOLOCK)
Join tnTran WITH (NOLOCK) on tnTran.pk = tnSummaryTran.lktnTranOriginal
Join tnHeader WITH (NOLOCK) on tnHeader.pk = tnSummaryTran.fktnHeader
Join apHeader WITH (NOLOCK) on apHeader.fktnHeader = tnHeader.pk
Join apTran WITH (NOLOCK) on apTran.fktnTran = tnTran.pk
Join osControlNumberStatus osControlNumbertnHeaderOpenStatus WITH (NOLOCK) on osControlNumbertnHeaderOpenStatus.pk = tnHeader.fkosControlNumberOpenStatus
Join osControlNumberStatus osControlNumberOpenStatus WITH (NOLOCK) on osControlNumberOpenStatus.pk = tnTran.fkosControlNumberStatus
Join tnSummaryTran tnSummaryTranOriginal WITH (NOLOCK) on tnSummaryTranOriginal.pk = dbo.tnfInitialSummaryTranPK(tnSummaryTran.pk)
Join tnHeader tnHeaderReference WITH (NOLOCK) on tnHeaderReference.pk = tnSummarytranOriginal.fktnHeader
Join osParticipant WITH (NOLOCK) on osParticipant.pk = tnHeader.fkosParticipant
Join apVendor WITH (NOLOCK) on apVendor.fkosparticipant = osparticipant.pk
Join osEntity WITH (NOLOCK) on osEntity.pk = osParticipant.fkosEntity
Left Join osControlNumberStatus osControlNumbertnHeaderPostStatus WITH (NOLOCK) on osControlNumbertnHeaderPostStatus.pk = tnHeader.fkosControlNumberPostStatus
Left Join osControlNumberStatus osControlNumbertnHeaderPaymentStatus WITH (NOLOCK) on osControlNumbertnHeaderPaymentStatus.pk = tnHeader.fkosControlNumberPaymentStatus
-- Get Commitment Header (Type 9):
Left Join apHeader apHeaderCommHeader WITH (NOLOCK) on apHeaderCommHeader.fktnHeader = tnHeader.lktnHeaderReference
Left Join tnHeader tnHeaderCommHeader WITH (NOLOCK) on tnHeaderCommHeader.pk = tnHeader.lktnHeaderReference
Where
tnSummaryTran.fktnHeader Is Not Null