存储过程优化

[boytomato]

CREATE PROC Cqrcrjhz

@month int,
@year int ,
@Dz_id int

AS

declare @day int ,@dayCur int
declare @CurString nvarchar(4000)
declare @cur_id int
declare @tempflag nvarchar(200)


if (@Dz_id!=-1)
begin
set @tempflag='where kqUserInfo.user_dz_id ='+cast(@Dz_id as varchar(2))
end
else
begin
set @tempflag=''
end
SELECT @day=DATEPART(dd,DATEADD(m,1,CAST(STR(@month) + '/01/' + STR(@year) as
datetime)) - 1)

set @dayCur=1
set @CurString='select user_id as 编号 ,user_Name as 姓名, userdept.dz_name as 单位,'
set @CurString=@CurString+''''''+'as 工种'+','''' as 出勤,'
while(@dayCur<=@day)
begin
Set @CurString=@CurString+'dbo.Kqsumbyday('+cast(@year as varchar(4))+','+cast(@month as varchar(2))+','+cast(@dayCur as varchar(2))+','+'user_id) as '''+Cast(@dayCur as varchar(2))+''','
set @dayCur=@dayCur+1
end
set @CurString=substring(@CurString,1,len(@CurString)-1)

set @CurString=@CurString+', (select count(kq_Cur_Id) from KqInfoList where datepart (yyyy,kq_date)='+cast(@year as varchar(4))+' and kq_id=kqUserInfo.user_id and datepart (mm,kq_date)='+cast(@month as varchar(2))+') as 合计'
set @CurString=@CurString+', (select count(kq_Cur_Id) from KqInfoList where datepart (yyyy,kq_date)='+cast(@year as varchar(4))+' and kq_id=kqUserInfo.user_id and datepart (mm,kq_date)='+cast(@month as varchar(2))+'and kq_bc=1) as 夜'
set @CurString=@CurString+', (select count(kq_Cur_Id) from KqInfoList where datepart (yyyy,kq_date)='+cast(@year as varchar(4))+' and kq_id=kqUserInfo.user_id and datepart (mm,kq_date)='+cast(@month as varchar(2))+'and kq_bc=2) as 早'
set @CurString=@CurString+', (select count(kq_Cur_Id) from KqInfoList where datepart (yyyy,kq_date)='+cast(@year as varchar(4))+' and kq_id=kqUserInfo.user_id and datepart (mm,kq_date)='+cast(@month as varchar(2))+'and kq_bc=3) as 中'
set @CurString=@CurString+',''''as 差,'''' as 伤,''''as 休,''''as 事,''''as 旷,'''' as 其他 '
set @CurString=@CurString+' from kqUserInfo inner join userdept on userdept.dz_id=kqUserInfo.user_dz_id '+@tempflag+' order by user_dz_id ,user_id '

print @CurString
exec(@CurString)

GO


调用的函数
CREATE FUNCTION dbo.Kqsumbyday(@year int ,@month int ,@day int , @user_id int )
RETURNS nvarchar(3000)
AS
BEGIN
declare @sumstr nvarchar(3000)
set @sumstr=' '
select @sumstr=@sumstr+case when @sumstr<>'' then ',' else '' end+ substring (bc_Name,1,1) from KqInfoList a
inner join bcInfo b on a. kq_bc=b.bc_id
where
datepart (yyyy,kq_date)=@year and datepart (mm,kq_date)=@month and datepart (dd,kq_date)=@day
and kq_id=@user_id
return(@sumstr)
END


调用的表 KqInfoList 有 2W数据

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KqInfoList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KqInfoList]
GO

CREATE TABLE [dbo].[KqInfoList] (
[Kq_Cur_id] [int] IDENTITY (1, 1) NOT NULL ,
[Kq_id] [int] NULL ,
[Kq_Date] [datetime] NULL ,
[Kq_Bc] [int] NULL ,
[Kq_Dz] [int] NULL ,
[Kq_Cur_Date] [datetime] NULL ,
[KQ_Flag] [int] NULL ,
[Kq_DeptChild_id] [int] NULL ,
[KQ_Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[KQ_User] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO


有700 左右数据
表KqUserInfo
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KqUserInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KqUserInfo]
GO

CREATE TABLE [dbo].[KqUserInfo] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Dept_Child_Id] [int] NULL ,
[User_Name] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[User_Dz_id] [int] NULL ,
[User_X] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO


表 UserDept
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserDept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserDept]
GO

20 条数据
CREATE TABLE [dbo].[UserDept] (
[Dz_id] [int] IDENTITY (1, 1) NOT NULL ,
[Dz_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Flag] [int] NULL
) ON [PRIMARY]
GO

存储过程执行一次大概要40S左右。。。。
谢谢大家。。

[boytomato]

调用的函数
CREATE FUNCTION dbo.Kqsumbyday(@year int ,@month int ,@day int , @user_id int )
RETURNS nvarchar(3000)
AS
BEGIN
declare @sumstr nvarchar(3000)
set @sumstr=' '
select @sumstr=@sumstr+case when @sumstr<>'' then ',' else '' end+ substring (bc_Name,1,1) from KqInfoList a
inner join bcInfo b on a. kq_bc=b.bc_id
where
datepart (yyyy,kq_date)=@year and datepart (mm,kq_date)=@month and datepart (dd,kq_date)=@day
and kq_id=@user_id
return(@sumstr)
END

主要问题在函数。
函数怎么能优化一下呢。

[superhasty]

晕。

where
datepart (yyyy,kq_date)=@year and datepart (mm,kq_date)=@month and datepart (dd,kq_date)=@day

这样根本使用不了索引。不过你也根本没有建立索引。那样你的Join将成为Hash Join，性能非常慢。我刚写了一篇Blog，不知道对你是否有一些帮助？

http://www.cnblogs.com/superhasty/archive/2007/07/30/SQLServer2005Memory_Sql.html

[boytomato]

我的日期格式都是
这样的
2007-08-01
这样如何和 数据库中的 datetime 类型关联比较
好对时候也只能这样获取
把日期转换成
covert(nvarchar(10),getdate(),120)='2007-08-01'
可是如何比较效率高点呢。。

[boytomato]

CREATE FUNCTION dbo.Kqsumbyday(@year nvarchar(4) ,@month nvarchar(2) ,@day nvarchar(2) , @user_id int )
RETURNS nvarchar(3000)
AS
BEGIN
declare @sumstr nvarchar(3000)
set @sumstr=' '
select @sumstr=@sumstr+case when @sumstr<>'' then ',' else '' end+ substring (bc_Name,1,1) from KqInfoList a
inner join bcInfo b on a. kq_bc=b.bc_id
where
kq_date=@year+'-'+@month+'-'+@day
and kq_id=@user_id
return(@sumstr)
END

给日期加上索引，把日期这样处理 kq_date=@year+'-'+@month+'-'+@day
由原来的40 s 下降为12s
还有没有方法提高效率。。

[boytomato]

faint