求大神帮助优化一个低级的视图

tds__609 2014-12-01 08:55:31

现在系统有个视图，里面有很多函数，效率很低，求大神帮助优化，
分不够了，拜托了。。。



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

...全文

206 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

tds__609 2014-12-01

打赏
举报

所有的函数，又用out apply代替了，速度还是不行，求大神支招

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,
b.out_yard_time as lastOutRussia,
b.name_en as lastOutRussiaCity,

datediff(day,a.in_yard_time,b.out_yard_time)+1 AS InRussiaDays,
datediff(day,dbo.cnt_loop_index.begin_time,c.out_yard_time)+1 AS InChinaDays,
datediff(day,c.out_yard_time,a.in_yard_time)+1 as OnWayDays,
a.in_yard_time as firstInRussia,
a.name_en as firstInRussiaCity,
case when isnull(dbo.cnt_loop_index.end_time,'')='' then
datediff(day, a.in_yard_time,getdate())+1 
when  dbo.cnt_loop_index.end_time <>'' then 
datediff(day, a.in_yard_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
outer apply (
select top 1   code_port.name_en ,in_yard_time  from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where  in_yard_time 
between cnt_loop_index.begin_time and isnull(cnt_loop_index.end_time,getdate()) 
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='俄罗斯'
order by cnt_ie_info.create_time asc
)a
outer apply (
select top 1  out_yard_time,code_port.name_en from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where  cnt_time_state.in_yard_time
between cnt_loop_index.begin_time and isnull(cnt_loop_index.end_time,getdate()) 
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='俄罗斯'
order by cnt_ie_info.create_time desc
)b
outer apply 
(
select top 1  out_yard_time from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where  cnt_time_state.in_yard_time < a.in_yard_time
and cnt_time_state.in_yard_time >=(cnt_loop_index.begin_time-1)
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='中国'
order by out_yard_time desc
)c
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

發糞塗牆 2014-12-01

打赏
举报

查收私信，把执行计划发我邮箱，你这看不清，右键执行计划另存为即可

tds__609 2014-12-01

打赏
举报

引用 2 楼 DBA_Huangzj 的回复:

执行计划贴来看看

这是按照索引字段查找的执行计划，不增加检索条件，根本就查不完啊。太慢了

tds__609 2014-12-01

打赏
举报

引用 3 楼 xiaoxiangqing 的回复:

里面这么多函数，你可以看下执行计划，看哪个地方慢？

是啊，所以想问问这些函数有办法替换没有。

xiaoxiangqing 2014-12-01

打赏
举报

里面这么多函数，你可以看下执行计划，看哪个地方慢？

發糞塗牆 2014-12-01

打赏
举报

执行计划贴来看看

tds__609 2014-12-01

打赏
举报

有人么？

發糞塗牆 2014-12-01

打赏
举报

执行一下，索引明可以自己定义

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

还在加载中灬 2014-12-01

打赏
举报

我想能不能少有些函数，在这里和子查询效果一样啊~ 看能不能表连接处理，不过你的贴信息量巨大，我也说不上什么就当是建议吧

MVC 模式代表 Model-View-Controller（模型-视图-控制器）模式。这种模式用于应用程序的分层开发。Model（模型） - 模型代表一个存取数据的对象或 JAVA POJO。它也可以带有逻辑，在数据变化时更新控制器。View（视图） - 视图代表模型包含的数据的可视化。Controller（控制器） - 控制器作用于模型和视图上。它控制数据流向模型对象，并在数据变化时更新视图。它使视图与模型分离开。

安卓开发大军浩浩荡荡，经过近十年的发展，Android技术优化日异月新，如今Android 9.0 已经发布，Android系统性能也已经非常流畅，可以在体验上完全媲美iOS。但是，到了各大厂商手里，改源码、自定义系统，使得Android原生系统变得鱼龙混杂，然后到了不同层次的开发工程师手里，因为技术水平的参差不齐，即使很多手机在跑分软件性能非常高，打开应用依然存在卡顿现象。另外，随着产品内容迭...

Android 性能优化 (一)APK高效瘦身 http://blog.csdn.net/whb20081815/article/details/70140063 Android 性能优化 (二)数据库优化秒变大神 http://blog.csdn.net/whb20081815/article/details/70142033 Android 性能优化（三）布局优化秒变大神 ...

APMCon2017|一大波技术大神来袭，你要的性能优化干货这里全都有在波涛汹涌的技术世界里,如果不希望自己的小船说翻就翻,就需要跟着大神多学学,所谓听君一席话，胜读十年书。现在就有个跟着大神学技术的好机会就在今年的8月10-11日，听云联合了极客邦科技/InfoQ将共同主办国内第二届应用性能管理大会-APMCon2017，会议内...

MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章

求大神 帮助优化一个低级的视图

求大神帮助优化一个低级的视图