存储过程怎么优化？？？

java4yjuany 2017-02-28 12:08:05

业务需求。社区出来数据肯能有重复,让后按时间排序打印，：查询出来的小孩子id也会有重复所以用了distict ,这里执行一个晚上都不出来，怎能么优化下呢？数据量大？求帮助
USE [Child_xh]
GO
/****** Object: StoredProcedure [dbo].[PROC_DY_tgjc] Script Date: 2017/2/28 12:00:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_DY_tgjc]
AS
BEGIN
declare @a int,@error int,@b int
declare @bchildid varchar(50)--临时变量，用来保存游标值
declare @printtableid varchar(50)--临时变量，用来保存游标值
set @a=1 set @error=0 set @b=1
BEGIN TRAN --申明事务
--申明游标为BCHILD_ID
declare order_cursor CURSOR FOR --select distinct BCHILD_ID from CHILD_CONSTITUTION where pagesize is null or positions is null
select distinct BCHILD_ID from CHILD_CONSTITUTION where (pagesize is null or positions is null) and DEL_FLAG='T'
--打开游标
open order_cursor
FETCH NEXT FROM order_cursor INTO @bchildid
WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
--执行sql操作
set @a=1 set @b=1
declare printtalbe_cursor CURSOR FOR select CHILD_CONSTITUTION_ID from CHILD_CONSTITUTION where BCHILD_ID=@bchildid and DEL_FLAG='T'
open printtalbe_cursor
FETCH NEXT FROM printtalbe_cursor INTO @printtableid
WHILE @@FETCH_STATUS= 0
begin
if @a%8=0
begin
set @b=@a/8
end
if @a%8!=0
begin
set @b=@a/8+1
end
update CHILD_CONSTITUTION set POSITIONS=@a,PAGESIZE=@b where CHILD_CONSTITUTION_ID=@printtableid
set @a=@a+1
FETCH NEXT FROM printtalbe_cursor INTO @printtableid
end
close printtalbe_cursor
DEALLOCATE printtalbe_cursor

set @error=@error+@@error --记录每次运行sql后是否正确 0正确
FETCH NEXT FROM order_cursor INTO @bchildid
end
if @error=0--没有错误统一提交事务
begin
commit tran--提交
end
else
begin
rollback tran--回滚
end
CLOSE order_cursor--关闭游标
DEALLOCATE order_cursor--释放游标
END

...全文