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

Откуда:
Сообщений: 56
Всем доброго времени суток! Подскажите,сталкивался ли кто-нибудь с подобной проблемой и на что можно бы было обратить внимание. В общих чертах модель задачи:
есть cte

With ObjList as
 (
   select B.*
   from A -- список объектов, которые необходимо обработать  -размер 50000 записей
   inner join B on a.idObj=b.idObj-- список операций с объектами - довольно большая табличка 23млн записей, не секционирована
   left join
     (
       select idObj, Max(DateOper) as MaxDtOper
       from  C  -- таблица, в которую помещаются результаты некого расчета
                   --, пока не слишком заполнена данными, 200тыр записей..
       group by IdObj      
     ) LastOper on LastOper.idObj=B.idObj
  Cross apply
  (вычисляем предыдущую операцию) PrevOper
 
--Далее берем те записи что еще не попали в выборку С,
 -- или которые позже чем записи что там уже есть по соответствующим объектам  
 where  (B.DateoPER>c.MaxDtOper OR c.MaxDtOper is null)
     and B.Flag=1 
     and C.Flag=0 -- важна некая смена признака с 0 на 1цу... только такие операции мы берем
)


Сама выборка отрабатывает нормально -за 10 секунд, выдает 1400 записей..
Далее, если сделать выгрузку во временную таблицу
Select *
into #tmp
from ObjList 


а после сделать следующее
select
from #tmp
outer apply
  (
    вычисление по определенным критериям еще операций из большой таблицы B
  )  AdditionalInfo1
outer apply
  (
    вычисление по определенным критериям еще операций из большой таблицы B
  )  AdditionalInfo2

то выполняется за те же 10 секунд-т.к обработка 4тыр записей не особо ресурсозатратная...

Однако, если делать выборку сразу из cte, что собственно и подразумевалось изначально..
select
from ObjList 
outer apply
  (
    вычисление по определенным критериям еще операций из большой таблицы B
  )  AdditionalInfo1
outer apply
  (
    вычисление по определенным критериям еще операций из большой таблицы B
  )  AdditionalInfo2


то запрос работает 2-3 минуты, что неприемлемо(( Такое ощущение, что вместо того чтобы сделать выбокру внутри cte и после применить outer apply к полученным записям, он применяет его ко всем-в результате время выполнения запроса так сильно возрастает... пробовал Option(force order) для последнего селекта, не помогло... подскажите пожалуйста в какую сторону хотя бы копать-т.к решение через временную таблицу совсем не нравится, а хочется чтобы запрос выполнялся по времени так же как и с ней..)
12 фев 12, 13:51    [12076874]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
vadimman
решение через временную таблицу совсем не нравится, а хочется чтобы запрос выполнялся по времени так же как и с ней..)

1. Дык, кончай рассказывать - покажи личико (т.е. запрос), Гульчатай.

2. А так-то, за одно ЭТО
where  (B.DateoPER>c.MaxDtOper OR c.MaxDtOper is null)

надо лишать квартальной премии.

3. Я уж молчу про тупую группировку 200тыр записей.
12 фев 12, 14:04    [12076920]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
aleks2
2. А так-то, за одно ЭТО
where  (B.DateoPER>c.MaxDtOper OR c.MaxDtOper is null)

надо лишать квартальной премии.
А я вообще ничего не понимаю - разве с есть во FROMе??
Это выполняется?!
12 фев 12, 16:06    [12077393]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vadimman
Member

