[登录] [注册]

用友软件论坛 » 用友软件 » 用友T1 » 零售单录入商品信息检索特别慢

零售单录入商品信息检索特别慢

[ 198 查看 / 0 回复 ]

发新话题

回复该主题

悠悠杨

组别高级会计师
生日
帖子2152
积分85290
威望3941
魅力3901
性别
注册时间 2009-06-10
最后登录 2010-07-30

2010-03-15 08:36 |只看楼主 1^#

字体大小: t T

问题名称:	零售单录入商品信息检索特别慢
问题现象:	零售单录入商品信息检索特别慢8秒左右
原因分析:	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_vw_AllBarcode]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[a_vw_AllBarcode] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE View dbo.a_vw_AllBarcode as Select s_ID,barcode From Product where barcode is not null Union select Prod_Id,barcode From Productbarcode GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO declare tb cursor for --刷新视图处理的语句 select 'exec sp_refreshview ''['+replace(user_name(uid), N']',N']]')+'].[' +replace(object_name(id),N']',N']]')+']''' from dbo.sysobjects where xtype='v' and status>=0 union all --刷新存储过程,自定义函数,触发器的语句 select 'exec sp_recompile ''[' + replace(user_name(uid), N']', N']]')+'].[' + replace(object_name(id), N']', N']]')+ ']''' from dbo.sysobjects where xtype in('tr','fn','if','tf','p') and status>=0 declare @s nvarchar(4000) open tb fetch tb into @s while @@fetch_status=0 begin exec(@s) fetch tb into @s end close tb deallocate tb
解决方案:	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_vw_AllBarcode]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[a_vw_AllBarcode] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE View dbo.a_vw_AllBarcode as Select s_ID,barcode From Product where barcode is not null Union select Prod_Id,barcode From Productbarcode GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO declare tb cursor for --刷新视图处理的语句 select 'exec sp_refreshview ''['+replace(user_name(uid), N']',N']]')+'].[' +replace(object_name(id),N']',N']]')+']''' from dbo.sysobjects where xtype='v' and status>=0 union all --刷新存储过程,自定义函数,触发器的语句 select 'exec sp_recompile ''[' + replace(user_name(uid), N']', N']]')+'].[' + replace(object_name(id), N']', N']]')+ ']''' from dbo.sysobjects where xtype in('tr','fn','if','tf','p') and status>=0 declare @s nvarchar(4000) open tb fetch tb into @s while @@fetch_status=0 begin exec(@s) fetch tb into @s end close tb deallocate tb

转发

TOP

上一主题 | 下一主题