视图 CorrelationID_vw有六个字段,分别是EtrackID,CorrelationID,ActivityID, SourceApplicationServer,TrackingItemSeven,TrackingItemFive现在有一个Function需要调用这个视图,函数的主体如下:Create function [dbo].[GetEtrackIDNumber]
(
@EtrackID int,
@PartnerName nvarchar(128)
)
returns int
as
begindeclare @number int declare @table1 table(EtrackID nvarchar(50))
declare @table2 table(EtrackID nvarchar(50))
declare @table3 table(EtrackID nvarchar(50))
declare @table4 table(EtrackID nvarchar(50))
declare @table5 table(EtrackID nvarchar(50))declare @temp1 table(ActivityID nvarchar(128))
declare @temp4 table(CorrelationID nvarchar(128))
declare @temp5 table(TrackingItemseven nvarchar(128))
declare @temp6 table(CorrelationID nvarchar(128)) insert @temp1 select ActivityID from CorrelationID_vw where EtrackID in (@EtrackID)
insert @table1 select EtrackID from CorrelationID_vw A JOIN @temp1 B on B.ActivityID = A.CorrelationID
insert @table2 select EtrackID from CorrelationID_vw A JOIN @temp1 B on B.ActivityID = A.ActivityIDinsert @temp4 select CorrelationID from CorrelationID_vw where EtrackID in (@EtrackID)
insert @table3 select EtrackID from CorrelationID_vw A JOIN @temp4 B on A.ActivityID = B.CorrelationIDinsert @temp5 select TrackingItemseven from CorrelationID_vw where EtrackID in (@EtrackID) AND TrackingItemFive in('create','maintain')
insert @table5 select EtrackID from CorrelationID_vw A JOIN @temp5 B on A.CorrelationID = B.TrackingItemseveninsert @temp6 select CorrelationID from CorrelationID_vw where EtrackID in (@EtrackID) AND CorrelationID <> ''
insert @table4 select EtrackID from CorrelationID_vw A JOIN @temp6 B on A.CorrelationID = B.CorrelationIDinsert @table1 select * from @table2
insert @table1 select * from @table3
insert @table1 select * from @table4
insert @table1 select * from @table5
insert @table1 select @EtrackID as EtrackIDselect @number=count(ID) from etrack_document_tb where PartnerName=@PartnerName and
exists (select 1 from @table1 where EtrackID = etrack_document_tb.interchangeID)return @number
end
GO由于逻辑的实现需要扫描视图很多次,所以速度比较慢,这个试图大约有300W的数据。哪位高手能够帮我优化下这个函数?
(
@EtrackID int,
@PartnerName nvarchar(128)
)
returns int
as
begindeclare @number int declare @table1 table(EtrackID nvarchar(50))
declare @table2 table(EtrackID nvarchar(50))
declare @table3 table(EtrackID nvarchar(50))
declare @table4 table(EtrackID nvarchar(50))
declare @table5 table(EtrackID nvarchar(50))declare @temp1 table(ActivityID nvarchar(128))
declare @temp4 table(CorrelationID nvarchar(128))
declare @temp5 table(TrackingItemseven nvarchar(128))
declare @temp6 table(CorrelationID nvarchar(128)) insert @temp1 select ActivityID from CorrelationID_vw where EtrackID in (@EtrackID)
insert @table1 select EtrackID from CorrelationID_vw A JOIN @temp1 B on B.ActivityID = A.CorrelationID
insert @table2 select EtrackID from CorrelationID_vw A JOIN @temp1 B on B.ActivityID = A.ActivityIDinsert @temp4 select CorrelationID from CorrelationID_vw where EtrackID in (@EtrackID)
insert @table3 select EtrackID from CorrelationID_vw A JOIN @temp4 B on A.ActivityID = B.CorrelationIDinsert @temp5 select TrackingItemseven from CorrelationID_vw where EtrackID in (@EtrackID) AND TrackingItemFive in('create','maintain')
insert @table5 select EtrackID from CorrelationID_vw A JOIN @temp5 B on A.CorrelationID = B.TrackingItemseveninsert @temp6 select CorrelationID from CorrelationID_vw where EtrackID in (@EtrackID) AND CorrelationID <> ''
insert @table4 select EtrackID from CorrelationID_vw A JOIN @temp6 B on A.CorrelationID = B.CorrelationIDinsert @table1 select * from @table2
insert @table1 select * from @table3
insert @table1 select * from @table4
insert @table1 select * from @table5
insert @table1 select @EtrackID as EtrackIDselect @number=count(ID) from etrack_document_tb where PartnerName=@PartnerName and
exists (select 1 from @table1 where EtrackID = etrack_document_tb.interchangeID)return @number
end
GO由于逻辑的实现需要扫描视图很多次,所以速度比较慢,这个试图大约有300W的数据。哪位高手能够帮我优化下这个函数?
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CorrelationID_vw] asSELECT bam_Tracker_Send_EtrackID.ID AS EtrackID,
bam_Tracker_Receive_Completed.CorrelationID,
bam_Tracker_Receive_Completed.ActivityID,
'TK5ICOEBTSA' as SourceApplicationServer,
bam_Tracker_Send_AllInstances.TrackingItemSeven,
bam_Tracker_Send_AllInstances.TrackingItemFive FROM bam_Tracker_Send_AllInstances with (nolock)
INNER JOIN bam_Tracker_Send_EtrackID with (nolock)
ON bam_Tracker_Send_AllInstances.ActivityID = bam_Tracker_Send_EtrackID.ActivityID
INNER JOIN bam_Tracker_Receive_Completed with (nolock)
ON bam_Tracker_Send_AllInstances.InterchangeID = bam_Tracker_Receive_Completed.ActivityID其中bam_Tracker_Send_AllInstances也是视图,其他两个是表视图bam_Tracker_Send_AllInstances CREATE VIEW [dbo].[bam_Tracker_Send_AllInstances]
AS
SELECT *
FROM [dbo].[bam_Tracker_Send_ActiveInstances] with (nolock)
WHERE ActivityID NOT IN
(
SELECT ActivityID FROM [dbo].[bam_Tracker_Send_CompletedInstances] with (nolock)
)
UNION ALL
SELECT * FROM [dbo].[bam_Tracker_Send_CompletedInstances] with (nolock) GO
现在是bam_Tracker_Receive_Completed有200W左右的数据,bam_Tracker_Send_EtrackID大约400多W,
bam_Tracker_Send_AllInstances大约400多W。现在调用这个函数一次大约需要2~4秒,在10分钟内我需要调用这个函数最高1000左右,所以比较愁啊,这个函数不能满足我的需求啊。
难道真是这个函数的逻辑比较复杂才导致调用时间过长吗?
(
@EtrackID int,
@PartnerName nvarchar(128)
)
returns int
as
begindeclare @number int select @number=count(ID)
from etrack_document_tb AS T
where PartnerName=@PartnerName
and
exists(select 1 from CorrelationID_vw A JOIN CorrelationID_vw B on
(
B.ActivityID IN( A.CorrelationID,A.ActivityID)
OR
A.ActivityID IN( B.CorrelationID,B.ActivityID)
OR
(B.TrackingItemFive in('create','maintain') AND a.CorrelationID=B.TrackingItemseven)
OR
(B.CorrelationID <> '' AND A.CorrelationID = B.CorrelationID)
) AND B.EtrackID=@EtrackID AND A.EtrackID=T.interchangeID )
return @number
end
GO
视图次数过多,导致性能瓶颈。