Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Есть ли возможность ускорить выполнение запроса?  [new]
harisma
Member

Откуда:
Сообщений: 243
В нашей программе динамически формируются запросы к базе данных, которая крутится на SQL Server.
Поскольку в таблице в поле, по которому происходит сортировка, может быть много данных со значением NULL, было принято решение модернизировать формирование запроса таким образом, чтобы при поиске по этому полю искомое значение (если оно там действительно есть), гарантировано было найдено.
Однако в процессе выполнения модернизированного запроса оказалось, что скорость его выполнения упала в 3-4 раза (было ~100, стало ~350), а количество чтений, по данным профайлера, выросла с 1895 до 46813.
Привожу ниже старый и новый скрипты. Основное их отличие - наличие в новом скрипте нового калькулируемого поля SortField, значения в котором получаются путем применения функции IsNULL для поля сортировки, а также указание порядкового номера поля SortField в условии сортировки, вместо явного указания имени поля сортировки
(скрипты выкушены из профайлера и немного упрощены, для уменьшения их размера, потому прошу извинить за их формат)
Можно ли как-то ускорить его выполнение?

Старый скрипт:
declare @p3 int
set @p3=34
exec sp_executesql N'declare @id char(16), @reccount int
set @id=''00BD912E9D39AF40''
declare @Str varchar(100)
select @Str = D.NAME from Dictionaries_C3AD665D2FD8E140 as D where
((D.HASCHILD = 0))
 and (D.recid = @ID)