Откуда:
Сообщений: 56
aleks2 , меня просто поражает..
вы написали пост, а полезного в нем нет ни строки, и спрашиваешься зачем? думаете мне или кому-то этот набор букв будет полезен? или в данном случае нужно ваше мнение про квартальную премию? если можете что-то предложить по существу, напишите, я не гордый возьму на заметку, а пока это мягко говоря треп.
чем можно по вашему заменить группировку? row_number или top 1 в связке с order by в данном случае прироста не дадут.
2) Как можно убрать условие с OR ? при условии что из таблицы B берем только те строки, по котором ObjID еще не попал
в таблицу С или операции (таблица B содержит операции) которые еще не были обработаны -т.е были позже самой последней из С
3) не могу понять что даст вам название таблиц?? ну допустим
with OperSlice as (
select   h.ObjID
         , h.DateOper
         , h.Flag
         ,qSt.codeGroup
  from 
      dbo.ObjActual  CB with(nolock) 
      inner  join
      dbo.ObjOperHistory h with(nolock) on CB.ObjID= h.ObjID
  left join
  (
    select c.ObjID
           , Max(c.DateLoading) as MaxDateLoading
    from dbo.ObjectCycles c with(nolock)
    group by c.ObjID
  ) lc on h.ObjID= lc.ObjID
  cross apply  -- первая предыдущая операция
   (select top 1 hPrev.Flag
    from dbo.ObjOperHistory  hPrev with(nolock)
    where hPrev.ObjID=h.ObjID
      and (hPrev.DateOper> lc.MaxDateLoading or lc.MaxDateLoading is null)
      and hPrev.DateOper<h.DateOper
    order by DateOper desc
   ) PrevInf   
  left join qSt qStCur on qStCur.Code=h.Code  

  where (h.DateOper > lc.MaxDateLoading or lc.MaxDateLoading is null) 
        and h.DateOper >='20120101'  
        and h.flag=1  
        and PrevInf.flag=0
  )
 



 ----delete from #tmp
 --insert into #tmp
-- select os.* --into #tmp
 --from OperSlice os
 
 select os.*,RightInf.frst
 from --#tmp os
      OperSlice os
 outer apply
 (select MIN(DateOper) as frst --                               
  from ObjOperHistory  NextGroupOper with(nolock)
  left join qSt qStnext 
                     on qStnext.Code=NextGroupOper.Code 
   where NextGroupOper.ObjID=Os.ObjId
     and qStnext.CodeGroup<>os.CodeGroup
     and NextGroupOper.DateOper>os.DateOper
   )RightInf  
12 фев 12, 19:26    [12077975]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
vadimman
чем можно по вашему заменить группировку? row_number или top 1 в связке с order by в данном случае прироста не дадут.
2) Как можно убрать условие с OR ? при условии что из таблицы B берем только те строки, по котором ObjID еще не попал
в таблицу С или операции (таблица B содержит операции) которые еще не были обработаны -т.е были позже самой последней из С
3) не могу понять что даст вам название таблиц?? ну допустим

1) Зато даст прирост создание представления с кластерным индексом (Если конечно у вас не 200тыс различных идентификаторов). Да и outer apply в сочетании с top 1 + order by вполне может.
2) Тривиально. h.DateOper > ISNULL(lc.MaxDateLoading,0)
3) Название - ничего, а вот то, что "вычисление по определенным критериям еще операций из большой таблицы B" очень сильно похоже на "вычисляем предыдущую операцию" из CTE - может натолкнуть на размышления. Например о том, что эти выборки можно объединить. Ну и интереснее всего конечно даже не сам запрос, а его план, как с использованием временной таблицы, так и без.
12 фев 12, 20:56    [12078316]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iljy
vadimman
чем можно по вашему заменить группировку? row_number или top 1 в связке с order by в данном случае прироста не дадут.
2) Как можно убрать условие с OR ? при условии что из таблицы B берем только те строки, по котором ObjID еще не попал
в таблицу С или операции (таблица B содержит операции) которые еще не были обработаны -т.е были позже самой последней из С
3) не могу понять что даст вам название таблиц?? ну допустим

2) Тривиально. h.DateOper > ISNULL(lc.MaxDateLoading,0)
Зачем??
Достаточно h.DateOper > lc.MaxDateLoading добавить в ON LEFT JOINа - вооон туда, перед CROSS JOIN
12 фев 12, 21:49    [12078476]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
iljy
пропущено...

2) Тривиально. h.DateOper > ISNULL(lc.MaxDateLoading,0)
Зачем??
Достаточно h.DateOper > lc.MaxDateLoading добавить в ON LEFT JOINа - вооон туда, перед CROSS JOIN
CROSS APPLY, конечно же!
12 фев 12, 21:51    [12078481]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vadimman
Member

