Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
SomewhereSomehow
bormental,

я удивляюсь как с хинтом медленнее не стало, вы же просто его добавляете, игнорируя основную мысль, по поводу переупорядочивания соединений...
А с временной таблицей не пробовали?



Сорри, попробую доскональнее и внимательнее чуть позже - времени сегодня в обрез (
Сейчас вот навскидку сделал вроде как по Вашей идее...но что то тяжко выполняется это дело (.
Прилагаю...
Спасибо за участие.

К сообщению приложен файл (testsql.zip - 21Kb) cкачать
30 мар 12, 14:45    [12340498]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
bormental,

Судя по плану, безобразие твориться уже на этапе заполнения временной таблицы, померьте ради интереса время, сколько занимает заполнение таблицы и последующий запрос при помощи set statistics time on.
Еще, можно попробовать, убрать из условия соединения переменные, может быть по этому у него крышу так рвет с оценками? опять же можно попробовать форсировать порядок соединений и посмотреть что будет если выключить параллелизм...
померьте скорости разных вариантов
select
	   spo.F$NREC as sponrec 
	  ,spo.f$kol as kol
	  ,spo.f$srprice as spoprice
	  ,party.f$nrec as partynrec
      ,podrord.f$cgrpodr as cgrpodr
      ,skl.f$csopr as sklcspsopr
      ,spo.f$cmc as spocmc
      ,spo.f$vidorder vidorder
into #selspo
from 
	t$sporder spo 
	inner join #needpodr atri on spo.f$ccpodr=atri.f$crec
	inner join t$katpodr podrord on spo.f$ccpodr=podrord.f$nrec	
	inner join t$katparty party on spo.f$cparty = party.f$nrec	
	inner join t$sklorder skl on spo.f$csklorder=skl.f$nrec
where
	spo.f$sp = 0 and 
	spo.f$dord >= @bdate and 
	spo.f$dord <= @edate and	
	party.f$CORGPAR = @needorg and
	atri.f$vcomp in (@nreclevelo,@nreclevelp,@nreclevelu)
--option(force order)
--option(force order,maxdop 1)
30 мар 12, 15:36    [12341036]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

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

ок..попробую...пока же почекал кусок заполнения временной таблицы - не шустро там и вправду.
Но как тока добавляешь снизу Option(Recompile) так все сразу ок
В Общем ,присланном мной скрипте он тоже есть...но такое впечатление что в батче общем как то директива эта не воспринимается что ли на этапе заполнения времянки...
30 мар 12, 16:09    [12341395]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
bormental
после этого запустил тот же скрипт. Отработало где то за 3 минуты тоже самое. Но, каждый последующее выполнение уже делается меньше 30 секунд (менял параметры входящие специально...). Внесениие хинта inner loop join никак не влият после этого уже.
План новый прилагаю

30 секунд это уже нормально, или все еще медленно?

Я все таки не сторонник использовать никакие другие хинты кроме как OPTION(RECOMIPLE), так что вот еще мои 5 копеек, при условии что мы уже обновили статистику. В последнем плане (Good_Plan_after_updstat) сервер решает дважды обратится к самой большой таблице - T$SPORDER по 2-м разным индексам, потом еще следует весьма дорогой хэш джойн чтобы их объединить, а потом только лукап с целью наложить фильтр по f$dord и таки свести результат к положенным 8 тысячам строк. Задача в том, чтобы сделать все эти фильтры и оставить 8К как можно раньше, ну и конечно избавиться от 2х обращений к ORDER.

Мое предложение:
добавить в индекс T$SPORDER.T$SPORDER19 вот эти поля: f$sp,f$dord
Или же создать новый индекс по (f$cparty, f$sp, f$dord), желательно именно в таком порядке, для ускорения выборки, хотя возможны варианты. Если сервер не захочет использовать этот индекс, то будет очень странно, и буду чесать репу :)

Также в SKLORDER.SKLORDER0 я бы добавил INCLUDE(F$CSOPR), потому что это единственное поле в этой таблице из-за которого приходится делать лукап 8К раз. По желанию можно сделать тоже самое для индекса по KATSOPR, но нужно смотреть на размеры, делать индекс очень большим тоже не хорошо.

А кардиналити между SPORDER и KATPARTY все равно какое то кривое, но это похоже уже не исправить, у меня есть подозрение, что из 700К строк KATPARTY в SPORDER.cparty используется только порядка 148К уникальных значений, это действительно так?
31 мар 12, 11:14    [12344509]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

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

Трудно сказать нормально или медленно. По мне может и нормально, а местные гуру скажут что медленно ) Каковы вот критерии "нормально/медленно" тут ? ) В принципе, удовлетворителен результат в пределах минуты по мне (учитывая объемы данных и условия что не могу менять структуру физ таблиц.)
Как уже отмечали Выше,я не могу править структуры физических таблиц БД. Вариант,SomewhereSomehow , предложил перезагружать во времянку базовую выборку по sporder и строить нужные индексы(это к Вашему предложению подделать индексы к sporder).


Теперь насчет sporder&katparty дам некоторые выкладки

1. select COUNT(*) from t$katparty = 699234


2.
set @bdate=dbo.datetoint('20120201')
set @edate=dbo.datetoint('20120229')


(select distinct spo.f$cparty
from t$sporder spo inner join t$sklorder skl on spo.f$csklorder=skl.f$nrec and spo.f$sp=0 and spo.f$dord>=@bdate and spo.f$dord<=@edate)=142222


3.

(select distinct spo.f$cparty
from t$sporder spo inner join t$sklorder skl on spo.f$csklorder=skl.f$nrec and spo.f$sp=0 and spo.f$dord>=@bdate and spo.f$dord<=@edate
inner join t$katparty party on spo.f$cparty = party.f$nrec and party.f$CORGPAR=@needorg) ~10000

В sporder, как Вы понимаете , храниться ссылка только на каталог партий(katparty), в котором есть ссылка на контрагента (katparty.corgpar). Выборку нужно по определенному контрагенту именно.

Еще добавлю что есть в sporder индекс отдельно только по полю cparty. В свете этого может быть даже выигрышнее делать будет предвыборку из katparty по нужному corgpar и далее join-ить к этой выборке sporder с условиями ? :

