EXEC sp_executesql is slow

Question

I have a stored procedure like this:

declare @where nvarchar(max)

set @where=' where 1=1 '
if(@IsSimNha is not null)
    set @where+=' and s.IsSimNha=@IsSimNha'
if(@IsSearchSimPSC is not null)
    set @where+=' and s.HanPhaiPhatSinhLai<=@HanPhaiPhatSinhLai'
if(@GiaTu is not null)
    set @where+=' and s.AgentPrice>=@GiaTu'
if(@GiaDen is not null)
    set @where+=' and s.AgentPrice<=@GiaDen'
if(@AgentID is not null)
    set @where+=' and s.AgentId=@AgentID'
if(@NhaMangId is not null)
    set @where+=' and s.NhaMangId=@NhaMangId'
if(@TinhTrangID is not null)
    set @where+=' and s.StatusId=@TinhTrangID'
if(@NgayNhapTu is not null)
    set @where+=' and s.NgayNhapSim>=@NgayNhapTu'
if(@NgayNhapDen is not null)
    set @where+=' and s.NgayNhapSim<=@NgayNhapDen'

if(@NgayPscTu is not null)
    set @where+=' and s.NgayPhatSinhCuoc>=@NgayPscTu'
if(@NgayPscDen is not null)
    set @where+=' and s.NgayPhatSinhCuoc<=@NgayPscDen'

if(@HanPSCTu is not null)
    set @where+=' and s.HanPhaiPhatSinhLai>=@HanPSCTu'
if(@HanPSCDen is not null)
    set @where+=' and s.HanPhaiPhatSinhLai<=@HanPSCDen'

if(@HanSuDungTu is not null)
    set @where+=' and s.HanSuDung>=@HanSuDungTu'
if(@HanSuDungDen is not null)
    set @where+=' and s.HanSuDung<=@HanSuDungDen'
if(@SoSeri is not null)
    set @where+=' and s.SoSeri<=@SoSeri'
if(@Lenght is not null)
    set @where+=' and LEN(s.Number) = @Lenght'
if(@SearchDau is not null)
    set @where+=' and s.Number like @SearchDau+''%'''
if(@SearchGiua is not null)
    set @where+=' and s.Number like ''%''+@SearchGiua+''%'''
if(@SearchCuoi is not null)
    set @where+=' and s.Number like ''%''+@SearchCuoi'

set @sql = 'declare @TotalItemCount int;'
set @sql+= ' select  @TotalItemCount=count(1)
            from sims s with (NOLOCK) '
            +@where
set @sql+=' select @TotalItemCount TotalItemCount, s.SimId,s.Number, s.DisplayNumber, s.AgentId,ag.Email,ag.Mobile2,s.NhaMangId,s.StatusId,s.SalelPrice,
s.SaleOffPrice,s.Commisions,s.AgentPrice,s.HoaHong,s.IsShow,s.DateCreated,ag.AgentName,s.SoSeri,s.SimTypeId,
s.NgayPhatSinhCuoc,s.HanPhaiPhatSinhLai,s.MaCaNhan,s.SoPhatSinhCuoc,s.HanSuDung,s.GhiChu,s.SoTienCamKet,s.SoThangCamKet,
s.NgayKichHoat, s.NgayNhapSim,s.IsSimNha,s.CoHoTroTraGop,s.GhiChuTraGop,s.ViTriId
from sims s with (NOLOCK) left join Agents ag with (NOLOCK) on s.AgentID=ag.AgentID ' +@where

declare @orderbysql nvarchar(max);

if(@OrderBy='SalelPrice')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.SalelPrice asc '
    else set @orderbysql= N' ORDER BY s.SalelPrice desc '
end
else if(@OrderBy='SaleOffPrice')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.SaleOffPrice asc '
    else set @orderbysql= N' ORDER BY s.SaleOffPrice desc '
end
else if(@OrderBy='AgentPrice')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.AgentPrice asc '
    else set @orderbysql= N' ORDER BY s.AgentPrice desc '
end
else if(@OrderBy='NgayPSC')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.NgayPSC asc '
    else set @orderbysql= N' ORDER BY s.NgayPSC desc '
end
else if(@OrderBy='HanPhatSinhCuoc')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.HanPhatSinhCuoc asc '
    else set @orderbysql= N' ORDER BY s.HanPhatSinhCuoc desc '