Откуда:
Сообщений: 56
iljy
на счет h.DateOper > ISNULL(lc.MaxDateLoading,0) с одной стороны согласен, с другой стороны, сейчас попробовал поменять в данном запросе условие на таковое, несмотря на то что query cost уменьшилась -время выполнения возрасло на 3 секунды.. проверил несколько раз.. хотя ваше решение мне то же кажется более правильным но похоже сервер строит свой план оптимизации в разных случаях по разному... так что я за то что пробывать надо и так и так)

А теперь про решение, которое нашел я за последние 2 часа) в общем похоже что force order не влияет на мой outer apply,
и в разделе (самая нижняя часть моего ответа aleks2)
 select os.*,RightInf.frst
 from --#tmp os
      OperSlice os
 outer apply
 (select MIN(DateOper) as frst --                               
  from ObjOperHistory  NextGroupOper with(nolock)
  left join qSt qStnext 
                     on qStnext.Code=NextGroupOper.Code 
   where NextGroupOper.ObjID=Os.ObjId
     and qStnext.CodeGroup<>os.CodeGroup
     and NextGroupOper.DateOper>os.DateOper
   )RightInf  

он производит вычисления для всех строк огромной ObjOperHistory, до выполнения условий where и сокращения числа записей... с одной стороны это логично, т.к Execution order (в случае без юниона) должен идти сперва (условия from, join,apply,on ) а уж после условие where (так уверяет microsoft в mcts self training kit book) однако там же и написано что
The theoretical execution order is referred to as “theoretical” because the optimizer might
change the actual execution order to optimize performance
так что в мол в теории так а на деле никому не известно, как он этот план там строит -НО если взять и сделать так:

1) весь код я вынес из cte
2) в top 1 раздела помеченным коментарием -- первая предыдущая операция записал теперь следующее
select top 1 hPrev.Flag , h.DateOper as CurOper
3) для конечного outer apply
сменил min на top 1
4) поменял в этом последнем outer apply NextGroupOper.DateOper> CurOper (вместо h.DateOper - он же os.DateOper)

в результате все стало работать так как и хотелось... т.е указав в запросе CurOper из другого блока, полагаю я не позволил серверу выполнять apply для всех операций в таблице ObjOperHistory в первую очередь, а только после джойнов и т.п ..

Но это только мое предположение... Очень бы хотелось услышать объяснение почему могло произойти так. Заранее спасибо
12 фев 12, 22:55    [12078767]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vadimman
Member

Откуда:
Сообщений: 56
...танцы с бубном блин)) пытаюсь щас логическую связь установить почему работает так... в общем проверил на счет top 1 вместо min -точно влияет... А вот использование сравнения NextGroupOper.DateOper> CurOper сейчас уже разницы не дает!!!
хоть с CurOper хоть с h.dateOper %) то ли он статистику какую уже построил... не пойму...завтра еще проверю конечный запрос-будет ли так же 10 секунд выполняться...) задача хоть и выполнена, очень хочется теперь для себя понять почему так а не иначе..)
12 фев 12, 23:16    [12078819]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
iap
iljy
пропущено...

2) Тривиально. h.DateOper > ISNULL(lc.MaxDateLoading,0)
Зачем??
Достаточно h.DateOper > lc.MaxDateLoading добавить в ON LEFT JOINа - вооон туда, перед CROSS JOIN

А, ну да, это на hPrev.DateOper> lc.MaxDateLoading or lc.MaxDateLoading is null смотрел.
13 фев 12, 00:05    [12079028]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
vadimman
...танцы с бубном блин))

Может вам в школу танцев? А планы выполнения посмотреть религия не позволяет?

Между прочим, иногда бывает лучше таки сделать через временную таблицу, в этом случае у сервера не рвет крышу с неправильными эстимейтами, хотя да, возможны какие то потери производительности на перекомпиляциях из-за временной таблицы.
13 фев 12, 06:00    [12079312]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
vadimman
чем можно по вашему заменить группировку?