select
....
from
(
select distinct f$nrec as nrecparty from t$katparty where f$corgpar=@needorg
) t1 inner join t$sporder spo on t1.nrecparty = spo.f$cparty and spo.f$dord >= @bdate and spo.f$dord <= @edate.......
31 мар 12, 11:49    [12344598]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
Да..еще была возможность вскольз погонять данный скрипт на другой совсем базе на другом предприятии совсем немного времени, скажем так (структура БД точь в точь такая же, но объем данным там даже больше процентов на 30 чем где я сейчас тесты прогонял). Дак вот. там без всяких хинтов и ухищрений выборка всегда формируется в пределах 20 секунд. Сервера сопоставимы по железу, оперативки одинаково. скуль также 2008.Вообщем, как только будет возможность - приведу план запроса с той БД.
31 мар 12, 11:53    [12344613]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind
30 секунд это уже нормально, или все еще медленно?

С хинтом нестед лупс было вроде менее 20 секунд? ;-)

Mind
Я все таки не сторонник использовать никакие другие хинты кроме как OPTION(RECOMIPLE)

И даже optimize for? ;-)

Mind
так что вот еще мои 5 копеек, при условии что мы уже обновили статистику. В последнем плане (Good_Plan_after_updstat) сервер решает дважды обратится к самой большой таблице - T$SPORDER по 2-м разным индексам, потом еще следует весьма дорогой хэш джойн чтобы их объединить, а потом только лукап с целью наложить фильтр по f$dord и таки свести результат к положенным 8 тысячам строк. Задача в том, чтобы сделать все эти фильтры и оставить 8К как можно раньше, ну и конечно избавиться от 2х обращений к ORDER.

Вот тут, полностью согласен, я пришел к тем же выводам, когда в первый раз смотрел на план. Однако, как этого добиться, без влияния на структуру БД?

Влиять на нее нельзя (или нежелательно) по причине того, что это бд и таблицы сторонней финансовой системы под названием Галактика. К ней регулярно поставляются апдейты (по крайней мере так мне сказали наши IT-шники), которые могут делать все что угодно. Предполагая худшее, могут и влиять на структуру БД. Еще немаловажный аспект, т.к. система закрытая, меняя схему, можем наткнуться на то, что к примеру, перестанут выполняться штатные запросы. Например, оптимизатор подхватит новый индекс в совершенно неожиданном месте, там где это не надо и время выполнения превысит таймаут команды, для пользователей это будет выглядеть как - вы сломали нам систему, а учитывая что в ней производятся самые критичные для компании действия, например выставление счетов, расчет зарплат и т.д. - то это чревато...
У меня была задача интеграции с этой системой (правда у нас она крутится на Pervasive SQL) и лично я наоборот попросил наш IT урезать мне все права кроме как на выборку, чтобы нести минимальную ответственность если что-то поломается, нуивонафик.
Остается хинтование, либо манипуляция промежуточными результатами запроса.

Обновить статистику - всегда хорошая идея, если что-то идет не так. Но судя по планам, которые автор прилагал после обновления статистики, дело не столько в ней, сколько в том, что сервер выбирает не правильный порядок соединений, потому что делает неверные оценки. Может быть, следует обновить статистику вообще по всем таблицам в запросе и это поможет?

Но если это не поможет и мы отбросим неверные оценки по причине статистики, то возможно неверные оценки идут из-за того, что в условии on участвуют параметры (или хуже - локальные переменные), тогда это объясняет почему при option recompile сервер может более точно оценить кардиналити. Также не следует, имхо, сбрасывать со счетов количество таблиц. Все таки, их довольно много и довольно объемных, с множеством индексов.

Я бы пошел по пути определения узкого места, с которого начинаются расхождения actual и estimated и судя по всему мы его нашли в подзапросе к T$SPORDER. Осталось заставить сервер, вместо спулинга или многократного сканирования многомиллионной таблицы, сделать выборку 8000 записей, после чего успешно ее заюзать в остальном запросе. По этому мне так интересны эксперименты автора.

Может быть следует вообще выдернуть ее из подзапроса, сделать выборку во временную таблицу только из нее, сделать по временной индексы и джойнить ее со всеми остальными, ибо логика по словам ТС такая.

В общем, понимание проблемы есть, но для ее устранения, требуются активные эксперименты.
31 мар 12, 20:34    [12345816]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
bormental
Mind,

Трудно сказать нормально или медленно. По мне может и нормально, а местные гуру скажут что медленно ) Каковы вот критерии "нормально/медленно" тут ? )

Чтобы с работы не уволили, критерий один - довольные пользователи!
Ну и желательно чтобы созданный план был достаточно стабильным и не слетел при малейшем изменении в статистиках. Кстати после того как вы сделали UPDATE STATISTICS ... WITH FULLSCAN, вы подписались на то чтобы обновлять статистику по этим таблицам вручную, иначе слудующее автоматическое обновление пересчитает статистику с default sample rate и так как у нас стоит OPTION(RECOMPILE), то план будет тут же обновлен на плохой.
Чтобы этого избежать, для начала нужно выключить автообновление статистики и добавить джоб, который будет делать пересчет регулярно, вот такой командой:
UPDATE STATISTICS ... WITH FULLSCAN NORECOMPUTE
Но и это еще не все, если кто-нибудь запустит sp_updatestats, то вся статистика опять слетит к чертям, до следующего запуска ручного пересчета статистики. Как вариант, если нужно пересчитать всю статистику по всей базе, то нужно делать это вот такой командой:
sp_updatestats @resample = 'resample'

или сразу после запуска sp_updatestats, делать WITH FULLSCAN для определенных таблиц. Короче есть над чем подумать в плане автоматизации всего этого :)

bormental
Теперь насчет sporder&katparty дам некоторые выкладки

1. select COUNT(*) from t$katparty = 699234
Ага, ну кардиналити по отдельным таблицам можн выдернуть из плана, так что я уже знаю все количества строк в каждой из таблиц.
bormental
2.
set @bdate=dbo.datetoint('20120201')
set @edate=dbo.datetoint('20120229')


(select distinct spo.f$cparty
from t$sporder spo inner join t$sklorder skl on spo.f$csklorder=skl.f$nrec and spo.f$sp=0 and spo.f$dord>=@bdate and spo.f$dord<=@edate)=142222
Это уже интереснее, по моим расчетам получалось 148284, что недалеко от истины.
bormental
3.