end 
else if(@OrderBy='HanSuDung')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.HanSuDung asc '
    else set @orderbysql= N' ORDER BY s.HanSuDung desc '
end 
else if(@OrderBy='NgayNhapSim')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.NgayNhapSim asc '
    else set @orderbysql= N' ORDER BY s.NgayNhapSim desc '
end 
else if(@OrderBy='Number')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.Number asc '
    else set @orderbysql= N' ORDER BY s.Number desc '
end 
else if(@OrderBy='TenDaiLy')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.TenDaiLy asc '
    else set @orderbysql= N' ORDER BY s.TenDaiLy desc '
end 
else if(@OrderBy='SoSeri')
begin
    if(@SortBy=1)
        set @orderbysql= N' ORDER BY s.SoSeri asc '
    else set @orderbysql= N' ORDER BY s.SoSeri desc '
end 
else
begin
    if(@SortBy=1)
    begin
        if(@IsSimNha=1)
            set @orderbysql= N' ORDER BY s.HanPhatSinhCuoc asc '
        else set @orderbysql= N' ORDER BY s.SimId asc '
    end
    else
    begin
        if(@IsSimNha=1)
            set @orderbysql= N' ORDER BY s.HanPhatSinhCuoc desc '
        else set @orderbysql= N' ORDER BY s.SimId desc '
    end

end
set @orderbysql+=' OFFSET (@PageNum-1)* @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY'
set @sql+=@orderbysql

print @sql
EXEC sp_executesql @sql,N'@IsSimNha bit,@IsSearchSimPSC bit, @HanPhaiPhatSinhLai datetime,@GiaTu decimal(18,0),@GiaDen decimal(18,0),@NhaMangId int,@AgentID int,
@TinhTrangID int,@NgayNhapTu datetime,@NgayNhapDen datetime,@NgayPscTu datetime,@NgayPscDen datetime,@HanPSCTu datetime,@HanPSCDen datetime,@HanSuDungTu datetime,
@HanSuDungDen datetime,@SoSeri nvarchar(30),@Lenght int,@SearchDau nvarchar(30),@SearchGiua nvarchar(30),@SearchCuoi nvarchar(30),@PageNum int,@PageSize int ',@IsSimNha ,@IsSearchSimPSC , @HanPhaiPhatSinhLai ,@GiaTu ,@GiaDen,@NhaMangId ,@AgentID ,
@TinhTrangID ,@NgayNhapTu ,@NgayNhapDen ,@NgayPscTu ,@NgayPscDen ,@HanPSCTu ,@HanPSCDen ,@HanSuDungTu ,
@HanSuDungDen ,@SoSeri ,@Lenght ,@SearchDau ,@SearchGiua ,@SearchCuoi,@PageNum,@PageSize

It takes more than 3 seconds to complete.

But when I take script result to run like this:

select 
    @TotalItemCount TotalItemCount, s.SimId,s.Number, s.DisplayNumber, 
    s.AgentId, ag.Email, ag.Mobile2, s.NhaMangId, s.StatusId, s.SalelPrice,
    s.SaleOffPrice, s.Commisions, s.AgentPrice, s.HoaHong, s.IsShow, 
    s.DateCreated, ag.AgentName, s.SoSeri, s.SimTypeId,
    s.NgayPhatSinhCuoc, s.HanPhaiPhatSinhLai, s.MaCaNhan, s.SoPhatSinhCuoc, 
    s.HanSuDung, s.GhiChu, s.SoTienCamKet, s.SoThangCamKet,
    s.NgayKichHoat, s.NgayNhapSim, s.IsSimNha, s.CoHoTroTraGop, 
    s.GhiChuTraGop, s.ViTriId
from 
    sims s with (NOLOCK) 
left join 
    Agents ag with (NOLOCK) on s.AgentID = ag.AgentID 
where  
    s.Number like '0973688639'+'%' 
order by
    s.SimId desc  
    OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

and runs, it's take only 0.02s

I don't know why it's slow like that.

Note: @sql variable = second script.

Somebody can help me?

Thanks all

