Баг в MSSQL или что?

[OtherSide]

declare @dd datetime
set @dd = '20150717'

select top 1 (number) from fulltrades where market = @market and rounddate > '20150717' and id = @tickerid order by number
select top 1 (number) from fulltrades where market = @market and rounddate > @dd and id = @tickerid order by number


Первый селект отрабатывает 1 сек. Второй - 10 минут

[mskmel]

declare @dd datetime
set @dd = '20150717'

select top 1 (number) from fulltrades where market = @market and rounddate > '20150717' and id = @tickerid order by number
select top 1 (number) from fulltrades where market = @market and rounddate > @dd and id = @tickerid order by number
Первый селект отрабатывает 1 сек. Второй - 10 минут

Я не доктор, но во втором случае есть bind переменные, а в первом нет.
Гуглить думаю сюда: http://blogs.technet.com/b/mdegre/archi ... ffing.aspx" onclick="window.open(this.href);return false;

[oleg lebedev]

declare @dd datetime
set @dd = '20150717'

select top 1 (number) from fulltrades where market = @market and rounddate > '20150717' and id = @tickerid order by number
select top 1 (number) from fulltrades where market = @market and rounddate > @dd and id = @tickerid order by number


Первый селект отрабатывает 1 сек. Второй - 10 минут

Это никакой не баг, я такое видел на множестве разных баз с которыми работал: Оракл, postgres, GreenPlum.
Если вы подумаете чуть поглубже, то в первом query оптимизатор отвечает на вполне конкретный вопрос, о том какой план должен быть для rounddate > '20150717'. Во втором случае - оптимизатор отвечает на вопрос, какой план должен быть для любого значения col_val_current> @dd.
А что будет если @dd больше любого значения в этой колонке, а фильтры на др. колонках не работают (их нет или они неэффективны)? Это означает full scan на таблицу. Вот вам и 10 минут вместо 1 секунды. В последнем случае, там может индех и оптимизатор знает, скажем, что надо просмотреть всего 0.1% всех записей в случае вполне конкретного значения @dd. В самом деле, число записей для @dd>20150717 существенно меньше чем, скажем для @dd>20010112.
Попробуйте условие @dd=20150717 сравнить с @dd=past_date_from_db и вы обнаружите, что это тоже выполнится в то же время 1 сек.
Для общего случая, от оптимизатора требуют ответ прямо сейчас, без знания конкретного значения @dd и он выбирает full scan. Посмотрите на query plan чтобы убедится в том, что я вам тут написал.

К тому же надо понимать, что оптимизатор часто выбирает совершенно идиотские решения, когда есть из чего выбирать и условие rounddate > '20150717' оставляет гораздо меньше пространства для интеллектуальных упражнений.

[IvanF]

тут еще большой вопрос какого типа rounddate. Если это вариант строкового типа, то тогда все сразу будет понятно.

[Alexandr]

declare @dd datetime
set @dd = '20150717'

select top 1 (number) from fulltrades where market = @market and rounddate > '20150717' and id = @tickerid order by number
select top 1 (number) from fulltrades where market = @market and rounddate > @dd and id = @tickerid order by number


Первый селект отрабатывает 1 сек. Второй - 10 минут

нюанс следующий: семантика оптимизатора для двух запросов разная. В первом случае он смотрит в гистограмму статистики (optimize for value), а во втором смотрит в так называемый dense vector (optimize for unknown).
Первое, что я бы сделал, так это посмотрел в плане estimated rows count для первого и для второго, если есть большая разница между estimated row count и actual row count во втором случае (а соответственно и разный план, например index seek vs index scan), то смотрел бы в статистику.
Есть вероятность, что как раз кривая статистика.

[Alexandr]

тут еще большой вопрос какого типа rounddate. Если это вариант строкового типа, то тогда все сразу будет понятно.

Если типы roundate и dd разные и roundate более приоритетный, что означает, что нельзя dd привести к rounddate, а нужно rounddate приводить к dd, то будет гарантированный table scan

[OtherSide]

option (RECOMPILE) помогло

[Alexandr]

option (RECOMPILE) помогло