(select distinct spo.f$cparty
from t$sporder spo inner join t$sklorder skl on spo.f$csklorder=skl.f$nrec and spo.f$sp=0 and spo.f$dord>=@bdate and spo.f$dord<=@edate
inner join t$katparty party on spo.f$cparty = party.f$nrec and party.f$CORGPAR=@needorg) ~10000

В sporder, как Вы понимаете , храниться ссылка только на каталог партий(katparty), в котором есть ссылка на контрагента (katparty.corgpar). Выборку нужно по определенному контрагенту именно.

Еще добавлю что есть в sporder индекс отдельно только по полю cparty. В свете этого может быть даже выигрышнее делать будет предвыборку из katparty по нужному corgpar и далее join-ить к этой выборке sporder с условиями ? :

select
....
from
(
select distinct f$nrec as nrecparty from t$katparty where f$corgpar=@needorg
) t1 inner join t$sporder spo on t1.nrecparty = spo.f$cparty and spo.f$dord >= @bdate and spo.f$dord <= @edate.......

Да, это самая интересная часть.

t$katparty - 700К, селективность 0.0129, что оставляет нам порядка 9К
t$sporder - 10874К, селективность 0.12, в остатке 1304К
Если их объединить то вообще все красиво, 8-10К. Если добавлять индексы нельзя, то вариант SomewhereSomehow с временной таблицей самый правильный. Только для вставки во временую таблицу нужно делать джойн только этих 2х таблиц, ничего больше, ну и distinct тут не нужен однозначно, f$nrec же уникальный в t$katparty, нет? Ну и OPTION(RECOMPILE) не забудьте для этого селекта, он нужен однозначно. Надо убедиться что порядок соединения такой: t$katparty -> t$sporder, если нет, тогда захинтовать как INNER JOOP JOIN.

Индексы на получившейся временной таблице я бы создавать не стал, особого смысла нет. Мы хотим чтобы селект из этой таблицы был первым в плане, а потом уже все остальное джойнилось, и там пусть оптимизатор сам решает делать NESTED LOOPS или с какими то мелкими таблицами HASH MATCH, ну и о порядке дальнейших соединений пусть позаботитися сам. По крайней мере таким образом мы скорректируем неверные статистики по джойну этих 2-х больших таблиц, ну а дальше сервер все должен сделать сам.

Можно было бы вручную создать статистику с полным пересчетом на этой временной таблице, но учитывае ожидаемое количество строк в 8-10К, нет смысла делать это, дефолтная вполне сойдет и будет создана автоматически.
31 мар 12, 23:40    [12346354]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Mind
30 секунд это уже нормально, или все еще медленно?

С хинтом нестед лупс было вроде менее 20 секунд? ;-)

Это в пределах погрешности ;-)

SomewhereSomehow
Mind
Я все таки не сторонник использовать никакие другие хинты кроме как OPTION(RECOMIPLE)

И даже optimize for? ;-)

Хватит придираться :) optimize for это из той же серии, он не сильно ограничивает оптимизатор в свободе действий, да и при включении OPTION(RECOMIPLE) становится бессмысленной опцией. Я имел ввиду хинты для использования индексов, указания типа соединения и тем более форсирование порядка соединений. Как например в этом случае, я бы форсировал соединение только 2-х таблиц katparty->sporder. Но такой опции нет. При форсировании, я должен позаботиться о том чтобы все остальные джойны были указаны в правильном порядке, а их там дофига, а мозг у меня не резиновый. Должна же быть от оптимизатора хоть какая то польза, пусть дальше сам думает.

SomewhereSomehow
Mind
так что вот еще мои 5 копеек, при условии что мы уже обновили статистику. В последнем плане (Good_Plan_after_updstat) сервер решает дважды обратится к самой большой таблице - T$SPORDER по 2-м разным индексам, потом еще следует весьма дорогой хэш джойн чтобы их объединить, а потом только лукап с целью наложить фильтр по f$dord и таки свести результат к положенным 8 тысячам строк. Задача в том, чтобы сделать все эти фильтры и оставить 8К как можно раньше, ну и конечно избавиться от 2х обращений к ORDER.

Вот тут, полностью согласен, я пришел к тем же выводам, когда в первый раз смотрел на план. Однако, как этого добиться, без влияния на структуру БД?
У меня была задача интеграции с этой системой (правда у нас она крутится на Pervasive SQL) и лично я наоборот попросил наш IT урезать мне все права кроме как на выборку, чтобы нести минимальную ответственность если что-то поломается, нуивонафик.
Остается хинтование, либо манипуляция промежуточными результатами запроса.
Если нельзя даже создавать индексы, то таки да, вариантов остается совсем немного, или хинтовать, или временные таблицы. Второй вариант вполне даже не плохой, а в некоторых случаях и единственный, например, при кривых статистиках или очень сложных запросах.


SomewhereSomehow
Обновить статистику - всегда хорошая идея, если что-то идет не так. Но судя по планам, которые автор прилагал после обновления статистики, дело не столько в ней, сколько в том, что сервер выбирает не правильный порядок соединений, потому что делает неверные оценки.

Не совсем согласен с предположением. Сервер выбирает неправильный порядок соединений, потому что делает неверные оценки. Это все хорошо. А почему он делает неверные оценки? Все оценки основаны исключительно на предполагаемом количестве строк, упрощенно:
[стоимость операции] = [базовая цена оператора] + [цена обработки одной строки] * [предполагаемое кол-во строк]
Где цены зависят от типа операции, будь то seek, scan, loop join и т.д.
А кол-во строк базируется исключительно на статистике. Так что дело обычно таки в ней, или в отсутствии подходящих индексов.

SomewhereSomehow
Может быть, следует обновить статистику вообще по всем таблицам в запросе и это поможет?
Я уверен на 90%, что основные проблемы там с кардиналити между order и kartparty, но исправить это невозможно, ввиду неравномерности распределения значений или еще каких то ограничений мат. модели статистики. Оценки же по остальным таблицам выглядят вполне сносно. Если же обновлять статистики с полным сканированием по всем таблицам, то нужно делать это не разово, а регулярно, иначе все опять слетит.

SomewhereSomehow
Также не следует, имхо, сбрасывать со счетов количество таблиц. Все таки, их довольно много и довольно объемных, с множеством индексов.
Но по крайней мере серверу хватает ресурсов на компиляцию плана и он не вылетает с таймаутом компиляции, что уже хорошо.