This is sometimes due to the execution plan being cached. You might try adding `OPTION (RECOMPILE)` to the query string. Alternatively, the second run might be faster simply because the data is already loaded into memory (warm cache versus cold cache). — Gordon Linoff, Feb 26 '17 at 15:08
I clear sql server cache query and run the second script it also faster the first script. Thank you!!! I add OPTION (RECOMPILE) to script but it's not improved — Dan LE, Feb 26 '17 at 15:16
Just a guess since you didn't post DDL but are the table columns varchar? If so, table indexes cannot be used efficiently because the parameters are declared as nvarchar. — Dan Guzman, Feb 26 '17 at 15:16

SqlZim · Answer 1 · 2017-02-26T17:09:20.863

Three things:

Add option (recompile)
Get the count of all rows in sims using DMVs
It looks like you are only pulling ag.Email,ag.Mobile2 from the Agents table, and if that does not return more than 1 row (only 1 or 0 rows), then you can try an alternative pattern for using offset ... fetch described here: Pagination with offset / fetch : A better way - Aaron Bertrand

1) option (recompile)

after this line:

set @sql+=@orderbysql ..

add this line:

set @sql+= ' option (recompile);';

2) using the DMVs to get the row count for the table.

Swap out this:

...

set @sql = 'declare @TotalItemCount int;'
set @sql+= ' select  @TotalItemCount=count(1)
            from sims s with (NOLOCK) '
            +@where
set @sql+=' select @TotalItemCount TotalItemCount, s.SimId,s.Number, s.DisplayNumber, s.AgentId,ag.Email,ag.Mobile2,s.NhaMangId,s.StatusId,s.SalelPrice,
s.SaleOffPrice,s.Commisions,s.AgentPrice,s.HoaHong,s.IsShow,s.DateCreated,ag.AgentName,s.SoSeri,s.SimTypeId,
s.NgayPhatSinhCuoc,s.HanPhaiPhatSinhLai,s.MaCaNhan,s.SoPhatSinhCuoc,s.HanSuDung,s.GhiChu,s.SoTienCamKet,s.SoThangCamKet,
s.NgayKichHoat, s.NgayNhapSim,s.IsSimNha,s.CoHoTroTraGop,s.GhiChuTraGop,s.ViTriId
from sims s with (NOLOCK) left join Agents ag with (NOLOCK) on s.AgentID=ag.AgentID ' +@where

...

EXEC sp_executesql @sql,N'@IsSimNha bit,@IsSearchSimPSC bit, @HanPhaiPhatSinhLai datetime,@GiaTu decimal(18,0),@GiaDen decimal(18,0),@NhaMangId int,@AgentID int,
@TinhTrangID int,@NgayNhapTu datetime,@NgayNhapDen datetime,@NgayPscTu datetime,@NgayPscDen datetime,@HanPSCTu datetime,@HanPSCDen datetime,@HanSuDungTu datetime,
@HanSuDungDen datetime,@SoSeri nvarchar(30),@Lenght int,@SearchDau nvarchar(30),@SearchGiua nvarchar(30),@SearchCuoi nvarchar(30),@PageNum int,@PageSize int ',@IsSimNha ,@IsSearchSimPSC , @HanPhaiPhatSinhLai ,@GiaTu ,@GiaDen,@NhaMangId ,@AgentID ,
@TinhTrangID ,@NgayNhapTu ,@NgayNhapDen ,@NgayPscTu ,@NgayPscDen ,@HanPSCTu ,@HanPSCDen ,@HanSuDungTu ,
@HanSuDungDen ,@SoSeri ,@Lenght ,@SearchDau ,@SearchGiua ,@SearchCuoi,@PageNum,@PageSize

for this:

...

declare @TotalItemCount int;
select @TotalItemCount=p.rows
from sys.indexes i
  inner join sys.partitions p
    on p.object_id = i.object_id
      and p.index_id = i.index_id
 where i.object_id = object_id(N'dbo.sims')
   and i.index_id < 2;

set @sql=' select @TotalItemCount as TotalItemCount, s.SimId,s.Number, s.DisplayNumber, s.AgentId,ag.Email,ag.Mobile2,s.NhaMangId,s.StatusId,s.SalelPrice,
s.SaleOffPrice,s.Commisions,s.AgentPrice,s.HoaHong,s.IsShow,s.DateCreated,ag.AgentName,s.SoSeri,s.SimTypeId,
s.NgayPhatSinhCuoc,s.HanPhaiPhatSinhLai,s.MaCaNhan,s.SoPhatSinhCuoc,s.HanSuDung,s.GhiChu,s.SoTienCamKet,s.SoThangCamKet,
s.NgayKichHoat, s.NgayNhapSim,s.IsSimNha,s.CoHoTroTraGop,s.GhiChuTraGop,s.ViTriId
from sims s with (NOLOCK) left join Agents ag with (NOLOCK) on s.AgentID=ag.AgentID ' +@where

