-- 使用游标循环取出每个货位上的数据,因为一个货位上存在存放多个级别、烟笼号等
-- 经过分析这段游标相当影响效率,这段游标如果选择全部记录集则执行了35秒 Comment By:Bobby'
DECLARE @Place varchar(2000)
DECLARE @Customer varchar(2000)
DECLARE @Count varchar(2000)
DECLARE @NetW varchar(2000)
DECLARE @Date varchar(2000)
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
Declare Client_GetInfo Cursor for
Select
DISTINCT V_Place
from ##tempPlaceStorageGroup
Open Client_GetInfo
Fetch Next From Client_GetInfo INTO @Place
While @@FETCH_STATUS = 0
Begin
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
SET @Date = '' SELECT @Customer = @Customer + ',' + V_Customer
FROM (SELECT DISTINCT V_Customer FROM
##tempPlaceStorageGroup
WHERE V_Place = @Place AND V_Customer IS NOT NULL) AS XX SELECT @Count = @Count + V_GBGradeCode + ':' + CAST(I_InCount AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place AND I_InCount IS NOT NULL
AND V_GBGradeCode IS NOT NULL SELECT @NetW = @NetW + V_GBGradeCode + ':' + CAST(N_NetW AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place
AND N_NetW IS NOT NULL
AND V_GBGradeCode IS NOT NULL SELECT @Date = @Date + CAST(D_InDate AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place
AND D_InDate IS NOT NULL
AND V_GBGradeCode IS NOT NULL UPDATE ##tempPlaceStorageGroup
SET CustomerInfo = @Customer ,
CountInfo = @Count,
NetWInfo = @NetW,
DateInfo = @Date
WHERE V_Place = @Place
AND V_Customer IS NULL
Fetch Next From Client_GetInfo INTO @Place
End
Close Client_GetInfo
Deallocate Client_GetInfo这是我存储过程中游标的代码,这段代码执行了35秒 ,请大家帮我优化一下
-- 经过分析这段游标相当影响效率,这段游标如果选择全部记录集则执行了35秒 Comment By:Bobby'
DECLARE @Place varchar(2000)
DECLARE @Customer varchar(2000)
DECLARE @Count varchar(2000)
DECLARE @NetW varchar(2000)
DECLARE @Date varchar(2000)
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
Declare Client_GetInfo Cursor for
Select
DISTINCT V_Place
from ##tempPlaceStorageGroup
Open Client_GetInfo
Fetch Next From Client_GetInfo INTO @Place
While @@FETCH_STATUS = 0
Begin
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
SET @Date = '' SELECT @Customer = @Customer + ',' + V_Customer
FROM (SELECT DISTINCT V_Customer FROM
##tempPlaceStorageGroup
WHERE V_Place = @Place AND V_Customer IS NOT NULL) AS XX SELECT @Count = @Count + V_GBGradeCode + ':' + CAST(I_InCount AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place AND I_InCount IS NOT NULL
AND V_GBGradeCode IS NOT NULL SELECT @NetW = @NetW + V_GBGradeCode + ':' + CAST(N_NetW AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place
AND N_NetW IS NOT NULL
AND V_GBGradeCode IS NOT NULL SELECT @Date = @Date + CAST(D_InDate AS varchar) + ','
FROM ##tempPlaceStorageGroup
WHERE V_Place = @Place
AND D_InDate IS NOT NULL
AND V_GBGradeCode IS NOT NULL UPDATE ##tempPlaceStorageGroup
SET CustomerInfo = @Customer ,
CountInfo = @Count,
NetWInfo = @NetW,
DateInfo = @Date
WHERE V_Place = @Place
AND V_Customer IS NULL
Fetch Next From Client_GetInfo INTO @Place
End
Close Client_GetInfo
Deallocate Client_GetInfo这是我存储过程中游标的代码,这段代码执行了35秒 ,请大家帮我优化一下
解决方案 »
- 怎样删除这几条数据 急救啊
- 在线等,菜鸟问题,sql server 中bak文件如何变成mdf,ldf文件
- PowerDesigner 数据表 导入 sqlserver
- 有点复杂的交叉表
- 用sql语句能不能获得硬盘号?????精神有些不正常了????help me
- sql server 2005 如何安装 Reporting Services 报表服务?
- 再请邹建大哥,还是那个复杂报表的问题。
- 哪位大神帮忙看看我的存储过程有什么问题
- 有人知道在oracle中写入一个精确到分钟的日期记录的SQL语句吗?
- Oracle 安装 后IIS错误!!
- 成功Sql2000reporting service 迁移到Sql2005reporting service
- 多表关联如何优化
我们这边服务器配置很高,应该不会这么慢啊
DECLARE @Customer varchar(2000),@V_Customer varchar(100)
DECLARE @Count varchar(2000),@V_GBGradeCode varchar(100),@I_InCount varchar(100),@N_NetW varchar(100),@D_InDate datetime
DECLARE @NetW varchar(2000)
DECLARE @Date varchar(2000)
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
Declare Client_GetInfo Cursor for
Select
DISTINCT V_Place,V_Customer,V_GBGradeCode,I_InCount,N_NetW,D_InDate
from ##tempPlaceStorageGroup
Open Client_GetInfo
Fetch Next From Client_GetInfo INTO @Place,@V_Customer,@V_GBGradeCode,@I_InCount,@N_NetW,@D_InDate
While @@FETCH_STATUS = 0
Begin
SET @Customer = ''
SET @Count = ''
SET @NetW = ''
SET @Date = '' SELECT @Customer = @Customer + ',' + @V_Customer
SELECT @Count = @Count + @V_GBGradeCode + ':' + @I_InCount + ','
SELECT @NetW = @NetW + @V_GBGradeCode + ':' +@N_NetW + ','
SELECT @Date = @Date + CAST(@D_InDate AS varchar) + ','
Fetch Next From Client_GetInfo INTO @Place
End UPDATE ##tempPlaceStorageGroup
SET CustomerInfo = @Customer ,
CountInfo = @Count,
NetWInfo = @NetW,
DateInfo = @Date
WHERE V_Place = @Place
AND V_Customer IS NULL
Close Client_GetInfo
Deallocate Client_GetInfo
--如果 数据类型有错误 请更正一下吧
*****************************************************************************
最近没想出什么好签名!
Fetch Next From Client_GetInfo INTO @Place,@V_Customer,@V_GBGradeCode,@I_InCount,@N_NetW,@D_InDate
冒牌的先说一下:
一般来说,游标代码是很少有优化空间的,而且楼主的这段游标代码可以说中规中矩,zhuaiman(Money(int NNNNN)(︶︿︶)╭∩╮) 得改法改变了功能,应该不可取,所以最好考虑不用游标的方法,函数是解决你的功能的最直观的方法,但是你的是临时表,函数用不了,而且函数性能不一定比游标好
create function fn_Customer (
@Place varchar(2000) --需要这么长吗?楼主考虑下
)
returns varchar(2000)
as
begin
declare @r varchar(2000)
set @r=''
select @r=@r+','+V_Customer from tempPlaceStorageGroup where V_Place=@Place AND V_Customer IS NOT NULL
-- if @r<>'' --这两句楼主原来没有这功能,需要就加
-- set @r=stuff(@r,1,1,'')
return @r
end
gocreate function fn_Count (
@Place varchar(2000) --需要这么长吗?楼主考虑下
)
returns varchar(2000)
as
begin
declare @r varchar(2000)
set @r=''
select @r=@r+ V_GBGradeCode + ':' + CAST(I_InCount AS varchar) + ',' from tempPlaceStorageGroup where V_Place=@Place AND I_InCount IS NOT NULL
AND V_GBGradeCode IS NOT NULL-- if @r<>'' --这两句楼主原来没有这功能,需要就加
-- set @r=left(@r,len(@r)-1) return @r
end
gocreate function fn_NetW (
@Place varchar(2000) --需要这么长吗?楼主考虑下
)
returns varchar(2000)
as
begin
declare @r varchar(2000)
set @r=''
select @r=@r+V_GBGradeCode + ':' + CAST(N_NetW AS varchar) + ',' from tempPlaceStorageGroup where V_Place=@Place
AND N_NetW IS NOT NULL
AND V_GBGradeCode IS NOT NULL-- if @r<>'' --这两句楼主原来没有这功能,需要就加
-- set @r=left(@r,len(@r)-1) return @r
end
gocreate function fn_Date (
@Place varchar(2000) --需要这么长吗?楼主考虑下
)
returns varchar(2000)
as
begin
declare @r varchar(2000)
set @r=''
select @r=@r+CAST(D_InDate AS varchar) + ',' from tempPlaceStorageGroup where V_Place=@Place
AND D_InDate IS NOT NULL
AND V_GBGradeCode IS NOT NULL-- if @r<>'' --这两句楼主原来没有这功能,需要就加
-- set @r=left(@r,len(@r)-1) return @r
end
go
--函数建立后,你的存储过程的这一段代码可以用一下语句代替
UPDATE tempPlaceStorageGroup
SET CustomerInfo = dbo.fn_Customer(V_Place),
CountInfo = dbo.fn_Count(V_Place),
NetWInfo = dbo.fn_NetW(V_Place),
DateInfo = dbo.fn_Date(V_Place)
WHERE V_Customer IS NULL
Select
DISTINCT V_Place
from ##tempPlaceStorageGroup
where V_Customer IS NULL
其实用函数和游标我测试了一下觉得速度差不多啊