1. При соотношении 200 000 в C и 1400 возвращаемых запросом даже коррелированный подзапрос будет эффективнее.

      
   ...
   dbo.ObjActual  CB with(nolock) 
      inner  join
   dbo.ObjOperHistory h with(nolock) on CB.ObjID= h.ObjID AND h.DateOper > ISNULL((select MAX(c.DateLoading) FROM dbo.ObjectCycles c WHERE h.ObjID= c.ObjID), cast(0 as datetime))
   cross apply  -- первая предыдущая операция
   ...

2.
vadimman
 from  C  -- таблица, в которую помещаются результаты некого расчета

А что мешает в C заранее исчислить Max(DateOper) и вопче не группировать - это выше моего понимания.

3. Ну, про кляузу ON для left join тут уже говорили.
13 фев 12, 08:42    [12079431]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vadimman
Member

Откуда:
Сообщений: 56
Mind, может быть вам лучше ничего не писать чем писать но ничего умного?, ваше мнение про танцы не уместно, план выполнения я смотрю всегда, не знаю какая у вас там религия, на меня ничто ограничения не накладывает.
то что можно сделать с временной табличкой я написал в первом еще посте-если по вашему это отличный вариант я не согласен..
если сможете что-то конерктное рассказать про то в какой последовательности сервер фомрирует запрос и как можно его заставить выполнять запрос в требуемом тебе порядке, с удовольствием выслушаю.. а пока...


aleks2 -поэтому заранее можно исчислить Max(DateOper) где? просто в переменную? если где-то на уровне таблиц -это не вариант в моем случае, я разработчик а не проектировщик базы, чтобы менять структуру дюже много надо бегать согласовывать, .. с чего вы взяли что именно этот запрос дает тормоза? я попробывал коррелированый подзапрос -время выполнения не изменилось.
Однако мне любопытно, почему вы считаете что он лучше чем джойн? как вы руководствуетесь когда используете его а когда подзапрос? -и самое главное как вы к этому пришли, опытным путем или допустим у майрософта есть где-то рекомендации в этом направлении? в их мсдн или технете?, если да то дайте пожалуйста ссылку
это все довольно любопытно, жду ваш ответ.
13 фев 12, 11:44    [12080329]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
vadimman
aleks2 -поэтому заранее можно исчислить Max(DateOper) где? просто в переменную?

+- одно или дюжина полей в таблице никакой погоды не делает.

vadimman
я разработчик а не проектировщик базы, чтобы менять структуру дюже много надо бегать согласовывать

Кому лехко?

vadimman
я попробывал коррелированый подзапрос -время выполнения не изменилось.

Вы просто не умеете их готовить.

vadimman
Однако мне любопытно, почему вы считаете что он лучше чем джойн?


Патаму, что "пока не слишком заполнена данными, 200тыр записей.." кагбе намекает, что записей могет стать больше.
А "список объектов, которые необходимо обработать -размер 50000 записей" кагбе вряд ли измениться.
И колбасить многомиллионную таблицу ради пусть 50000 записей - неудобно получается.

Короме того, результат GROUP BY суть куча, в которой нет индексов, и вся работа с ней - суть сканирование. Что тоже не есть фонтан. Поэтому, весьма часто, помещение результата GROUP BY в индексированную временную таблицу творит чудеса.
13 фев 12, 12:05    [12080551]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
vadimman
-поэтому заранее можно исчислить Max(DateOper) где? просто в переменную?

Еще раз - можно создать представление с кластерным индексом.
13 фев 12, 12:09    [12080590]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
iljy,

вернее не представление конечно (MAX не даст), а таблицу, которую поддерживать триггером.
13 фев 12, 12:14    [12080641]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vadimman
Member

Откуда:
Сообщений: 56
aleks2 , интересно, изучу этот вопрос по поводу group by...

iljy indexed view конечно поможет, можно весь этот запрос в нее обернуть, будет вообще быстро) -однако из-за того что в эти таблицы идут довольно добльшие вставки, никто это не одобрит и не позволит, про вычисления максимума, это да, как вариант, в случае если в будущем время выполнения на group by начнет расти возможно придется к нему прибегнуть.
13 фев 12, 17:57    [12083583]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить