现在系统有个视图，里面有很多函数，效率很低，求大神帮助优化，
分不够了， 拜托了。。。
ALTER view
[dbo].[v_cnt_loop2] as
SELECT ROW_NUMBER() OVER(PARTITION BY dbo.cnt_loop_index.cnt_no ORDER BY begin_time DESC) rowId, dbo.cnt_ie_info.cnt_type, dbo.cnt_ie_info.cnt_size, dbo.cnt_ie_info.cnt_category, dbo.cnt_ie_info.cnt_kind,
dbo.cnt_loop_index.begin_time, dbo.cnt_loop_index.end_time, dbo.cnt_loop_index.cnt_no,
dbo.cnt_ie_info.city_name AS begin_city_name,dbo.cnt_ie_info.yard_name AS begin_yard_name,
dbo.cnt_loop_index.begin_job_no, dbo.cnt_loop_index.end_job_no,cnt_ie_info_1.city_name AS end_city_name, cnt_ie_info_1.yard_name AS end_yard_name, DATEDIFF(day, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) + 1 AS days, dbo.GetCntNoInRussiaDays(dbo.cnt_loop_index.cnt_no,
dbo.cnt_loop_index.begin_time,dbo.cnt_loop_index.end_time) AS InRussiaDays,
dbo.GetCntInChinaDays(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) AS InChinaDays,
dbo.GetCntOnWayDaysToRussia(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) AS OnWayDays,
dbo.GetFirstInRussiaDate(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) as firstInRussia,
dbo.GetFirstInRussiaCity(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) as firstInRussiaCity,
dbo.GetCntLastOutRussia(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) as lastOutRussia,
dbo.GetLastOutRussiaCity(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) as lastOutRussiaCity,
case when isnull(dbo.cnt_loop_index.end_time,'')='' then
datediff(day,dbo.GetFirstInRussiaDate(dbo.cnt_loop_index.cnt_no, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time),getdate())+1
when isnull(dbo.cnt_loop_index.end_time,'')<>'' then
datediff(day, dbo.GetFirstInRussiaDate(dbo.cnt_loop_index.cnt_no,
dbo.cnt_loop_index.begin_time, dbo.cnt_loop_index.end_time),dbo.cnt_loop_index.end_time)+1
end useDays,
dbo.cnt_loop_index.col_1, dbo.cnt_loop_index.RID
FROM dbo.cnt_loop_index LEFT OUTER JOIN
dbo.cnt_ie_info ON dbo.cnt_loop_index.begin_job_no = dbo.cnt_ie_info.job_no LEFT OUTER JOIN
dbo.cnt_ie_info AS cnt_ie_info_1 ON dbo.cnt_loop_index.end_job_no = cnt_ie_info_1.job_no
函数：
ALTER function [dbo].[GetCntInChinaDays] (@CntNo varchar(50),@BeginTime datetime,@EndTime datetime)
returns INT
as
begin
declare @count int
declare @inyard datetime
declare @outyard datetime
/*
获取从去俄罗斯之前在中国的时间天数
*/
set @count=null
if rtrim(isnull(@EndTime,''))<>''
set @EndTime=getdate()
if rtrim(isnull(@CntNo,''))<>''
begin
set @inyard = (
select top 1 in_yard_time from v_cnt_ie_state_base where in_yard_time between @BeginTime and @EndTime
and cnt_no=@CntNo
and at_country='俄罗斯'
order by create_time asc)
set @outyard= (
select top 1 out_yard_time from v_cnt_ie_state_base where in_yard_time < @inyard
and in_yard_time >=(@BeginTime-1)
and cnt_no=@CntNo
and at_country='中国'
order by out_yard_time desc)
select @count=datediff(day,@BeginTime,@outyard)+1
end
return @count
ALTER function [dbo].[GetCntLastOutRussia] (@CntNo varchar(50),@BeginTime datetime,@EndTime datetime)
returns datetime
as
begin
declare @outyard datetime
/*
获取从去俄罗斯之前在中国的时间天数
*/
if rtrim(isnull(@EndTime,''))<>''
set @EndTime=getdate()
if rtrim(isnull(@CntNo,''))<>''
begin
set @outyard= (
select top 1 out_yard_time from v_cnt_ie_state_base where out_yard_time < @EndTime
and in_yard_time >=@BeginTime
and cnt_no=@CntNo
and at_country='俄罗斯'
order by out_yard_time desc)
end
return @outyard
ALTER function [dbo].[GetFirstInRussiaCity] (@CntNo varchar(50),@BeginTime datetime,@EndTime datetime)
returns nvarchar(50)
as
begin
declare @inCity nvarchar(50)
/*
获取第一次进去俄罗斯的城市
*/
if rtrim(isnull(@EndTime,''))<>''
set @EndTime=getdate()
if rtrim(isnull(@CntNo,''))<>''
begin
set @inCity = (
select top 1 city_name from v_cnt_ie_state_base where in_yard_time> @BeginTime
and in_yard_time< @EndTime
and cnt_no=@CntNo
and at_country='俄罗斯'
order by create_time asc)
end
return @inCity
ALTER function [dbo].[GetLastOutRussiaCity] (@CntNo varchar(50),@BeginTime datetime,@EndTime datetime)
returns nvarchar(50)
as
begin
declare @outCity nvarchar(50)
/*
获取从去俄罗斯之前在中国的时间天数
*/
if rtrim(isnull(@EndTime,''))<>''
set @EndTime=getdate()
if rtrim(isnull(@CntNo,''))<>''
begin
set @outCity= (
select top 1 city_name from v_cnt_ie_state_base where out_yard_time < @EndTime
and in_yard_time >=@BeginTime
and cnt_no=@CntNo
and at_country='俄罗斯'
order by out_yard_time desc)
end
return @outCity
end
------解决思路----------------------
执行一下，索引明可以自己定义
USE [ECTMP]
GO
CREATE NONCLUSTERED INDEX IX_cnt_time_state_1
ON [dbo].[cnt_time_state] ([cnt_ie_fid],[in_yard_time])
INCLUDE ([out_yard_time])
GO