Если option recompile помог вот почему:
запрос select top 1 (number) from fulltrades where market = @market and rounddate > '20150717' and id = @tickerid order by number
является так называемым ad hoc запросом и он не параметризован, поэтому в кеше планов запросов он лежит не параметризованным, это означает, что только полное соответствие текста запроса обеспечит cache plan hit. Это значит, что такой же запрос, но с другой датой опять будет парсится, создаваться отдельный план запроса, который может отличаться от плана запроса с предыдущей датой (это важно). Т.е. если у вас данные распределены таким образом, что за одну дату может быть 2 заявки, а за другую - 1000000, то проблемы нет, каждый раз будет новый план, все гуд.

Теперь про второй запрос:
select top 1 (number) from fulltrades where market = @market and rounddate > @dd and id = @tickerid order by number

Этот запрос кешируется в кеше планов параметризованным, и, соответственно cache hit будет для какую бы дату вы не выбрали. Причем план запроса генерится при первом исполнении подобного запроса. А теперь вуаля, вы сначала выполнили параметризованный запрос с датой, такой, что количество записей малое и сгенерился план (например Index seek + rid lookup), а потом пытаетесь выполнить тот же запрос, но с датой, для которой количество строк результата большое, но так как будет cache hit, то возьмется план, который уже есть, соответственно, так где нужен index scan будет тот же index seek + rid lookup, что очень грустно скажется на производительности.

Почему вам помог recompile: так как всегда генерится новый план запроса, то вариативность данных (много/мало строк в результирующем наборе) будет компенсироваться адекватным планом.

Еще раз: проблема возникла потому, что вы сначала исполнили параметризованный запрос с датой, где было мало данных, а потом где много, и второй случай был исполнен с сильно неоптимальным планом.
Где это посмотреть: в плане запроса у select оператора (если не путаю) есть свойство, которое показывает с каким значением был скомпилирован план, а с каким исполнен. Также что что-то не так можно увидеть в том, что есть большая разница между estimated row count и actual row count в плане выполнения запроса.

[Alexandr]

Также вам можно рассмотреть еще один вариант, чтобы не зависеть от того, с какими значениями запрос выполнялся первый раз.
Специально указать optimize for unknown, тогда он тупо возьмет статистику из dense vector и вы получите какой-то план, который более менее подходит для любых значений переменных.
почитать можно тут
http://blogs.msdn.com/b/sqlprogrammabil ... ature.aspx" onclick="window.open(this.href);return false;

[OtherSide]

Это просто еще раз доказывает, что SQL - это кусок говна, хуже языка или технологии невозможно представить. Изначально задумывался как "язык для бухгалтеров" и что типа даже выучить рельяционную алгебру для них это космос. И язык типа декларативный - не надо заботится как машина вычисляет - надо придумать что хотят от нее. А по факту вылазит такое вот. Я не знаю какой логикой тут нужно обладать, что бы предвидеть такое. Какая разница по идее передается по значению или по ссылке. А если и есть - так пусть оптимизатор внутри себя как то решит. Получается для написания элементарнейшого запроса надо перетрусить все кишки сиквел сервера.
Вот я еще наткнулся на глючок. Вроде как считается что сиквел сервис должен быть достаточно умным, что бы инлайн подставлять хранимые функции? Так нифига. Всегда хранимые функции у меня отрабатываются гораздо медленее чем тот же код вписаный - даже самый элементарный

[Alexandr]

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

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

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

А что такое отрабатывается хранимая функция? Там выше уже приведен пример, когда несоответствие типов сразу приводит к table scan, так что весь вопрос как вы эти функции используете. Плюс есть общеизвестная штука, что оптимайзер крайне плохо оптимизит так называемые multistatement table valued functions (и оно вполне понятно почему), а также план запроса, где эти функции используются не отражают ресурсы, которые затрачивались на эти функции

[OtherSide]

В любом случае спасибо за помощь

[oleg lebedev]