SomewhereSomehow
Я бы пошел по пути определения узкого места, с которого начинаются расхождения actual и estimated и судя по всему мы его нашли в подзапросе к T$SPORDER. Осталось заставить сервер, вместо спулинга или многократного сканирования многомиллионной таблицы, сделать выборку 8000 записей, после чего успешно ее заюзать в остальном запросе. По этому мне так интересны эксперименты автора.
Согласен, мне тоже интересно что получится.

SomewhereSomehow
Может быть следует вообще выдернуть ее из подзапроса, сделать выборку во временную таблицу только из нее, сделать по временной индексы и джойнить ее со всеми остальными, ибо логика по словам ТС такая.
Но фильтр по одной только T$SPORDER не даст 8К, это будет порядка 1.3 ляма строк. Там необходим фильтрующий джойн с KATPARTY.
А зачем индексы на временной таблице? Вы думаете они реально будут использоваться? Самая дешевая операция для временной таблицы будет скан, так зачем смущать сервер всякими индексами?
1 апр 12, 00:26    [12346438]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind
Хватит придираться :) optimize for это из той же серии, он не сильно ограничивает оптимизатор в свободе действий, да и при включении OPTION(RECOMIPLE) становится бессмысленной опцией. Я имел ввиду хинты для использования индексов, указания типа соединения и тем более форсирование порядка соединений. Как например в этом случае, я бы форсировал соединение только 2-х таблиц katparty->sporder. Но такой опции нет. При форсировании, я должен позаботиться о том чтобы все остальные джойны были указаны в правильном порядке, а их там дофига, а мозг у меня не резиновый. Должна же быть от оптимизатора хоть какая то польза, пусть дальше сам думает.

Да я не придираюсь, это я так, ёрничаю =) Конечно, я понял, что вы имели ввиду и, более того, сам придерживаюсь того же мнения. (Мне в моем опыте хватило наткнуться на хинты with nolock, merge join, with (index (abc)) и кстати option recompile - чтобы понять, как оно влияет)
Mind
упрощенно:
[стоимость операции] = [базовая цена оператора] + [цена обработки одной строки] * [предполагаемое кол-во строк]
Это действительно очень упрощенно, и к сожалению, в общем случае неверно =( Могут быть задействованы величины из связУемых таблиц. Например, если вам не влом, гляньте последние записи у меня в блоге, и попробуйте определить как вычисляется стоимость lookup. Буду премного благодарен если найдете формулу, ибо все что я сам смог найти, это график некой функции, почти в точности совпадающий по форме с графиком построенным по результатам лукапа (как я сверял в маткаде). Но при попытке подобрать точные коэффициенты - к сожалению - фиаско! Не помогла даже аппроксимация методом наименьших квадратов и написанная мной прога на .нет. Либо я херовый математик, либо я рано остановил подбор коэффициентов (около 40 часов на 4 ядерном процессоре 3,2). Мне кажется, первое.

Mind
Я уверен на 90%, что основные проблемы там с кардиналити между order и kartparty, но исправить это невозможно, ввиду неравномерности распределения значений или еще каких то ограничений мат. модели статистики. Оценки же по остальным таблицам выглядят вполне сносно. Если же обновлять статистики с полным сканированием по всем таблицам, то нужно делать это не разово, а регулярно, иначе все опять слетит.

Вполне может быть. Алгоритм, определяющий наиболее статистически значимые значения несовершенен. И в этом, кстати, вы мне позволили наглядно убедиться предоставив репро, за что вам, большое спасибо, теперь, я тоже скептически отношусь к сбору статистики. Есть ситуации, когда можно делать обновления with fullscan, но это ничего не даст, в силу самого алгоритма.

Mind
Но фильтр по одной только T$SPORDER не даст 8К, это будет порядка 1.3 ляма строк. Там необходим фильтрующий джойн с KATPARTY.
А, ну возможно, видимо я невнимательно смотрел, я думал по дате и сп там отфильтруется 8к строк, тогда конечно. Я не вдавался в детали, моя идея была в том, чтобы обеспечить выборку из 10 млн всего 8-ми тысяч.

Насчет зачем индексы на временной таблице. Есть такая тема, что оптимизатор использует некие паттерны при генерировании вариантов и вычислении их стоимости. Нет индекса - нет варианта - нет вариантов+N*sub_plans. Т.е. мы отсекаем сразу целую ветку оптимизации по применению правил - это может быть хорошо, а может быть плохо. Учитывая, что нам надо помочь оптимизатору всяко-всяко использовать результаты временной таблицы при соединении с остальными, я надеюсь, что это все же хорошо, но я могу ошибаться.
По этому, имхо, только активные эксперименты спасут отца русской демократии.
1 апр 12, 01:47    [12346573]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow
Вполне может быть. Алгоритм, определяющий наиболее статистически значимые значения несовершенен. И в этом, кстати, вы мне позволили наглядно убедиться предоставив репро, за что вам, большое спасибо, теперь, я тоже скептически отношусь к сбору статистики. Есть ситуации, когда можно делать обновления with fullscan, но это ничего не даст, в силу самого алгоритма.

И все-таки, мне кажется, это не тот случай, когда статистика skewed, тут должно быть более реальное распределение данных и алгоритм должен отработать лучше и собрать "правильную" статистику. Ну не верю я что оно по миллиону строк с разными значениями собрал плохую статистику. Все-таки мне кажется надо пытаться что-то замутить с временными таблицами или брать на себя ответственность, расставлять джойны как надо и нещадно хинтовать.

А вообще, я удивляюсь:
bormental
Трудно сказать нормально или медленно. По мне может и нормально, а местные гуру скажут что медленно ) Каковы вот критерии "нормально/медленно" тут ? ) В принципе, удовлетворителен результат в пределах минуты по мне (учитывая объемы данных и условия что не могу менять структуру физ таблиц.)

Так задача ж нужна не местным гуру, а вам, и вам решать, достаточно ли быстро/медленно или нет! Какая разница кто что скажет, если вас это устраивает =)
1 апр 12, 02:06    [12346589]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Да я не придираюсь, это я так, ёрничаю =) Конечно, я понял, что вы имели ввиду и, более того, сам придерживаюсь того же мнения. (Мне в моем опыте хватило наткнуться на хинты with nolock, merge join, with (index (abc)) и кстати option recompile - чтобы понять, как оно влияет)