set @reccount=@@ROWCOUNT
set @Str = IsNull(@Str, '''')
if (@reccount <> 0) begin
  declare cur cursor local scroll for
  select top 50 D.NAME, D.recid from Dictionaries_C3AD665D2FD8E140 as D
  where
((D.HASCHILD = 0)) and
((IsNull(D.NAME, '''') < @Str) or ((IsNull(D.NAME, '''') = @Str) and (D.recid <= @ID)))
order by D.NAME desc, D.recid desc
open cur
fetch last from cur into @Str, @ID
  set @P1 = @@CURSOR_ROWS
close cur
deallocate cur
set @Str = IsNull(@Str, '''')
select top 100 D.RECTYPE,D.RECID,D.HASCHILD,D.PARENTID,D.LINKTYPE,D.LINKID,D.USERSAVEID,D.SAVETS,D.CODE,D.NAME,D.F1_MTP,D.F1_MID,SDFN0.DEFNAME AS F1,D.F2,D.F14_MTP,D.F14_MID,SDFN1.DEFNAME AS F14,D.F16,D.F7,D.F4,D.F6_MTP,D.F6_MID,SDFN2.DEFNAME AS F6,D.F17,D.F9,D.F10,D.F11,D.F12,D.F13,F15=convert(decimal(28,5),0),F18=convert(decimal(28,5),0),D.F19,D.F20_MTP,D.F20_MID,SDFN3.DEFNAME AS F20,D.F22,D.F24,D.F25,D.F26,D.F27,D.F28,D.F29,D.F30,D.F32,D.F31,D.F44,D.F45_MTP,D.F45_MID,SDFN4.DEFNAME AS F45,D.F46,D.F47 FROM Dictionaries_C3AD665D2FD8E140 AS D
WITH(INDEX(NAME))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN0 ON ((SDFN0.MEANTYPE=D.F1_MTP) AND (SDFN0.MEANID=D.F1_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN1 ON ((SDFN1.MEANTYPE=D.F14_MTP) AND (SDFN1.MEANID=D.F14_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN2 ON ((SDFN2.MEANTYPE=D.F6_MTP) AND (SDFN2.MEANID=D.F6_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN3 ON ((SDFN3.MEANTYPE=D.F20_MTP) AND (SDFN3.MEANID=D.F20_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN4 ON ((SDFN4.MEANTYPE=D.F45_MTP) AND (SDFN4.MEANID=D.F45_MID))
where
((D.HASCHILD = 0))
 and ((IsNull(D.NAME, '''') > @Str) or ((IsNull(D.NAME, '''') = @Str) and (D.recid >= @ID)))
order by D.NAME asc, D.recid asc
end else
select top 100 D.RECTYPE,D.RECID,D.HASCHILD,D.PARENTID,D.LINKTYPE,D.LINKID,D.USERSAVEID,D.SAVETS,D.CODE,D.NAME,D.F1_MTP,D.F1_MID,SDFN0.DEFNAME AS F1,D.F2,D.F14_MTP,D.F14_MID,SDFN1.DEFNAME AS F14,D.F16,D.F7,D.F4,D.F6_MTP,D.F6_MID,SDFN2.DEFNAME AS F6,D.F17,D.F9,D.F10,D.F11,D.F12,D.F13,F15=convert(decimal(28,5),0),F18=convert(decimal(28,5),0),D.F19,D.F20_MTP,D.F20_MID,SDFN3.DEFNAME AS F20,D.F22,D.F24,D.F25,D.F26,D.F27,D.F28,D.F29,D.F30,D.F32,D.F31,D.F44,D.F45_MTP,D.F45_MID,SDFN4.DEFNAME AS F45,D.F46,D.F47 FROM Dictionaries_C3AD665D2FD8E140 AS D
WITH(INDEX(NAME))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN0 ON ((SDFN0.MEANTYPE=D.F1_MTP) AND (SDFN0.MEANID=D.F1_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN1 ON ((SDFN1.MEANTYPE=D.F14_MTP) AND (SDFN1.MEANID=D.F14_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN2 ON ((SDFN2.MEANTYPE=D.F6_MTP) AND (SDFN2.MEANID=D.F6_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN3 ON ((SDFN3.MEANTYPE=D.F20_MTP) AND (SDFN3.MEANID=D.F20_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN4 ON ((SDFN4.MEANTYPE=D.F45_MTP) AND (SDFN4.MEANID=D.F45_MID))
where
((D.HASCHILD = 0))
order by D.NAME asc, D.recid asc
',N'@P1 int OUTPUT',@p3 output
select @p3

Новый скрипт:
declare @p3 int
set @p3=34
exec sp_executesql N'declare @id char(16), @reccount int
set @id=''00BD912E9D39AF40''
declare @Str varchar(100)
select @Str = D.NAME from Dictionaries_C3AD665D2FD8E140 as D where
((D.HASCHILD = 0))
 and (D.recid = @ID)

set @reccount=@@ROWCOUNT
set @Str = IsNull(@Str, '''')
if (@reccount <> 0) begin
  declare cur cursor local scroll for
  select top 50 IsNull(D.NAME, ''''), D.recid from Dictionaries_C3AD665D2FD8E140 as D
  where
((D.HASCHILD = 0)) and
((IsNull(D.NAME, '''') < @Str) or ((IsNull(D.NAME, '''') = @Str) and (D.recid <= @ID)))
order by 1 desc, D.recid desc
open cur
fetch last from cur into @Str, @ID
  set @P1 = @@CURSOR_ROWS
close cur
deallocate cur
set @Str = IsNull(@Str, '''')
select top 100 D.RECTYPE,D.RECID,D.HASCHILD,D.PARENTID,D.LINKTYPE,D.LINKID,D.USERSAVEID,D.SAVETS,D.CODE,D.NAME,D.F1_MTP,D.F1_MID,SDFN0.DEFNAME AS F1,D.F2,D.F14_MTP,D.F14_MID,SDFN1.DEFNAME AS F14,D.F16,D.F7,D.F4,D.F6_MTP,D.F6_MID,SDFN2.DEFNAME AS F6,D.F17,D.F9,D.F10,D.F11,D.F12,D.F13,F15=convert(decimal(28,5),0),F18=convert(decimal(28,5),0),D.F19,D.F20_MTP,D.F20_MID,SDFN3.DEFNAME AS F20,D.F22,D.F24,D.F25,D.F26,D.F27,D.F28,D.F29,D.F30,D.F32,D.F31,D.F44,D.F45_MTP,D.F45_MID,SDFN4.DEFNAME AS F45,D.F46,D.F47,  IsNull(D.NAME ,'''') as SortField  FROM Dictionaries_C3AD665D2FD8E140 AS D
WITH(INDEX(NAME))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN0 ON ((SDFN0.MEANTYPE=D.F1_MTP) AND (SDFN0.MEANID=D.F1_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN1 ON ((SDFN1.MEANTYPE=D.F14_MTP) AND (SDFN1.MEANID=D.F14_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN2 ON ((SDFN2.MEANTYPE=D.F6_MTP) AND (SDFN2.MEANID=D.F6_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN3 ON ((SDFN3.MEANTYPE=D.F20_MTP) AND (SDFN3.MEANID=D.F20_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN4 ON ((SDFN4.MEANTYPE=D.F45_MTP) AND (SDFN4.MEANID=D.F45_MID))
where
((D.HASCHILD = 0))
 and ((IsNull(D.NAME, '''') > @Str) or ((IsNull(D.NAME, '''') = @Str) and (D.recid >= @ID)))
order by 52 asc, D.recid asc
end else
select top 100 D.RECTYPE,D.RECID,D.HASCHILD,D.PARENTID,D.LINKTYPE,D.LINKID,D.USERSAVEID,D.SAVETS,D.CODE,D.NAME,D.F1_MTP,D.F1_MID,SDFN0.DEFNAME AS F1,D.F2,D.F14_MTP,D.F14_MID,SDFN1.DEFNAME AS F14,D.F16,D.F7,D.F4,D.F6_MTP,D.F6_MID,SDFN2.DEFNAME AS F6,D.F17,D.F9,D.F10,D.F11,D.F12,D.F13,F15=convert(decimal(28,5),0),F18=convert(decimal(28,5),0),D.F19,D.F20_MTP,D.F20_MID,SDFN3.DEFNAME AS F20,D.F22,D.F24,D.F25,D.F26,D.F27,D.F28,D.F29,D.F30,D.F32,D.F31,D.F44,D.F45_MTP,D.F45_MID,SDFN4.DEFNAME AS F45,D.F46,D.F47,  IsNull(D.NAME ,'''') as SortField  FROM Dictionaries_C3AD665D2FD8E140 AS D
WITH(INDEX(NAME))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN0 ON ((SDFN0.MEANTYPE=D.F1_MTP) AND (SDFN0.MEANID=D.F1_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN1 ON ((SDFN1.MEANTYPE=D.F14_MTP) AND (SDFN1.MEANID=D.F14_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN2 ON ((SDFN2.MEANTYPE=D.F6_MTP) AND (SDFN2.MEANID=D.F6_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN3 ON ((SDFN3.MEANTYPE=D.F20_MTP) AND (SDFN3.MEANID=D.F20_MID))
LEFT OUTER JOIN SYSTEM_DEFNAMES AS SDFN4 ON ((SDFN4.MEANTYPE=D.F45_MTP) AND (SDFN4.MEANID=D.F45_MID))
where
((D.HASCHILD = 0))
order by 52 asc, D.recid asc
',N'@P1 int OUTPUT',@p3 output
select @p3
6 сен 11, 13:46    [11234249]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить