SQL Server 优化存储过程中的代码

Question

比较下面两段代码，都是一样的，只是有细微的差别:

ALTER procedure [dbo].[SP_USUARIOS_UPDATE]
    @usu_ds varchar(100),
    @usu_dt_lst_log datetime,
    @usu_ds_senha varchar(255),
    @usu_ds_email varchar(100)
    as
    begin
    declare @usu_ID int;
    create table #TempUser
    (
        UsuID int,
        Senha varchar(255),
        Email varchar(100)
    )
    select Usuarios.usu_ID as UsuID,Usuarios.usu_ds_senha as Senha, 
    Usuarios.usu_ds_email as Email into #TempUser from Usuarios where Usuarios.usu_ds = @usu_ds
    if(@usu_ds_senha is null)
    begin
    set @usu_ds_senha = (select #TempUser.Senha from #TempUser);
    end
    if(@usu_ds_email is null)
    begin
    set @usu_ds_email = (select #TempUser.Email from #TempUser);
    end
    set @usu_ID = (select #TempUser.UsuID from #TempUser);
    update Usuarios set usu_dt_lst_log = 
    @usu_dt_lst_log,usu_ds_senha = @usu_ds_senha,usu_ds_email = @usu_ds_email where usu_ID = @usu_ID
    end

和

ALTER procedure [dbo].[SP_USUARIOS_UPDATE]
@usu_ds varchar(100),
@usu_dt_lst_log datetime,
@usu_ds_senha varchar(255),
@usu_ds_email varchar(100)
as
begin
declare @usu_ID int;
if(@usu_ds_senha is null)
begin
set @usu_ds_senha = (select Usuarios.usu_ds_senha from Usuarios where Usuarios.usu_ds = @usu_ds);
end
if(@usu_ds_email is null)
begin
set @usu_ds_email = (select Usuarios.usu_ds_email from Usuarios where Usuarios.usu_ds = @usu_ds);
end
set @usu_ID = (select Usuarios.UsuID from Usuarios where Usuarios.usu_ds = @usu_ds);
update Usuarios set usu_dt_lst_log = 
@usu_dt_lst_log,usu_ds_senha = @usu_ds_senha,usu_ds_email = @usu_ds_email where usu_ID = @usu_ID
end

你认为第一个在性能上比第二个快吗，我的意思是，第一个代码使用临时表(#TempUser)从真实表中存储 3 个字段。第二个代码，从真实表中一个一个地选择所有字段。

什么代码最优化？

Answer 1

首先——如果您传递的参数可能为空，您需要设置默认值。例如:

@usu_ds_email varchar(100) = null
...

否则，您进一步向下的空检查将永远不会发挥作用——该过程只会失败。

其次——直接运行更新。看起来你在不必要地来回推送大量数据。例如，您不需要从要更新的表创建临时表，然后右转并从刚刚创建的临时表更新您的表。

ALTER procedure [dbo].[SP_USUARIOS_UPDATE]
@usu_ds varchar(100),
@usu_dt_lst_log datetime,
@usu_ds_senha varchar(255) = null,
@usu_ds_email varchar(100) = null
as
begin

    update Usuarios 
    set usu_dt_lst_log = @usu_dt_lst_log,
        usu_ds_senha = isnull(@usu_ds_senha, usu_ds_senha),
        usu_ds_email = isnull(@usu_ds_email, usu_ds_email)
    where usu_ID = @usu_ds

end