Я работал в компании, где все запросы, были захинтованы индексами, буквально каждая таблица + OPTION(LOOP JOIN) и выключено автоматическое создание и обновление статистики. Их ДБА я переспортить так и не смог, потому что он пользовался незыблемым авторитетом в компании и меня слушать никто не стал.

SomewhereSomehow
Mind
упрощенно:
[стоимость операции] = [базовая цена оператора] + [цена обработки одной строки] * [предполагаемое кол-во строк]
Это действительно очень упрощенно, и к сожалению, в общем случае неверно =( )
Моя цель была лишь показать, что любая цена это так или иначе, функция от количества строк.
SomewhereSomehow
Могут быть задействованы величины из связУемых таблиц.
Не совсем понял, какие именно величины? Или читать блог? :)

SomewhereSomehow
Например, если вам не влом, гляньте последние записи у меня в блоге, и попробуйте определить как вычисляется стоимость lookup.
Если честно, я уже смотрел последние 2 записи, узнал пару новых трейс флагов, но в целом, боюсь мне надо потратить не один час на то чтобы "въехать" во все это, с собственными экпериментами и прочим. Может когда будет время/желание покопаться в этом подробнее.

SomewhereSomehow
Буду премного благодарен если найдете формулу, ибо все что я сам смог найти, это график некой функции, почти в точности совпадающий по форме с графиком построенным по результатам лукапа (как я сверял в маткаде). Но при попытке подобрать точные коэффициенты - к сожалению - фиаско!

Я где-то видел презентацию, где были формулы с числами, которые они используют, но уже не помню были ли там все операторы или только самые простые, и тем более не помню гле я это видел. Но у меня другой вопрос, а зачем вам точная формула лукапа? Какое будет практическое применение? Или просто из любознательности?

SomewhereSomehow
Mind
Я уверен на 90%, что основные проблемы там с кардиналити между order и kartparty, но исправить это невозможно, ввиду неравномерности распределения значений или еще каких то ограничений мат. модели статистики. Оценки же по остальным таблицам выглядят вполне сносно. Если же обновлять статистики с полным сканированием по всем таблицам, то нужно делать это не разово, а регулярно, иначе все опять слетит.

Вполне может быть. Алгоритм, определяющий наиболее статистически значимые значения несовершенен. И в этом, кстати, вы мне позволили наглядно убедиться предоставив репро, за что вам, большое спасибо, теперь, я тоже скептически отношусь к сбору статистики.
Это явно не тот случай, здесь в обоих критичных таблицах строк больше тысячи, так что проблемы не должно быть. Хотя есть таблицы GRNAL, SPGRNAL и GRPODR с 10, 10 и 18 строк соответственно, но судя по планам, я не думаю что они сильно влияют на оценки количества строк.

SomewhereSomehow
Насчет зачем индексы на временной таблице. Есть такая тема, что оптимизатор использует некие паттерны при генерировании вариантов и вычислении их стоимости. Нет индекса - нет варианта - нет вариантов+N*sub_plans. Т.е. мы отсекаем сразу целую ветку оптимизации по применению правил - это может быть хорошо, а может быть плохо. Учитывая, что нам надо помочь оптимизатору всяко-всяко использовать результаты временной таблицы при соединении с остальными, я надеюсь, что это все же хорошо, но я могу ошибаться.
Может быть я что-то упускаю, но я вижу единственный нормальный вариант плана при котором первое обращение должно быть к временной таблице, в этом случае индексы теряют всякий смысл. А почему вы думаете что оптимизатор не сможет использовать результаты временной талицы? При построении плана он построит статистики, узнает сколько строк в таблице, более менее определит кардиналити с другими таблицами и для всего этого индексы не нужны.
1 апр 12, 03:29    [12346650]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
SomewhereSomehow
Вполне может быть. Алгоритм, определяющий наиболее статистически значимые значения несовершенен. И в этом, кстати, вы мне позволили наглядно убедиться предоставив репро, за что вам, большое спасибо, теперь, я тоже скептически отношусь к сбору статистики. Есть ситуации, когда можно делать обновления with fullscan, но это ничего не даст, в силу самого алгоритма.

И все-таки, мне кажется, это не тот случай, когда статистика skewed, тут должно быть более реальное распределение данных и алгоритм должен отработать лучше и собрать "правильную" статистику. Ну не верю я что оно по миллиону строк с разными значениями собрал плохую статистику.

А мне кажется что как раз таки кривая статистика. Гляньте хотя бы на вот этот кусок плана в аттаче.
Для 9,048 строк из PARTY он ожидает 659,986 из ORDER, это получается примерно по 73 ордера на каждого контрагента, что скорее всего далеко от истины. Если мы тупо поделим 10,874К на 700К то получим 15.5 строк, в реале же соотношение будет 86,568/9,048= 9.56. Так моя цифра 15.5 основаная только лишь на количество строк в таблицах, у меня нет статистических распределений данных, и то ближе к истине, чем оптимизатор с его 73 против 9.53 реальных, при наличии 100% сэмпла!
Я догадываюсь откуда тут растут ноги, и у меня есть точные формулы расчета кардиналити между 2-мя таблицами, я попробую сделать репро конкретно этой ситуации и если получится выложу результаты тут.

К сообщению приложен файл. Размер - 14Kb
1 апр 12, 03:47    [12346663]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
SomewhereSomehow
А вообще, я удивляюсь:
bormental
Трудно сказать нормально или медленно. По мне может и нормально, а местные гуру скажут что медленно ) Каковы вот критерии "нормально/медленно" тут ? ) В принципе, удовлетворителен результат в пределах минуты по мне (учитывая объемы данных и условия что не могу менять структуру физ таблиц.)

Так задача ж нужна не местным гуру, а вам, и вам решать, достаточно ли быстро/медленно или нет! Какая разница кто что скажет, если вас это устраивает =)


