none
SQL Server中,存储过程执行速度比较慢,有优化的方案吗?

全部回复

  • Hi Marry_Xia,

    感谢你在MSDN论坛发帖.

    考虑过使用多线程来存储数据吗? 下面这个demo,就是并发的存储数据.

    https://www.codeproject.com/Articles/67499/Execute-Stored-Procedures-in-Parallel

    希望对你有所帮助。

    Best Regards,

    Hart


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年3月28日 7:11
    版主
  • hi Hart Cheng,我是在数据库中去执行存储过程也很慢,是sql语句需要优化。
    2017年3月28日 7:28
  • 首先看sql的执行计划

    http://feiyun0112.cnblogs.com/

    2017年3月28日 7:52
    版主
  • 您好,您可以稍微详细描述一下吗?我不是很懂您说的意思。

    ALTER PROC [dbo].[schooldifferent_proc] @DQDM varchar(20)
    AS
    declare @ZXXSame int -- 0:相等  1:不相等
    declare @orginalType int
    declare @cfxxdms varchar(max)
    --原始表
    DECLARE @o_ZXXXXDM VARCHAR(20)
    DECLARE @o_XXDM VARCHAR(20)
    DECLARE @o_CXFLDM smallint
    DECLARE @o_LSGXDM smallint
    DECLARE @o_DQDM VARCHAR(20)
    DECLARE @o_DQDMAll VARCHAR(20)
    DECLARE @o_JGS smallint
    DECLARE @o_XXLBDM smallint
    DECLARE @o_Ver smallint
    DECLARE @o_ChildXXDM VARCHAR(max)
    --中心校类别 0为常规,1为主题校,2为村小
    declare @o_ZXXLB smallint
    DECLARE @originalType smallint

    --导入表
    DECLARE @i_CXFLDM smallint
    DECLARE @i_LSGXDM smallint
    DECLARE @i_DQDM VARCHAR(20)
    DECLARE @i_DQDMAll VARCHAR(20)
    DECLARE @i_JGS smallint
    DECLARE @i_XXLBDM smallint
    DECLARE @i_Ver smallint
    DECLARE @i_XXDM VARCHAR(20)
    DECLARE @i_ZXXXXDM VARCHAR(20)
    DECLARE @i_ChildXXDM VARCHAR(max)
    declare @i_ZXXLB smallint
    DECLARE @importType smallint

    --可拆分学校代码
    set @cfxxdms='412, 414, 415'
    --开始循环
    select @o_XXDM=min(o_XXDM) from schooldifferent WHERE o_dqdm LIKE ''+@DQDM+'%'
    while @o_XXDM is not null
    begin
    set @o_ChildXXDM=null
    set @i_ChildXXDM=null
    SELECT @o_XXDM=o_XXDM,@o_CXFLDM=o_CXFLDM,@o_LSGXDM=o_LSGXDM,@o_DQDM=o_DQDM,@o_DQDMAll=o_DQDMAll,@o_JGS=o_JGS,@o_XXLBDM=o_XXLBDM,@o_Ver=o_Ver,@o_ZXXXXDM=o_ZXXXXDM,@o_ZXXLB=o_ZXXLB,
    @i_XXDM=i_XXDM,@i_CXFLDM=i_CXFLDM,@i_LSGXDM=i_LSGXDM,@i_DQDM=i_DQDM,@i_DQDMAll=i_DQDMAll,@i_JGS=i_JGS,@i_XXLBDM=i_XXLBDM,@i_Ver=i_Ver,@i_ZXXXXDM=i_ZXXXXDM,@i_ZXXLB=i_ZXXLB
    from schooldifferent WHERE o_dqdm LIKE ''+@DQDM+'%'  and o_XXDM=@o_XXDM

    if(@o_XXLBDM<>@i_XXLBDM)
    begin
    if left(@o_XXLBDM,1)='1'
    begin
    set @orginalType=1
    end
    else
    begin
    set @orginalType=2
    end
    if left(@i_XXLBDM,1)='1'
    begin
    set @importType=1
    end
    else
    begin
    set @importType=2
    end
    if (@originalType <> @importType or charindex(convert(varchar(20),@o_XXLBDM),@cfxxdms) > 0 or charindex(
    convert(varchar(20),@i_XXLBDM),@cfxxdms) > 0)
    begin
    exec DeleteSchoolData_DataState_VaildTip_proc @o_XXDM
    end
    end
    if((@o_ZXXLB=0 or @o_ZXXLB=1) and @i_ZXXLB=2)
    begin
    exec DeleteSchoolData_DataState_VaildTip_proc @o_XXDM
    end

    if (isnull(@o_ZXXXXDM,'')<>'' and @o_ZXXXXDM<>@i_ZXXXXDM)
    begin
    exec DeleteSchoolData_DataState_VaildTip_proc @o_XXDM
    end

    IF (isnull(@o_ZXXXXDM,'')<>'' and @o_ZXXXXDM=@i_ZXXXXDM)
    BEGIN
    SELECT @o_ChildXXDM=ChildXXDM from schoolzxx where  XXDM=@o_XXDM
    SELECT @i_ChildXXDM=ChildXXDM from schoolimportzxx where XXDM=@o_XXDM
    if @o_ChildXXDM<>@i_ChildXXDM 
    begin
    set @ZXXSame=1
    end
    else
    begin
    set @ZXXSame=0
    end
    END
    IF(@ZXXSame=1 or @o_CXFLDM<>@i_CXFLDM or @o_LSGXDM<>@i_LSGXDM or @o_DQDM<>@i_DQDM
    or @o_DQDMAll<>@i_DQDMAll or @o_JGS<>@i_JGS or @o_XXLBDM<>@i_XXLBDM or @o_Ver<>@i_Ver)
    BEGIN
    UPDATE [dbo].[jtable_data_state] SET DQDM = @i_DQDMAll,State = 1,DataValid=0 WHERE State<>3 and DWDM = @i_XXDM
    UPDATE [dbo].[j1_1] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_2] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_2f1] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_2f2] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_3] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_3f1] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_3f2] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j1_4] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_1] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_2] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_2f] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_3] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_3f] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[j2_4] SET dqdm = @i_DQDMAll,lsgxdm = @i_LSGXDM,xxlbdm =@i_XXLBDM,cxfldm =@i_CXFLDM
    WHERE dwdm=@i_XXDM
    UPDATE [dbo].[validtip] SET DQDM = @i_DQDMAll WHERE DWDM = @i_XXDM
    END
    select @o_XXDM=min(o_XXDM) from schooldifferent WHERE o_dqdm LIKE ''+@DQDM+'%' and o_XXDM>@o_XXDM
    end

    这是我写的存储过程

    2017年3月28日 9:31
  • 有没有考虑建索引,提升下速度。通常是WHERE语句部分涉及的字段,这样可提高查询速度。


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2017年3月28日 9:58
  • 我看你的sql多次用到like,这个肯定是影响性能的,建议换种方式,比如对应数据先存入临时表

    当然最的方式还是更加执行计划分析


    http://feiyun0112.cnblogs.com/

    2017年3月29日 1:15
    版主