如何改进调用函数的存储过程？ [英] How to improve a stored procedure calling a function?
问题描述
我有一个在3个不同的存储过程中调用的函数，但我正在处理一个存储过程并尝试将其性能从3分钟改善到几秒钟内。
我可能错了，但我相信引起问题的问题是
1 ）该函数使用表变量和
2）它返回的信息比我正在处理的存储过程所需的信息多。我在我的sp中选择
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来仅选择我需要的字段，但由于该函数用于其他SP，我无法更改该函数。
我想修复它的方法是创建一个带临时表而不是表变量的新存储过程，并调用新的我正在工作的原始SP。我有一些问题让它工作，所以我想知道是否还有其他方法来改善存储过程的性能，这是调用可恢复功能？
我只是想学习不同的方法或选项来提高该程序的性能。
任何帮助或信息将非常有用和赞赏。谢谢。
我尝试了什么：
原始存储过程
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
)
此Select语句中的多个连接是否可以成为问题？
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
推荐答案
是的，函数是问题。这与我运行存储过程的时间相同。我在我的问题中添加了代码，其中有多个连接。我是SQL的新手，我不知道是否所有的连接都引起了这个问题，或者只是因为它获取了太多的信息。
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.
这篇关于如何改进调用函数的存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