Может я немного некорректно написал об этом. Я бы мог вполне остановиться на варианте формирования, скажем до 10 минут, и заказчика бы это все равно удовлетворило. Тут немножко не то я имел ввиду. Задача,тут не поспоришь, нужна мне. Но я не могу объективно оценить быстро/медленно работает данная фишка,в силу того, что осознаю что мои познания малы пока еще в области такого зверька как MSSQL. Это будет ясно когда я, с помощью Вас и Mind-а(может еще кто то присоединиться к проблематике вопроса...) смогу/не смогу заставить ускорить работать алгоритм быстрее. Насколько я понимаю, это произойдет в тот момент - когда Вы , поглядев в план скажите "все парень...при текущих входных условиях задачи больше не выжать будет ничего.."

Но вернемся к теме.
Прикинул вариант с заполнением времянки (при выборки в temp table оставил только sporder и katparty. SQLQuery6.sql исходник соотносится с планом SqlQuery6_Plan.sqlplan. План SqlQuery6_1_Plan.sqlplan лишь отличается тем, что в исходнике попробовал добавить хинт указав явный индекс WITH (INDEX(t$sporder21)))
Также вариант попробовал свой, когда предварительно выцепляю партии контрагентов и потом уже join-ю на ордер (SQLQuery7.sql. План выполнения этого запроса получается практически 1:1 как в SqlQuery6_Plan.sqlplan )

По времени что могу сказать...за рассматриваемый период февраль формируется по всем этим вариантах примерно одинаково. А именно 35-45 секунд. Объем данных в sporder по нужным критериям влияет очень - задаю другой месяц. и если выборка в #selspo получается, грубо говоря, в пару раз меньше то и время отработки всего батча в 2 раза уменьшается.

К сообщению приложен файл (newtestsql.rar - 34Kb) cкачать
2 апр 12, 00:24    [12348537]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
bormental
SomewhereSomehow
А вообще, я удивляюсь:
пропущено...

Так задача ж нужна не местным гуру, а вам, и вам решать, достаточно ли быстро/медленно или нет! Какая разница кто что скажет, если вас это устраивает =)


Может я немного некорректно написал об этом. Я бы мог вполне остановиться на варианте формирования, скажем до 10 минут, и заказчика бы это все равно удовлетворило. Тут немножко не то я имел ввиду. Задача,тут не поспоришь, нужна мне. Но я не могу объективно оценить быстро/медленно работает данная фишка,в силу того, что осознаю что мои познания малы пока еще в области такого зверька как MSSQL. Это будет ясно когда я, с помощью Вас и Mind-а(может еще кто то присоединиться к проблематике вопроса...) смогу/не смогу заставить ускорить работать алгоритм быстрее. Насколько я понимаю, это произойдет в тот момент - когда Вы , поглядев в план скажите "все парень...при текущих входных условиях задачи больше не выжать будет ничего.."

Я бы сказал что это уже оно, если нельзя создавать/изменять индексы, то вряд ли что-то еще можно выжать из этого запроса.

bormental
Но вернемся к теме.
Прикинул вариант с заполнением времянки (при выборки в temp table оставил только sporder и katparty. SQLQuery6.sql исходник соотносится с планом SqlQuery6_Plan.sqlplan. План SqlQuery6_1_Plan.sqlplan лишь отличается тем, что в исходнике попробовал добавить хинт указав явный индекс WITH (INDEX(t$sporder21)))
Но тогда получаются очень дорогие лукапы, потому что индекса t$sporder21 не достаточно чтобы получить все необходимые данные, поэтому получаем 1.3 ляма лукапов. Можно оставить конечно с этим хинтом, но не ради перформанса этого запроса, а для "живучести" сервера в общем, сам запрос как раз таки может немного пострадать. Если сделать без хинта то каждый раз будет сканироваться вся таблица sporder, с последующим помещением её в буфер и возможным вытеснением из буфера всего остального. Тут чтобы определится, надо смотреть на размер этой таблицы в МБ, количество памяти доступной серверу и как часто вызывается запрос. Если таблица займет 5% буфера то может и нормально делать скан каждый раз, а если 50%, то я бы подумал.
Сколько у вас CPU на сервере, 24? Я бы ограничил первый запрос скажем MAXDOP(8 или 4), тратить все CPU может оказаться не очень хорошей идеей, у вас же OLTP система.

bormental
Также вариант попробовал свой, когда предварительно выцепляю партии контрагентов и потом уже join-ю на ордер (SQLQuery7.sql. План выполнения этого запроса получается практически 1:1 как в SqlQuery6_Plan.sqlplan )

Так и должно быть, потому что серверу пофик на эти синтаксические "приседания" он все равно приводит запрос к единому виду при оптимизации.

bormental
По времени что могу сказать...за рассматриваемый период февраль формируется по всем этим вариантах примерно одинаково. А именно 35-45 секунд. Объем данных в sporder по нужным критериям влияет очень - задаю другой месяц. и если выборка в #selspo получается, грубо говоря, в пару раз меньше то и время отработки всего батча в 2 раза уменьшается.

Я так понимаю, что партия контрагентов в этот раз другая нежели для плана Good_Plan_after_updstat? Поэтому и цифры получаются другие для плана с заполнением временной таблицы.
2 апр 12, 04:46    [12348691]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Ну вот собственно репро. Попытался сделать с теми же количествами строк что и у топикстартера.
В скрипте 3 таблицы:
KATPARTY - 700К строк; поле corgpar более-менее равномерно заполнено значениями от 1 до 77
SPORDER - 10,874К строк; поле cparty равномерно заполнено значениями из KATPARTY, но использованы только 148,284 значения
SPORDER2 - 10,874К строк; поле cparty равномерно заполнено всеми значениями из KATPARTY

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

Еще я создаю необходимые индексы, и так как статистика используется только из этих индексов, то имеем фактически статистику пересчитанную с FULLSCAN.

Результаты можно увидеть на картинке. В первом случае оптимизатор жестоко ошибается, причем в большую сторону, всего лишь из-за того что мы использовали не все значения из справочника. Ошибка в большую сторону это обычно лучше чем в меньшую, но тем не менее это может привести к неправильному распределению стоимостей в плане и как следствие перестройку всего плана к менее оптимальному. Также переоценка чревата тем, что оптимизатор попросит больше памяти чем необходимо и при выполнении запрос может ждать памяти, хотя на самом деле она и не нужна в таких количествах, такое впрочем применимо скорее для очень тяжелых OLAP запросов.