Это просто еще раз доказывает, что SQL - это кусок говна, хуже языка или технологии невозможно представить. Изначально задумывался как "язык для бухгалтеров" и что типа даже выучить рельяционную алгебру для них это космос. И язык типа декларативный - не надо заботится как машина вычисляет - надо придумать что хотят от нее. А по факту вылазит такое вот. Я не знаю какой логикой тут нужно обладать, что бы предвидеть такое. Какая разница по идее передается по значению или по ссылке. А если и есть - так пусть оптимизатор внутри себя как то решит. Получается для написания элементарнейшого запроса надо перетрусить все кишки сиквел сервера.
Вот я еще наткнулся на глючок. Вроде как считается что сиквел сервис должен быть достаточно умным, что бы инлайн подставлять хранимые функции? Так нифига. Всегда хранимые функции у меня отрабатываются гораздо медленее чем тот же код вписаный - даже самый элементарный

Когда я только ознакомился с базами и с SQL много лет назад, то тоже так думал про SQL.
А потом когда освоил ряд баз и накопил большой опыт в разных их областях (development, DBA. design), то перестал так думать.
А вообще, хорошо на SQL мало кто умеет писать, хотя просто SQL пишут почти все. Для игрушечных баз - это вполне подходит. Когда SQL становится сложный и вовлекает большой объём данных, то, как правило, происходит эпический затык и всякие там гуру в областях из Java и С++ вопят " Почему это работало в development и нихрена не работает в production?".

[Alexandr]

oleg lebedev, да банально, если задумываться почему так сделали что-то в базе, то часто оказывается, что по другому сделать вообще невозможно, либо есть несколько путей и одна база пошла по одному пути, а другая по другому (например, ms sql блокирует строки при записи, а Оракл тупо копирует блок в undo буфер и тем самым как-бы обеспечивает неблокируемое чтение, а третьего варианта то особо и нет). Все таки в Оракле и Майкрософте не дураки сидят, и, если бы была возможность сделать как-то менее геморно, давно сделали бы.

[mskmel]

А вообще, хорошо на SQL мало кто умеет писать, хотя просто SQL пишут почти все. Для игрушечных баз - это вполне подходит. Когда SQL становится сложный и вовлекает большой объём данных, то, как правило, происходит эпический затык и всякие там гуру в областях из Java и С++ вопят " Почему это работало в development и нихрена не работает в production?".

Это нормально. С ростом сложности процесса возрастают требования к специалистам. Возить свою попу на работу это задача с которой любая блондинка справится, а вот управлять траками\самолётами\пароходами нужны специально отобученные люди.

[Easbayguy]

Надо делать все на MongoDB, а то SQL то, SQL не то!

[oleg lebedev]

oleg lebedev, да банально, если задумываться почему так сделали что-то в базе, то часто оказывается, что по другому сделать вообще невозможно, либо есть несколько путей и одна база пошла по одному пути, а другая по другому (например, ms sql блокирует строки при записи, а Оракл тупо копирует блок в undo буфер и тем самым как-бы обеспечивает неблокируемое чтение, а третьего варианта то особо и нет). Все таки в Оракле и Майкрософте не дураки сидят, и, если бы была возможность сделать как-то менее геморно, давно сделали бы.

Почему третьего не дано?
В postgres, например, дата блок вообще не обновляется, а всё пишется в новый блок. Когда запись произведена pointer перекидывается на новый блок. Это позволяет избавиться от большого количества головных болей, присущих Ораклу типа dead locks, trigger is mutating, snapshot too old и пр. Но за это нужно платить в виде необходимости делать vacuum на регулярной основе, где старые блоки помечаются как пригодные для использования для новых записей.
Dead locks в postgres тоже есть, но их появление на порядок меньше чем в Оракле.

По поводу Майкрософт, то они вообще не написали своей базы, если не считать убогий access.
MS SQL server - это Sybase, который они адаптировали лет 20 назад.

[oleg lebedev]

А вообще, хорошо на SQL мало кто умеет писать, хотя просто SQL пишут почти все. Для игрушечных баз - это вполне подходит. Когда SQL становится сложный и вовлекает большой объём данных, то, как правило, происходит эпический затык и всякие там гуру в областях из Java и С++ вопят " Почему это работало в development и нихрена не работает в production?".

Это нормально. С ростом сложности процесса возрастают требования к специалистам. Возить свою попу на работу это задача с которой любая блондинка справится, а вот управлять траками\самолётами\пароходами нужны специально отобученные люди.

