存储过程调用函数 数据库跑的很慢 怎么解决
0
悬赏园豆：20
[待解决问题]
USE [e_npl]
GO
/****** Object: StoredProcedure [dbo].[sqlNplRunData_GetBadLoanUserInfo] Script Date: 04/15/2015 10:32:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sqlNplRunData_GetBadLoanUserInfo]
(
@txndate datetime
)
as
begin
select distinct CONTRACT_NUMBER,
dbo.fnNplConvertColToRowsByCode(@txndate, CONTRACT_NUMBER) CREDITOR_CODE,
dbo.fnNplConvertColToRowsByName(@txndate, CONTRACT_NUMBER) CREDITOR_Name,
dbo.fnNplConvertColToRowsByCapital(@txndate, CONTRACT_NUMBER) CAPITAL_PROPORTION,
dbo.fnNplConvertColToRowsByInterest(@txndate, CONTRACT_NUMBER) INTEREST_PROPORTION
into #temp
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
end
-----------------------------------------------------------------------
ALTER function [dbo].[fnNplConvertColToRowsByCode]
(
@txndate datetime,
@col1Val varchar(20) -- 返回结果集的主键列
)
returns varchar(8000)
as
begin
declare @retVal varchar(8000)
set @retVal = ''
select @retVal = CREDITOR_CODE + ', ' + @retVal
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
and CONTRACT_NUMBER = @col1Val
if len(@retVal) > 0
set @retVal = left(@retVal, len(@retVal) - 1)
return @retVal
end
------------------------------------------------------------------------------
ALTER function [dbo].[fnNplConvertColToRowsByName]
(
@txndate datetime,
@col1Val varchar(20) -- 返回结果集的主键列
)
returns varchar(8000)
as
begin
declare @retVal varchar(8000)
set @retVal = ''
select @retVal = isnull(dbo.fnNPL_GetUserIdToUserName(CREDITOR_CODE, ','), CREDITOR_CODE) + ', ' + @retVal
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
and CONTRACT_NUMBER = @col1Val
if len(@retVal) > 0
set @retVal = left(@retVal, len(@retVal) - 1)
return @retVal
end
----------------------------------------------------------------------------
ALTER function [dbo].[fnNplConvertColToRowsByCapital]
(
@txndate datetime,
@col1Val varchar(20) -- 返回结果集的主键列
)
returns varchar(8000)
as
begin
declare @retVal varchar(8000)
set @retVal = ''
select @retVal = cast(CAPITAL_PROPORTION as varchar(20)) + '%, ' + @retVal
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
and CONTRACT_NUMBER = @col1Val
if len(@retVal) > 0
set @retVal = left(@retVal, len(@retVal) - 1)
return @retVal
end
--------------------------------------------------------------------------
ALTER function [dbo].[fnNplConvertColToRowsByInterest]
(
@txndate datetime,
@col1Val varchar(20) -- 返回结果集的主键列
)
returns varchar(8000)
as
begin
declare @retVal varchar(8000)
set @retVal = ''
select @retVal = cast(INTEREST_PROPORTION as varchar(20)) + '%, ' + @retVal
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
and CONTRACT_NUMBER = @col1Val
if len(@retVal) > 0
set @retVal = left(@retVal, len(@retVal) - 1)
return @retVal
end
所有回答(5)
0
一步一步分析，慢在哪儿，然后针对性解决。
0
关键找到慢的地方，比如：
1、你的代码有问题？
2、是否有死循环
3、是否数据量太多？
4、是否数据的处理很耗时？
5、还有一个可能：是否使用了事务！
0
你试试创建个索引，索引键是 txndate ，并包含 CAPITAL_PROPORTION ，STATUS ，CONTRACT_NUMBER
建了，没用
@天地不——仁:
那你缩小结果集：
先把 select *
from UDT$LOAN_LIABILITY_hall
where txndate = @txndate
and CAPITAL_PROPORTION <> 0
and STATUS = '1'
生成临时表，
再对这个临时表进行操作了。
@Yu: 我是在两台电脑上分别运行，一台电脑上能够很快运行出结果，另一台运行不出，是不是跟sql服务器的配置有关
@天地不——仁:
sql服务器的配置不会限制某一台机器的
@Yu:两台电脑上的sql服务器的配置不同，但是我不知道哪里不同
@天地不——仁: 执行计划一样吗
@天地不——仁: 数据库-属性-选项
看看是不是一致的
0
自定义函数是很影响性能的，数据量大就不推荐用。看看能不能换成其他方式。
0
函数影响性能