Понять, почему сервер делает такие оценки на основании статистики весьма несложно. Если скажем у нас есть справочник из 10 строк и таблица фактов из 100 строк, но мы используем только 3 значения из справочника, то при джойне этих таблиц сервер оценивает, что каждой строке из справочника соответствует в среднем 100/3=33.33 строк из таблицы фактов. Потом это число перемножается на количество ожидаемых строк из внешней таблицы.
Формула таким образом получается вот такая:
SELECT 1.0*COUNT(*)/COUNT(DISTINCT cparty) FROM dbo.SPORDER
SELECT 1.0*COUNT(*)/COUNT(DISTINCT cparty) FROM dbo.SPORDER2

Ну и вот результаты:
---------------------------------------
73.332254322786
---------------------------------------
15.534285714285
что сходится со значениями из плана.

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

Выводы:
1. Статистика несовершенна даже с FULLSCAN
2. Если есть справочник не все значения из которого использованы в таблице фактов, то 100% будет переоценка кардиналити. Как следствие, создавать универсальную таблицу-справочник, в которой строки будут разных типов и использоваться потом разными таблицами, очень плохая идея :)

К сообщению приложен файл. Размер - 29Kb
2 апр 12, 06:03    [12348703]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Репро скрипт

К сообщению приложен файл (cardinality estimation test.sql - 2Kb) cкачать
2 апр 12, 06:04    [12348704]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

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



Не совсем так. Скажем так ..Партия контрагента - это уникальный идентификатор прихода. Она создается на этапе прихода от контрагента и в дальнейшем она везде цепляется в таблице складского движение (sporder). Например, это может быть из за того, что просто движений по sporder с партиями этого же контрагента в другом месяце м.б. меньше (больше).
2 апр 12, 07:33    [12348749]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
Хотя наверное я не прав. Как же скуль тогда быстрее выбирает данные, если все равно происходит скан таблицы. Значит объем sporder просто за другой промежуток временной отличается от нами тестируемого.
2 апр 12, 07:36    [12348752]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
Нет. Вот сейчас сделал еще раз запрос по этому другом контрагнету за этот же период. Получается что скан по sporderидет все равно по ~ 1,3 млн записям (что и в разжеванном примере) но выборка из katparty вместо 64 к, получается 9 к.
И из за этого весь селект отрабатывает за 8-10 сек. в отличие от нашего где 35-40 сек.
2 апр 12, 07:51    [12348759]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
Нет. Вот сейчас сделал еще раз запрос по этому другом контрагнету за этот же период. Получается что скан по sporderидет все равно по ~ 1,3 млн записям (что и в разжеванном примере) но выборка из katparty вместо 64 к, получается 9 к.
И из за этого весь селект отрабатывает за 8-10 сек. в отличие от нашего где 35-40 сек.

К сообщению приложен файл (SqlQuery6_2_Plan.rar - 16Kb) cкачать
2 апр 12, 07:52    [12348760]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
bormental
Хотя наверное я не прав. Как же скуль тогда быстрее выбирает данные, если все равно происходит скан таблицы. Значит объем sporder просто за другой промежуток временной отличается от нами тестируемого.

Я думаю, что просто второй селект из временной таблицы выполняется быстрее/медленнее в зависимости от того сколько в ней строк, 9к или 64к это может быть весьма значительно, учитывая что именно столько будет поисков по индексу и лукапов по другим таблицам.

Скан по sporder идет не по 1,3 млн, а по всем 10ти млн. 1.3 это то что остается в результате фильтрации по дате.
2 апр 12, 08:48    [12348830]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind
Но у меня другой вопрос, а зачем вам точная формула лукапа? Какое будет практическое применение? Или просто из любознательности?
Чисто академический интерес.
Mind
Может быть я что-то упускаю, но я вижу единственный нормальный вариант плана при котором первое обращение должно быть к временной таблице, в этом случае индексы теряют всякий смысл. А почему вы думаете что оптимизатор не сможет использовать результаты временной талицы? При построении плана он построит статистики, узнает сколько строк в таблице, более менее определит кардиналити с другими таблицами и для всего этого индексы не нужны.
М.б., но я бы не стал сразу столь категорично отметать индексы. В варианте когда их нет, единственное возможным вариантом остается всегда полное сканирование таблицы, если мы исходим из логики сканируем всю таблицу и после чего последовательно джойним все остальное, то индексы конечно нам ни к чему, но если в таблице может быть много строк (а как я понял их м.б. от 8К до 60К), то может быть дать оптимизатору побольше вариантов выбора стратегии доступа? По крайне мере, стоит попробовать это на этапе отладки, если в будущем удастся прийти к хорошему плану без них, то можно будет их удалить с чистой совестью.

Mind
А мне кажется что как раз таки кривая статистика. Гляньте хотя бы на вот этот кусок плана в аттаче.
Для 9,048 строк из PARTY он ожидает 659,986 из ORDER, это получается примерно по 73 ордера на каждого контрагента, что скорее всего далеко от истины. Если мы тупо поделим 10,874К на 700К то получим 15.5 строк, в реале же соотношение будет 86,568/9,048= 9.56. Так моя цифра 15.5 основаная только лишь на количество строк в таблицах, у меня нет статистических распределений данных, и то ближе к истине, чем оптимизатор с его 73 против 9.53 реальных, при наличии 100% сэмпла!
Я догадываюсь откуда тут растут ноги, и у меня есть точные формулы расчета кардиналити между 2-мя таблицами, я попробую сделать репро конкретно этой ситуации и если получится выложу результаты тут.

Я смотрел по последнему на тот момент архиву testsql.zip 12340498. Там вроде все ок, по крайней мере в этом куске: Картинка с другого сайта.

Mind
Репро скрипт

Репро интересное, почитал с удовольствием. Но по-моему, вы опять манипулируете данными, как в случае, когда использовали только нечетные номера =)
С одной стороны, вы, когда готовите тестовые данные, делаете равномерное распределение nrec, по каждому из 77 значений.
Т.е. в каждой выборке по одному из 77 значений, будут представлены как те значения которые есть в таблице sporder, так и те которых нет (из-за того, что заполнено было только 21%), и естественно, т.к. джойним мы все значения, то те, для которых нет, дадут overestimate. Если на простом примере:
+
create table #katparty (nrec int identity(1,1) primary key, corgpar int not null)
create table #sporder (nrec int identity(1,1) primary key,  cparty int not null)
insert into #katparty (corgpar) values (1),(2),(3);
insert into #sporder (cparty) values (1),(1),(1),(1),(1),(2),(2),(2),(3);
create index idx_sporder on #sporder(cparty)