Я тоже считаю, что это нормально. Мой пост был в ответ на категоричное утверждение, что SQL - гумно, и предназначено для бухгалтеров. Я хотел сказать, что на нём нужно уметь писать и понимать как работает база для которой это написано, чтобы результат написания не выглядело гумном.

[OtherSide]

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

[oleg lebedev]

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

Там есть логика и вполне объяснимая. Просто требуется некий опыт работы в этой области, также как и в любой другой.

[Counter]

Ну, какая-то логика есть.
На самом деле, из общих соображений о смысле полей market, rounddate и id в таблице fulltrades, я бы предположил, что поле id индексировано и условие id = @tickerid гораздо более "уникально", чем условие rounddate > '20150717' или market = @market.
Т.е., если вы сравните время выполнения
select top 1 (number) from fulltrades where id = @tickerid and market = @market and rounddate > '20150717' order by number
select top 1 (number) from fulltrades where id = @tickerid and market = @market and rounddate > @dd order by number

то, скорее всего, не увидите разницы во времени выполнения

[Alexandr]

Почему третьего не дано?
В postgres, например, дата блок вообще не обновляется, а всё пишется в новый блок. Когда запись произведена pointer перекидывается на новый блок. Это позволяет избавиться от большого количества головных болей, присущих Ораклу типа dead locks, trigger is mutating, snapshot too old и пр. Но за это нужно платить в виде необходимости делать vacuum на регулярной основе, где старые блоки помечаются как пригодные для использования для новых записей.
Dead locks в postgres тоже есть, но их появление на порядок меньше чем в Оракле.

по сути в postgres сделали бесконечный undo segment путем создания дополнительного гемора (с vacuum). Snapshot too old тоже не будет возникать в oracle, если Undo сегмент бесконечный.
Можете привести пример, при каким обстоятельствах в oracle будет dead lock, а в postgres нет?

[Колхозник]

Это просто еще раз доказывает, что SQL - это кусок говна,.........

Вот вы ведь же даже не потрудились повесить схему таблицы по сути запроса..
А туда же - "гусок говна".
Хто вас знает, вполне может быть что колонка "rounddate" вообще типа varchar (....) и оптимизатор уходит в глобальную конвертацию данных колонки по все таблице перед тем как сравнить varchar и datetime.

А народ уже трактаты понаписал без достаточных вводных (еще можно понять - от избытка времени).
Надо было промолчать.
Какой привет, такой ответ.

[Мальчик-Одуванчик]

Это просто еще раз доказывает, что SQL - это кусок говна, хуже языка или технологии невозможно представить. Изначально задумывался как "язык для бухгалтеров" и что типа даже выучить рельяционную алгебру для них это космос. И язык типа декларативный - не надо заботится как машина вычисляет - надо придумать что хотят от нее. А по факту вылазит такое вот. Я не знаю какой логикой тут нужно обладать, что бы предвидеть такое. Какая разница по идее передается по значению или по ссылке. А если и есть - так пусть оптимизатор внутри себя как то решит. Получается для написания элементарнейшого запроса надо перетрусить все кишки сиквел сервера.
Вот я еще наткнулся на глючок. Вроде как считается что сиквел сервис должен быть достаточно умным, что бы инлайн подставлять хранимые функции? Так нифига. Всегда хранимые функции у меня отрабатываются гораздо медленее чем тот же код вписаный - даже самый элементарный

Когда я только ознакомился с базами и с SQL много лет назад, то тоже так думал про SQL.
А потом когда освоил ряд баз и накопил большой опыт в разных их областях (development, DBA. design), то перестал так думать.
А вообще, хорошо на SQL мало кто умеет писать, хотя просто SQL пишут почти все. Для игрушечных баз - это вполне подходит. Когда SQL становится сложный и вовлекает большой объём данных, то, как правило, происходит эпический затык и всякие там гуру в областях из Java и С++ вопят " Почему это работало в development и нихрена не работает в production?".

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

[Alexandr]

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

Да ладно вам, C++ освоить сильно сложнее, чем скуль (да и жабу тоже, с ее многочисленными библиотеками)