SQL Server 表大概4百万条记录，如何优化提升 ‘按时间段条件’ 来获取查询记录的速度？

Question

--按时间段查询访问记录
CREATE PROCEDURE [dbo].[PROC_GetVisitLog]
(
    @sdt nvarchar(32)=null, --开始日期
    @edt nvarchar(32)=null --结束日期
)
AS
begin
    declare @sql nvarchar(512)
    set @sql='select Id,User,Url,ActionDesc,Status,Msg,StartTime,EndTime from VisitLog where 1=1 ';
    set @sql=(case when @sdt is null or @sdt='' then @sql else @sql+' and StartTime >=@sdt' end);
    set @sql=(case when @edt is null or @edt='' then @sql else @sql+' and StartTime<=@edt' end);
    set @sql=@sql+' order by Id desc';
    EXEC sp_executesql @sql,N'@sdt nvarchar(32),@edt nvarchar(32)',@sdt,@edt
end

Answer 1

加索引呀