--underestimate
--actual:5
--estimated:3
select o.nrec
from #katparty k
	inner loop join #sporder o on k.nrec = o.cparty
where k.corgpar = 1
option(recompile)

--equal
--actual:3
--estimated:3
select o.nrec
from #katparty k
	inner loop join #sporder o on k.nrec = o.cparty
where k.corgpar = 2
option(recompile)

--overestimate
--actual:1
--estimated:3
select o.nrec
from #katparty k
	inner loop join #sporder o on k.nrec = o.cparty
where k.corgpar = 3
option(recompile)

drop table #sporder,#katparty

А еще точнее, важно не то что они есть или нет в таблице с которой соединяем, важно то что в данном случае, при оценке соединения используется не гистограмма значений а просто density и в зависисмости от того, насколько хорошо и равномерно распределены значения, мы будем получать одну и ту же оценку, но разные действительные значения. "В среднем по больнице" (если мы не будем исключать 80% всех значений), должно получиться более менее точно, но если заставить сервер делать оценки, используя только информацию о плотности, для заведомо несуществующих значений, то мы и получим такое расхождение.
Короче, в данном случае получается упираемся в ограничения модели.
Ну и вот, в целом, мне сомнительно что если мы говорим о реальности, то данные имеют такое красивое распределение, что ровно для одного контрагента из таблицы партий выбираются именно такие партиии, 80% которых отсутсвуют в таблице заказов... Все может быть конечно, но лично я сомневаюсь.

Mind,
а не смотрели, в каких случаях эта формула перестает работать и оценки кол-ва строк в соединении делаются по другому?

bormental,
к сожалению, сейчас нет времени сильно подробно вникать в новые планы, в итоге, я так понял, что первоначальные планы были для одного контрагента, а последние для другого, дял которого записей больше, в с случае с первоначальным контрагентом с использованием временной таблицы отрабатывает за 8-10 секунд, а если записей становится больше - время масштабируется линейно?
2 апр 12, 16:44    [12352606]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
bormental
Member

Откуда:
Сообщений: 90
А почему тогда нельзя никак заюзать выборку во врем таблицу только из sporder . Имею ввиду хотя бы в нее отгрузить по индексу по диапазону дат эти 1,3 млн рекордов, чтобы как сказал Mind не было скана по всем 10 млн...
Я попытался это сделать но все равно оптимизатор решает по ней почему то скан сделать, хотя даже пробовал явно указывать индекс :

К сообщению приложен файл (picture.zip - 106Kb) cкачать
2 апр 12, 17:13    [12352839]     Ответить | Цитировать Сообщить модератору
 Re: Поясните поведение Sql при таком select  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
bormental,

Ну у вас эти самые 1.3М строк занимают более 100 мб, не хилая такая нагрузка! Я не думаю, что вытаскивать 1.3м строк во временную таблицу хорошая идея... Надо наоборот, постараться сделать наиболее узкое условие.

Теперь, относительно стратегии доступа. У вас ведь в дальнейшем, в запросе, используются не только поле дата, а значит серверу нужны другие поля. Откуда он их может взять? Либо из самого индекса (если он кластерный или покрывающий, т.е. все необходимые поля уже есть в самом индексе), либо выполнить lookup, для каждого из найденного в индексе значения. Что касается первого, я так понял индекс не кластерный и не покрывающий, и влиять на структуру индексом мы не можем. Остается второе, т.е. лукап. Но тогда, тут все зависит от селективности предиката (в данном случае spo.f$dord >= @bdate and spo.f$dord <= @edate). И в вашем случае, он не очень селективен, и отбирает примерно 1/10 всей таблицы. А учитывая что лукап предполагает модель случайного доступа (т.е. искомое значение может находиться где угодно), то это делает лукап очень дорогой операцией. Соотношение между тем когда сервер выбирает лукап или просмотр всего индекса/таблицы - определяется той самой формулой, которой я интересовался, но в общем обычно процент отобранных строк и последующего лукапа, должен быть довольно низким по отношению к общему числу строк, явно не ваш случай.
Остается посмотреть на другие индексы, может быть запрос по ним даст лучшую избирательность, запрос по katparty дает уже гораздо более лучший результат, но даже тогда, насколько я понял, без хинта оптимизатор все равно выбирает скан.

Может быть, вы знаете как соединить какие-то таблицы, без помощи sporder, таким образом, чтобы в итоге получилась совсем небольшая таблица? Тогда можно попробовать пойти обратным путем, собрать во временную таблицу результат, и уже потом сджойнить его с большой таблицей. И если это будет достаточно выгодно, то м.б. сервер предпочтет использовать поиск по индексу + лукап или пересечения индексов, и откажется от сканирования всей таблицы?
Вкратце, проиллюстрировать идею можно так
+ как идея...
use tempdb;
go
create table t1(id int identity, a int, b int);
create table t2(a int primary key);
create table t3(a int primary key);
insert into t1 select number,number from master..spt_values where type = 'p' and number between 500 and 1000
insert into t2 select number from master..spt_values where type = 'p' and number between 1 and 507
insert into t3 select number from master..spt_values where type = 'p' and number between 505 and 1500
create index t1_a on t1(a);
go
--------------------
-- t1 table scan
select *
from
	t1
	join t2 on t1.a = t2.a
	join t3 on t1.a = t3.a
go
--------------------
-- сначала объединим t2 и t3, чтобы получить во временной таблице достаточно небольшое число строк
-- всего 2 строки, 0.4% от всех записей в таблице t1
select t2a = t2.a, t3a = t3.a
into #t2t3
from
	t2
	join t3 on t2.a = t3.a
-- теперь, этот результат объединяем с якобы большой t1 получаем: index seek t1_a + t1 rid lookup
select
	*
from
	t1
	join #t2t3 t2t3 on t2t3.t2a = t1.a
go
drop table #t2t3;
drop table t1,t2,t3;
go

но не знаю, возможно ли вообще это в вашем случае...ведь обычно конечно, сервер умеет догадываться до такого и без временных таблиц.
2 апр 12, 20:58    [12353847]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить