首页 新闻 论坛 群组 Blog 文档 下载 读书 Tag 网摘 搜索 .NET Java 游戏 视频 人才 外包 培训 数据库 书店 程序员
中国软件网
欢迎您:游客 | 登录 注册 帮助
    • 这样复杂的存储过程，如何优化？
    进入用户个人空间
    加为好友
    发送私信
    在线聊天
    • lxljx
    • 等级:
    • 可用分等级：
    • 总技术分：
    • 总技术分排名：
    • 揭贴率：
    发表于：2008-08-21 15:16:00 楼主
    我现在的存储过程执行的效率很低，可能主要与数据库中数据太多有关，其中project_nsaler_product表中有450多万数据，大家帮我看看，存储过程还有优化的空间吗？感谢大家提好的建议。

    由于长度限制，存储过程只能放在附件里，数据库用的是sqlserver2000.
    存储过程如下：
    ALTER            proc SP_UniteNsaler_product
    (
    @standardSPid         varchar(20), ---标准商品的Salerproductid
    @delSPid varchar(8000), ---整合后需删除的salerproductid 形如:XXXX,XXXX,XXXX
    @changeTBID         varchar(8000), ---需要替换Salerproductid的投标商品编码 形如:XXXX,XXXX,XXXX
            @changePriceTBID varchar(8000), ---是否需要调整价格 0--否  1--是  形如  0,1,0
            @changeMemo2 varchar(8000),  ---需整合商品的系数
            @Changeprice1 varchar(8000),  ---需调整商品的开标价
    @Changebidprice varchar(8000),  ---需调整商品的中标价
            @operatorid varchar(20), ---操作员名称(由于操作员编码存放在本地机器上,故此处直接存放姓名)
    @succ int=0 output ---成功返回1,失败返回0
    )
    as

    set nocount on
    declare  @cnn            int
    declare  @err            int
    declare  @eventid        varchar(50) 
    declare  @detailid      varchar(50) 

    declare  @namec         varchar(100)
    declare  @medicinemodel  varchar(50)
    declare  @outlookc      varchar(100)
    declare  @unitc          varchar(10)
    declare  @productnm      varchar(100)
    declare  @memo2 bigint
    declare  @jkycj varchar(100)

    declare  @standMemo2    bigint
    declare  @index int
    declare  @actionDesc    varchar(8000)

    declare  @delSPid_pre int
    declare  @delSPid_cur int

    declare  @changeTBid_pre int
    declare  @changeTBid_cur int

    declare  @changePriceTBid_pre int
    declare  @changePriceTBid_cur int

    declare  @changeMemo2_pre int
    declare  @changeMemo2_cur int

    declare  @changePrice1_pre int
    declare  @changePrice1_cur int

    declare  @changeBidprice_pre int
    declare  @changeBidprice_cur int


    declare  @tempTBid  varchar(20)
    declare  @tempSPid varchar(20)
    declare  @sqltext varchar(2000)
    declare  @tempchangeTBID  varchar(8000)
    declare  @tempchangePriceTBID  varchar(1)
    declare  @tempChangeMemo2  varchar(20)



    declare  @tempChangeprice1  varchar(20)
    declare  @tempChangebidprice varchar(20)


    declare  @strprice1 varchar(200)
    declare  @strbidprice varchar(200)

    set @delSPid_pre=0
    set @delSPid_cur=0

    set @changeTBid_pre=0
    set @changeTBid_cur=0

    set @changePriceTBid_pre=0
    set @changePriceTBid_cur=0

    set  @changeMemo2_pre = 0
    set  @changeMemo2_cur = 0

    set  @changeBidprice_pre=0
    set  @changeBidprice_cur =0


    set @changePrice1_pre =0
    set  @changePrice1_cur=0

    set @succ=0
    set @cnn=0
    set @err=0
    set @index=0
    set @actionDesc=''

    select @eventid=newid()


    50  修改 删除 举报 引用 回复
    进入用户个人空间
    加为好友
    发送私信
    在线聊天
    • lxljx
    • 等级:
    • 可用分等级：
    • 总技术分：
    • 总技术分排名：
    发表于：2008-08-21 15:17:261楼 得分:0
    未完待续.....................
    --1.生成动作说明
    if @tempchangeTBID=''
      begin
    SET @delSPid=replace(@delSPid,',',''',''')
    exec ('DECLARE my_cursor_sp CURSOR  FOR
    SELECT namec,medicinemodel,outlookc,unitc,productnm,nsaler_product.memo2,nsaler_product.jkycj FROM product,nsaler_product
    where product.productid=nsaler_product.productid and nsaler_product.salerproductid in ('''+@delSPid+'''))')
      end
    else
      begin
    SET @tempchangeTBID=replace(@changeTBID,',',''',''')
    exec ('DECLARE my_cursor_sp CURSOR  FOR
    SELECT namec,medicinemodel,outlookc,unitc,productnm,nsaler_product.memo2,nsaler_product.jkycj FROM product,nsaler_product
    where product.productid=nsaler_product.productid and
    exists(select salerproductid from project_saler_product where project_saler_product.salerproductid=nsaler_product.salerproductid and
    [id] in ('''+@tempchangeTBID+'''))')
      end

    OPEN my_cursor_sp

    FETCH NEXT FROM my_cursor_sp into @namec,@medicinemodel,@outlookc,@unitc,@productnm,@memo2,@jkycj

    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @index=@index+1
      set @actionDesc=@actionDesc+' 产品'+convert(varchar(10),@index)+':'+@namec+' || '+@medicinemodel+' || '+@outlookc+' || '+@unitc+
            ' || '+isnull(@productnm,'无')+' || '+convert(varchar(10),@memo2)+' || '+@jkycj
      FETCH NEXT FROM my_cursor_sp  into @namec,@medicinemodel,@outlookc,@unitc,@productnm,@memo2,@jkycj
    END

    CLOSE my_cursor_sp
    DEALLOCATE my_cursor_sp

    select  @actionDesc=@actionDesc+' 替换为: '+namec+' || '+medicinemodel+' || '+outlookc+' || '+unitc
          + ' || '+isnull(nsaler_product.productnm,'无')+' || '+convert(varchar(10),nsaler_product.memo2)+' || '+nsaler_product.jkycj,
            @standMemo2=nsaler_product.memo2
        from product,nsaler_product where product.productid=nsaler_product.productid
        and nsaler_product.salerproductid=@standardSPid
    select @cnn=@@rowcount,@err=@err+@@error

    begin tran


    --2.记录整合的动作说明
    insert into unit_action (eventid,unitetype,actiondesc,actdate,operatorid) values(@eventid,'商品整合',@actionDesc,getdate(),@operatorid)
    select @cnn=@@rowcount,@err=@err+@@error
           
    --3.记录动作明细
            if @changeTBID <>''   
              begin
              --3.1 替换nsaler_product表的productid
                    set @changePriceTBID=@changePriceTBID+','
    set @changeTBID=@changeTBID+','
                    set @changeMemo2=@changeMemo2+','
                    set @Changeprice1=@Changeprice1+','
    set @Changebidprice=@Changebidprice+','


            while @changeTBid_cur <LEN(LTRIM(RTRIM(@changeTBID)))
              begin
                            --需整合的商品的ID
                    SET @changeTBid_cur=CHARINDEX(',',@changeTBID,@changeTBid_pre)
            SET @tempTBid=SUBSTRING(@changeTBID,@changeTBid_pre,@changeTBid_cur-@changeTBid_pre)
            SET @changeTBid_pre=@changeTBid_cur+1
                           
                            --是否需要调整价格 0 不调整  1 调整
    SET @changePriceTBid_cur=CHARINDEX(',',@changePriceTBID,@changePriceTBid_pre)
            SET @tempchangePriceTBID=SUBSTRING(@changePriceTBID,@changePriceTBid_pre,@changePriceTBid_cur-@changePriceTBid_pre)
            SET @changePriceTBid_pre=@changePriceTBid_cur+1 
                   
                            --需整合商品的转换系数
    SET @changeMemo2_cur=CHARINDEX(',',@changeMemo2,@changeMemo2_pre)
            SET @tempChangeMemo2=SUBSTRING(@changeMemo2,@changeMemo2_pre,@changeMemo2_cur-@changeMemo2_pre)
            SET @changeMemo2_pre=@changeMemo2_cur+1                       
                           
         
                            --需整合商品的开标价
    SET @changePrice1_cur=CHARINDEX(',',@Changeprice1,@changePrice1_pre)
            SET @tempChangeprice1=SUBSTRING(@Changeprice1,@changePrice1_pre,@changePrice1_cur-@changePrice1_pre)
            SET @changePrice1_pre=@changePrice1_cur+1                       
                   
                   
                            --需整合商品的中标价
    SET @changeBidprice_cur=CHARINDEX(',',@Changebidprice,@changeBidprice_pre)
            SET @tempChangebidprice=SUBSTRING(@Changebidprice,@changeBidprice_pre,@changeBidprice_cur-@changeBidprice_pre)
            SET @changeBidprice_pre=@changeBidprice_cur+1                       
                           


                    select @detailid=newid()
                    --记录替换的动作明细 sqltype:整合的sql语句类型0--delete ,  1--update,  2--insert
               
                            if @tempchangePriceTBID='1'
                              begin
    set @strbidprice=@tempChangebidprice+'/'+@tempChangeMemo2+'*'+convert(varchar,@standMemo2)
    set @strprice1=@tempChangeprice1+'/'+@tempChangeMemo2+'*'+convert(varchar,@standMemo2)

    select @sqltext='update project_saler_product set bidprice=convert(numeric(20,4),'+@strbidprice+'),price1=convert(numeric(20,4),'+@strprice1+'), salerproductid='''+@standardSPid+''' where [id]='''+@tempTBid+''''
                              end
                            else
                              begin
                                    select @sqltext='update project_saler_product set  salerproductid='''+@standardSPid+''' where [id]='''+@tempTBid+''''
                              end
                         
                    insert into unit_action_detail (detailid,eventid,sqltype,sqltext,whichtable,previouskey,presentkey)
                    values(@detailid,@eventid,'1',@sqltext,'project_saler_product',@tempTBid,@tempTBid)
                    select @cnn=@@rowcount,@err=@err+@@error 
    --替换前先备份到副本表
                    insert into project_saler_product_copyright (detailid,[ID],ProjectId,SalerProductId,SalerId,Price,
        Price1,IsYijia,YijiaPrice1,YijiaPrice2,IsBid,BidPrice,TempLsPrice,LsPrice,Pack,
        Cengci,ZhiCai,SalerName,Score,Vote,Memo1,Memo2,Memo3,Memo4,Memo5)
                    select @detailid,[ID],ProjectId,SalerProductId,SalerId,Price,
        Price1,IsYijia,YijiaPrice1,YijiaPrice2,IsBid,BidPrice,TempLsPrice,LsPrice,Pack,
        Cengci,ZhiCai,SalerName,Score,Vote,Memo1,Memo2,Memo3,Memo4,Memo5
                                    from project_saler_product
                    where [id]=@tempTBid
                    select @cnn=@@rowcount,@err=@err+@@error
                   
                    --开始替换
                            if @tempchangePriceTBID='1'
                              begin
                        update project_saler_product set salerproductid=@standardSPid,
                                  bidprice=convert(numeric(20,4),bidprice/convert(int,@tempChangeMemo2)*@standMemo2),
                  price1=convert(numeric(20,4),price1/convert(int,@tempChangeMemo2)*@standMemo2)
                                  where [id]=@tempTBid
                                  select @cnn=@@rowcount,@err=@err+@@error
                              end
                            else
                              begin
                        update project_saler_product set salerproductid=@standardSPid
                                where [id]=@tempTBid
                                  select @cnn=@@rowcount,@err=@err+@@error
                              end
                    if @err>0
                      begin
                          break
                      end
              end
              end
           
           
    修改 删除 举报 引用 回复
    进入用户个人空间
    加为好友
    发送私信
    在线聊天
    • lxljx
    • 等级:
    • 可用分等级：
    • 总技术分：
    • 总技术分排名：
    发表于：2008-08-21 15:18:012楼 得分:0

    未完待续.....................

    --3.2  删除被整合的product
            if @err=0  and @delSPid <>''

              begin
        set @delSPid=@delSPid+','
                while @delSPid_cur <LEN(LTRIM(RTRIM(@delSPid)))
                  begin
                    SET @delSPid_cur=CHARINDEX(',',@delSPid,@delSPid_pre)
            SET @tempSPid=SUBSTRING(@delSPid,@delSPid_pre,@delSPid_cur-@delSPid_pre)
            SET @delSPid_pre=@delSPid_cur+1
                   
                    select @detailid=newid()
                    --3.2.1记录删除动作明细 sqltype:整合的sql语句类型0--delete ,  1--update,  2--insert
    select @sqltext='delete from nsaler_product where salerproductid='''+@tempSPid+''''
                    insert into unit_action_detail (detailid,eventid,sqltype,sqltext,whichtable,previouskey,presentkey)
                    values(@detailid,@eventid,'0',@sqltext,'nsaler_product',@tempSPid,@standardSPid)
                    select @cnn=@@rowcount,@err=@err+@@error 
                    --3.2.2删除前先备份
                    insert into nsaler_product_copyright (detailid,OperatorId,SalerProductId,ProductId,ProductNm,ProductNmE,Lxpj,Isxyzs,Xyzs,pzwh,Xyzsvalidity,
    isXypc,Xypcpzwh,isscpj,yScpj,zsscpj,isFzyp,Fzypwh,Yppzzs,Zlpz,Isyqbg,Yqbgbh,Yqbgvalidity,Iszlbhy,
    Zlh,Zlhvalidity,Isgmp,Gmpbh,Gmpfw,gmptime,Gmpvalidity,Isgmpqy,Ylzm,Ylzmqy,Ylzmh,Ylzmvalidity,
    Isgyzm,Gyzmurl,Iscmsb,Isgxcp,Gxcpph,Gxcpvalidity,Isyyy,PicUrl,Pack,Fgmpprice,Gmpprice,Wsprice,Hsprice,
    wspprice,Hspprice,Lsprice,Validity,Actiontime,Issczm,SczmValidity,Bwtqyid,Iszmwj,Qyid,Zmwjurl,Isfrzm,
    Bsqrxm,Bsqrsfz,Bsqryxq,Bsqwjurl,Status,Flag,Issalerid,Issbzcz,Fzsj,Isjky,Jkycj,Jkydlid,Isxy,XyValidity,
    Memo,Isyphf,issb,Isjkzcz,Isjkjybg,memo1,memo2,memo3,memo4,memo5,PzDate)
                      select @detailid,OperatorId,SalerProductId,ProductId,ProductNm,ProductNmE,Lxpj,Isxyzs,Xyzs,pzwh,Xyzsvalidity,
    isXypc,Xypcpzwh,isscpj,yScpj,zsscpj,isFzyp,Fzypwh,Yppzzs,Zlpz,Isyqbg,Yqbgbh,Yqbgvalidity,Iszlbhy,
    Zlh,Zlhvalidity,Isgmp,Gmpbh,Gmpfw,gmptime,Gmpvalidity,Isgmpqy,Ylzm,Ylzmqy,Ylzmh,Ylzmvalidity,
    Isgyzm,Gyzmurl,Iscmsb,Isgxcp,Gxcpph,Gxcpvalidity,Isyyy,PicUrl,Pack,Fgmpprice,Gmpprice,Wsprice,Hsprice,
    wspprice,Hspprice,Lsprice,Validity,Actiontime,Issczm,SczmValidity,Bwtqyid,Iszmwj,Qyid,Zmwjurl,Isfrzm,
    Bsqrxm,Bsqrsfz,Bsqryxq,Bsqwjurl,Status,Flag,Issalerid,Issbzcz,Fzsj,Isjky,Jkycj,Jkydlid,Isxy,XyValidity,
    Memo,Isyphf,issb,Isjkzcz,Isjkjybg,memo1,memo2,memo3,memo4,memo5,PzDate
                      from nsaler_product where salerproductid=@tempSPid
                    select @cnn=@@rowcount,@err=@err+@@error 

                    --3.2.3开始删除
                    delete from nsaler_product where salerproductid=@tempSPid
                    select @cnn=@@rowcount,@err=@err+@@error 
                    if @err>0
                      begin
                          break
                      end
                  end

              end
           


    if @err>0
      begin
        set @succ=0
        rollback tran
      end
    else
      begin
        set @succ=1
        commit tran
      end


    PRINT @succ
    set nocount off


    return
    修改 删除 举报 引用 回复
    进入用户个人空间
    加为好友
    发送私信
    在线聊天
    • volcanofifa
    • 等级:
    • 可用分等级：
    • 总技术分：
    • 总技术分排名：
    发表于：2008-08-27 14:03:373楼 得分:0
    短時間看完會累死的
    修改 删除 举报 引用 回复
    网站简介－广告服务－网站地图－帮助－联系方式－诚聘英才－English－ 问题报告
    北京创新乐知广告有限公司 版权所有 京 ICP 证 070598 号
    世纪乐知(北京)网络技术有限公司 提供技术支持
    Copyright © 2000-2008, CSDN.NET, All Rights Reserved