Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 15-May-17 21:00pm
v2
Comments
RossMW 15-May-17 19:27pm    
Unless there is more to the stored procedure than you've shown then I would guess the speed is related to the function and not the stored procedure.

Have you try running the function on its own. This is probably where the speed issue is.
[no name] 15-May-17 20:12pm    
Yes, the function is the issue. It is taking the same time as when I run the Stored Procedure. I have added the code in my question where there are multiple joins. I am new to SQL, I don't know if all the joins are causing the issue or is it just because it is getting too much of information.
RossMW 15-May-17 23:32pm    
Possibly. Peformance issues could be a number of things which would be impossible for us to determine without seing the structure and details of the tables.

Some of the posible areas are
- tables sizes
- key fields correctly indexed
- field type used for the joins
- Hardware etc

You will need to break it down and do testing, profiling to determine where the real issue is.

Maciej Los 16-May-17 2:49am    
Sounds like an answer to me.
RossMW 16-May-17 2:59am    
Never too sure on these vague issues. On your advice I'll post as the answer

1 solution

Truecolors wrote:

Yes, the function is the issue. It is taking the same time as when I run the Stored Procedure. I have added the code in my question where there are multiple joins. I am new to SQL, I don't know if all the joins are causing the issue or is it just because it is getting too much of information.


Possibly. Peformance issues could be a number of things which would be impossible for us to determine without seing the structure and details of the tables.

Some of the posible areas are
- tables sizes
- key fields correctly indexed
- field type used for the joins
- Hardware etc

You will need to break it down and do testing, profiling to determine where the real issue is.
 
Share this answer
 
v2
Comments
Maciej Los 16-May-17 3:38am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900