EXEC sp_executesql медленно

У меня есть хранимая процедура, как это:

объявить @where nvarchar (макс.)

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

Это займет более 3 секунд.

Но когда я беру результат скрипта для запуска так:

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

и работает, это займет всего 0,02 с

Я не знаю, почему это так медленно.

Примечание: переменная @sql = второй скрипт.

Кто-нибудь может мне помочь?

Спасибо всем

Источник

2 ответа

Три вещи:

  1. добавлять option (recompile)
  2. Получить количество всех строк в sims используя DMV
  3. Похоже, вы только извлекаете ag.Email,ag.Mobile2 из таблицы Агентов, и если это не возвращает более 1 строки (только 1 или 0 строк), то вы можете попробовать альтернативный шаблон для использования offset ... fetch здесь описано: нумерация страниц с offset / fetch : Лучший путь - Аарон Бертран

1) option (recompile)

после этой строки:

set @sql+=@orderbysql ..

добавьте эту строку:

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

2) использование DMV для получения количества строк в таблице.

Поменяйте это:

...

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

за это:

...

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) Альтернативный шаблон для использования offset ... fetch здесь описано: нумерация страниц с offset / fetch : Лучший путь - Аарон Бертран

Цель состоит в том, чтобы реорганизовать ваш динамический sql для генерации запроса в этом шаблоне вместо существующего шаблона:

Это потребует большей части рефакторинга вашего текущего кода, поэтому, если первые два изменения не помогут, это стоит проверить:

;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);
Источник

Я предлагаю вам внимательно изучить ваши типы данных, особенно nvarchar. Я вспоминаю несколько проблем с планами выполнения, которые используют сканирование индекса вместо поиска индекса только потому, что типы данных не соответствуют вашим индексам.

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'

это неправильно, потому что наша таблица использует varchar[32] для COLUMN1, и это генерирует по умолчанию запрос с типом NVARCHAR[32]. Правильный способ - указать тип:

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'

хотите верьте, хотите нет, но это сильно влияет на производительность в зависимости от размера таблицы и показателей.

Надеюсь, поможет,

Источник
Другие вопросы по тегам