...

EXEC sp_executesql @sql
,N'@IsSimNha bit,@IsSearchSimPSC bit, @HanPhaiPhatSinhLai datetime,@GiaTu decimal(18,0),@GiaDen decimal(18,0),@NhaMangId int,@AgentID int,
  @TinhTrangID int,@NgayNhapTu datetime,@NgayNhapDen datetime,@NgayPscTu datetime,@NgayPscDen datetime,@HanPSCTu datetime,@HanPSCDen datetime,@HanSuDungTu datetime,
  @HanSuDungDen datetime,@SoSeri nvarchar(30),@Lenght int,@SearchDau nvarchar(30),@SearchGiua nvarchar(30),@SearchCuoi nvarchar(30),@PageNum int,@PageSize int, @TotalItemCount bigint'
,@IsSimNha ,@IsSearchSimPSC , @HanPhaiPhatSinhLai ,@GiaTu ,@GiaDen,@NhaMangId ,@AgentID ,
  @TinhTrangID ,@NgayNhapTu ,@NgayNhapDen ,@NgayPscTu ,@NgayPscDen ,@HanPSCTu ,@HanPSCDen ,@HanSuDungTu ,
  @HanSuDungDen ,@SoSeri ,@Lenght ,@SearchDau ,@SearchGiua ,@SearchCuoi,@PageNum,@PageSize,@TotalItemCount

3) An alternative pattern for using offset ... fetch described here: Pagination with offset / fetch : A better way - Aaron Bertrand

The goal is to reorganize your dynamic sql to generate the query in this pattern instead of the present pattern:

This is going to require the most refactoring of your current code, so if the first two changes do not help enough, this is worth testing:.

;with cte as 
    (
      select SimId /* just the key column */
      from dbo.sims
      where ...
      order by ...
      offset @PageSize * (@PageNumber - 1) rows
      fetch next @PageSize rows only
    )
    select column_list...
      from dbo.sim as s
        inner join cte on s.SimId = cte.SimId -- or exists
        left join Agents ag ...
      order by ...
      option (recompile);

That is a new solution, but I feel weird in my code. I do not know why? :) — Dan LE, Feb 27 '17 at 07:07
Hi SqlZim, I just do like your tricks. but when i try run script. It's worse. — Dan LE, Feb 27 '17 at 17:03
@DanLE Share your execution plans using [Paste The Plan @ brentozar.com](https://www.brentozar.com/pastetheplan/) here are the instructions: [How to Use Paste the Plan](https://www.brentozar.com/pastetheplan/instructions/). — SqlZim, Feb 27 '17 at 17:06

score 0 · Answer 2 · answered Nov 16 '17 at 20:51

I suggest that you take a deep look to your data types, specially nvarchar, I recall multiple issues with execution plans that utilize index scan instead of index seek just because the data types do not match your indexes.

EG (ASP.NET C#):

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT [COLUMN2] FROM [TABLE] WHERE COLUMN1 = @value";
command.Parameters.AddWithValue("@value", orderNumber);


exec sp_executesql N'SELECT [COLUMN2] FROM [TABLE] WHERE COLUMN1 = @Value',N'@Value nvarchar(32)',@Value=N'b12af2381c8a40e7aaf6f682e209b2ed'

this is wrong because our table use varchar[32] for the COLUMN1 and this generate by default a query with NVARCHAR[32] type. The right way is to specify the type:

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT [COLUMN2] FROM [TABLE] WHERE COLUMN1 = @value";
command.Parameters.Add("@value", SqlDbType.VarChar, 32);
command.Parameters["@value"].Value = orderNumber; enter code here


exec sp_executesql N'SELECT [COLUMN2] FROM [TABLE] WHERE COLUMN1 = @Value',N'@Value varchar(32)',@Value=N'b12af2381c8a40e7aaf6f682e209b2ed'

believe it or not this make a huge difference in performance depending on your table size and indexes.

Hope it helps,

EXEC sp_executesql is slow

2 Answers2