Ошибка в запросе. Это так и должно быть?!

0 - 26.02.2012 - 11:51

Billi

41 - 26.02.2012 - 18:02

37-Lexusss >Примерчик набросал
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#temp'))
drop table #temp

create table #temp(val char(1), unique (val) WITH (IGNORE_DUP_KEY = ON))

insert into #temp
select '1'
union all
select '2'
union all
select '2'
union all
select '3'

select * from #temp

Результат
1
2
3

Гость

42 - 26.02.2012 - 18:19

(39)

Цитата:

Сообщение от Billi

левое соединение поставила сама одиныска

Как галочки поставишь, так соединение и будет. Не надо вот это отмазываться :) И вообще, слышать стрелки на конструктор от писателя на чистом T-SQL - это странно :)

Цитата:

Сообщение от Billi

в состав какой-нибудь номенклатуры воткнуть

Так и сделай "ВЫБРАТЬ МАКСИМУМ(Номенклатура), Конструкция" и не парься про все остальное.
(40) Не знал про такой ключик. Я скорее теоретик, нежели спец по всем фенечкам MS SQL.

Billi

43 - 26.02.2012 - 18:26

42-Lexusss >Конструктор-конструктор. Это 1с во всем виновато! :))

Billi

44 - 26.02.2012 - 18:27

Цитата:

Сообщение от Lexusss

Так и сделай "ВЫБРАТЬ МАКСИМУМ(Номенклатура), Конструкция" и не парься про все остальное.

Так я и не парюсь, давно уже проехал этот участок в конфе :)))

Гость

45 - 26.02.2012 - 22:01

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

Чучундер

46 - 27.02.2012 - 03:31

Модератороам - а поместить в Арихив?
- полезная ветка!!!

Billi

47 - 27.02.2012 - 05:27

Цитата:

Сообщение от Управление торговлей 11

что за нездоровое желание думать за 1с-ный оптимизатор?

А почему нездоровое?
Очень даже нормальное желание.
Гораздо хуже молотить запросы, не имея ни малеёшего представления о внутренних механизмах.
Вот тогда уж точно без оптимизатора не обойтись. :)
Но и в этих случаях он не всегда выручает. Иногда писатели такое наворотят, что у оптимизатора башню сносит :)

Цитата:

Сообщение от Управление торговлей 11

коллеги, используйте временные таблицы вместо вложенных запросов

Эта ветка как раз о том, как запихать данные во временную таблицу :)

Гость

48 - 27.02.2012 - 08:10

автор, напиши что надо получить ? Что есть документ, что есть номенклатура, что есть конструкция. Зачем со своим SQL лезть в 1с ? Если ты игнорируешь все советы и пытаешься изобретать велосипед, хотя есть авто и правила его эксплуатации. В 1с языке запросов нет инсертов, а ты пытаешься лбом биться о стену. Напиши задание и я тебе напишу решение для 1с. Разберем по полочкам

Гость

49 - 27.02.2012 - 08:44

(45)

Цитата:

Сообщение от Управление торговлей 11

1с-ный оптимизатор

Это фантастика! 1С просто отдает на уровень сервера SQL транслитерацию наших запросов и раскрытие виртуальных таблиц в язык физических таблиц. Оптимизацией там и не пахнет, посмотрите хотя бы на производительность сложных запросов файл-серверной базы. Так что нужно думать, что пишешь, особенно на средних и больших объемах баз.
Временные таблицы - это вообще большая проблема, но и возможный большой плюс. Проблема - невозможность их использования в динамических списках и RLS (вобщем то, по одной и той же причине). Плюс, который может быть и минусом, - сервер не оптимизирует заполнение временных таблиц, а прямо выполняет ту последовательность генерации временных таблиц, что задана у нас в пакете. При вложенных запросах, сервер SQL основывается на статистике и может построить совершенно волшебный план выполнения запроса, который среднестатистическому программисту покажется нереальным и несоответствующим его требованиям в запросе. Таким образом, вложенные запросы - для ленивцев, неспецов, которые не смогут определить оптимальный порядок выполнения запроса, а так же для тиражных решений, в которых может быть совершенно различная статистика индексов таблиц в зависимости от конкретного внедрения (где то на 10 номенклатуры 10000 покупателей, в другом же месте на 10000 номенклатуры всего лишь 10 покупателей). С другой стороны пакеты временных таблиц позволяет низкоуровнево определять план выполнения запроса, что может давать большой бонус к производительности, если оптимизатор SQL ошибается при построении планов выполнения.

Цитата:

Сообщение от Billi

у оптимизатора башню сносит

Точнее сказать, оптимизатор использует недостаточный объем статистики. Ведь он не может предсказать, что при пересечении двух таблиц по 1 000 000 записей разработчик предполагает получение 10 строк, поэтому во избежание получения множества пересечения в 1 000 000 000 000 строк, оптимизатор может сначала присоединить какую нить третью таблицу в 100 записей, и к результату уже присоединять вторую миллионную таблицу. Такой простой пример неверного угадывания плана выполнения может дать снижение производительности в сотни раз. Именно для таких случаев полезнее использовать временные таблицы, если возможно.
(48) Автор сам написал почти правильное решение в (17).

Billi

50 - 01.03.2012 - 10:33

49-Lexusss >Продолжим :)
Сегодня столкнулся с интересным моментом оптимизатора MS-SQL. Правда это не восьмёрка, а семёрка, но, думаю, что это неважно.
Задача такая: нужно найти документ ТабельОтработанногоВремени по определённому сотруднику, который проведён в определённом периоде, и вытащить из этого табеля строку с определённым видом часов по этому сотруднику.
На входе: таблица шапки документа с индексом по IDDOC, таблица строк документа с индексом по IDDOC+LINENO_, таблица журнала документов с индексом DOCTYPE (IDDOCDEF+DATE_TIME_IDDOC), таблица отборов с индексом PARENT (MDID+PARENTVAL+CHILD_DATE_TIME_IDDOC).
Итак, запрос (для начала, без отбора по типу часов, ниже будет понятно, почему):

SELECT $Табель.ПолнотаВводаДанных
, ТабельСтроки.*
FROM $Документ.ТабельОтработанногоВремени Табель (NOLOCK)
INNER JOIN (
SELECT Жур.IDDoc
FROM _1SJourn Жур (NOLOCK)
INNER JOIN _1SCRDOC Отбор (NOLOCK) ON Отбор.MDID = $ГрафаОтбора.Сотрудник
AND Отбор.ParentVal = :ВыбСотрудник*
AND Отбор.CHILD_DATE_TIME_IDDOC = Жур.DATE_TIME_IDDOC
AND Жур.IDDOCDEF = $ВидДокумента.ТабельОтработанногоВремени
WHERE Жур.Closed & 1 = 1) Табеля
ON Табеля.IDDOC = Табель.IDDOC
AND $Табель.ДатаПериода = :ДатаПериода
INNER JOIN $ДокументСтроки.ТабельОтработанногоВремени ТабельСтроки (NOLOCK)
ON ТабельСтроки.IDDOC = Табель.IDDOC
AND $ТабельСтроки.Сотрудник = :ВыбСотрудник
-- AND $ТабельСтроки.ТипЧасов = $Перечисление.ТипыЧасов.Вечерние

Мною предполагаемый план запроса:
1. Подзапросом Табеля формируем список id проведённых документов в которых присутствует выбранный сотрудник. Список формируется путём внутреннего соединения таблицы журнала с таблицей отбора по полю DATE_TIME_IDDOC, по индексам DOCTYPE таблицы журнала и PARENT таблицы отбора.
2.На основе полученного списка документов с помощью внутреннего соединения формируем список записей таблицы шапки документа. Список записей формируется по индексу IDDOC, с учетом условия по дате периода документа.
3.Формируем список записей таблицы строк документа с помощью внутреннего соединения с полученным списком записей таблицы шапки документа. Список формируется по индексу IDDOC, с учетом условия по выбранному сотруднику.

Действительный план запроса:
Пункты 1 и 2 совпадают.
3.Формируется список записей таблицы строк документа с помощью внутреннего соединения с полученном в подзапросе Табеля списком документов. Список формируется по индексу IDDOC, с учетом условия по выбранному сотруднику.
4.С помощью внутреннего соединения списка записей шапки со списком записей строк получается результат.

Казалось бы всё хорошо, план работает практически по плану. Но как только я добавляю условие по типу часов, то план выполнения запроса резко меняется до неузнаваемости:
1.Ищутся все документы в таблице шапки документов по условию дата периода.
2.Полученный список соединяется с таблицей строк документа по индексу IDDOC, при соединении учитываются условия по сотруднику и типу часов. В результате получается список записей таблицы строк документа.
3.4.К полученному списку записей с помощью внутреннего соединения присоединяется таблица журнала документов по индексу IDDOC. Учитывается флаг проведения документа.
5.Полученный результат через внутреннее соединение соединяется с таблицей отбора по индексу PARENT (казалось бы зачем? уже не нужно :) )

И теперь я в тупике.
Оставить всё как есть, или же с помощью временной таблицы заставить скуль выполнять запрос по первоначальному плану?

Гость

51 - 01.03.2012 - 13:09

(50) При чем тут изначальный вопрос?
ЗЫ: Консультации 2,5к/час

Billi

52 - 01.03.2012 - 13:14

51-Lexusss >Ты про какой изначальный?
"Консультации 2,5к/час", а я и не прошу консультаций.
Вопрос уже решён.

Я просто предлагаю порассуждать на тему работы оптимизатора MS-SQL/
Но если у тебя нет мыслей по этому вопросу, то можешь и не рассуждать :)

Гость

53 - 01.03.2012 - 16:41

(51) автору просто нравится в Москву через Камчатку (а-ля "мы не ищем лёгких путей"). Что касается 1с всё уже подсказали. Если вопрос решен - то пишут решение

Billi

54 - 01.03.2012 - 17:54

Многоуважаемый Jimbo, у Вас есть, что сказать по поводу алгоритма работы оптимизатора MS-SQL?
Может быть, Вам знакомы какие-то детали, которые не знаю я, и Вы можете мне разъяснить, в каких случаях сервер выполнят seek вместо scan?
Может быть, Вы можете развеять мои сомнения по поводу ситуации в (50), и опубликовать причины возникновения ситуации?
Или может быть, Вы думаете, что я юнец, который первый раз нажал на кнопку "Включить действительный план выполнения" и увидел, что сервер БД может не только бездумно выполнять мои инструкции, но и просто игнорировать их, заменяя на свои?

Гость

55 - 01.03.2012 - 18:06

(54) автор, я могу лишь пояснить как решить задачу в 1с типовыми средствами. Так как советует 1с. Всё равно что писать на ассемблере и рассуждать про оптимизаторы С++. У есть есть свои плюсы и минусы - главное разделять и властовать, а не делать из мухи слона.

Billi

56 - 01.03.2012 - 18:07

51-Lexusss >Я понимаю твою иронию, но вопрос стоял не в том, что бы помочь мне в отладке запроса, а в том, что бы услышать мнение профи по вопросам работы оптимизатора на конкретном примере.
Твой опыт работы с БД большого объёма, при большом количестве активных пользователей, был бы очень полезен.

Billi

57 - 01.03.2012 - 18:17

55-Jimbo >Если ты ещё не понял. Я не нуждаюсь в пояснениях по решению задач, которые я задал в этой ветке.
Эти вопросы заданы в качестве, скажем, раздражителя, для побуждения к дискуссии.
Ибо, очень много начинающих студентов, решая задачу средствами 1С не понимают последствий своих действий.

Может я слишком тонко начал этот разговор?
Ну, как смог :)

Гость

58 - 01.03.2012 - 20:57

Эх, где же наша Helen с ее воскликом ТТО! Сервер УМНЕЕ тебя, когда строит план. Индекс использования таблицы отбора по сотруднику даст намного ХУДШИЙ результат, нежели отбор таблицы по дате! Посмотри статистику по дате по таблице документов по сравнению со статистикой по сотруднику в таблице отборов. Да и сама таблица отбора наверняка потолще раз этак, в пятьдесят, в зависимости от количества связей документов и отборов (Привет пЕсателям платформы 7.7 и всем ее любителям).
Без отбора по ТипуЧасов это еще не так было важно, но статистика по наполненности таблицы (а она наверняка тоже очень плотная) подсказала SQL, что результирующая таблица получится компактной. Присоединить к этому общий журнал - видимо показалось менее страшным, нежели сканить журнал по отбору.
Решение (если проблема действительно актуальна): Посмотреть по плану выполнения количество строк на каждом шаге при варианте временной таблице.
Выкинуть использование таблицы отборов и посмотреть в этом случае. Скорее всего, это будет гораздо лучший вариант.
Вывод: Не знаешь броду - не лезь в воду. Оптимизатор гораздо лучше построит план выполнения, нежели 98% 1Снегов.

Гость

59 - 01.03.2012 - 20:58

58+ Я раздразился. Считай, ты достиг результата - я даже посмотрел, что за зверь этот MDID

Billi

60 - 02.03.2012 - 00:58

58-Lexusss >Ура! Свершилось!
Теперь давай дискутировать.
Отодвигаем в сторону превосходство блондинок,
и способность калькуляторов воспитывать детей (это я про то, что сервер УМНЕЕ меня:) ),
и то, что ты своим подчинённым втираешь по поводу 98% процентов, а затем переводишь тему в плоскость умности оптимизатора.
Переходим к практической части.
Повторяю ещё раз.
Вопрос в том, чтобы разобраться в ситуации, когда, казалось бы, очевидный план выполнения запроса (по крайней мере, я постарался так сделать) сервер изменяет на, как минимум, неожиданный. Плюёт на индексы, игнорирует спец. таблицы и т.д. Просто издевается над автором запроса :)
Давай просто разберём твой комментарий в (58), отбросив все подковырки.

Цитата:

Сообщение от Lexusss

Индекс использования таблицы отбора по сотруднику даст намного ХУДШИЙ результат, нежели отбор таблицы по дате! Посмотри статистику по дате по таблице документов по сравнению со статистикой по сотруднику в таблице отборов. Да и сама таблица отбора наверняка потолще раз этак, в пятьдесят, в зависимости от количества связей документов и отборов (Привет пЕсателям платформы 7.7 и всем ее любителям).

Раберём этот тезис.
Оптимизатор запросов SQL-сервера посмотрел на статистику поля ДатаПериода таблицы шапки документа ТабельОтработанногоВремени, сравнил её со статистикой поля ParentVal (Значение отбора) таблицы Отбобора и решил, что эффективнее отбирать по неиндексированному полю ДатаПериода, чем по инексированному полю ParentVal.
Вопрос: почему?
Ответ: А потому, что статистика!
Что есть статистика в понимании SQL-сервера?
Если очень просто, то это распределение данных в таблице.
На примере из (50) можно увидеть следующее (правда я не привёл явно достаточных данных для анализа, в этом моя вина, но я исправлюсь  ) на каждую ДатаПериода существует определённое количество документов ТабельОтработанногоВремени , которое не очень значительно изменятся в каждом следующем периодом. В моём случае это около 80-ти документов месяц. Если учесть, что в базе хранится информация всего за три года, то в таблице шапки документов всего около 3-х тысяч записей.
С другой стороны в таблице отбора по сотрудникам значительно больше записей (по словам Lexusssа – раз в 50, я думаю, что значительно больше. Хотя, чего думать, можно проверить:
Таблица шапки документа – 3333 записей, таблица отбора – 340525, всего в 100 раз  , а это значительно больше :) ).
Но здесь важно не количество записей, важно распределение записей с одинаковыми значениями в таблице.
Сотрудников в базе – 3593, и они разбросаны в таблице отборов гораздо более хаотично, чем документы с одинаковым периодом в таблице шапки документов. Причём, в случае с таблицей отбора, надо ещё учитывать и вид документа, а Табелей в базе 3333 из общего количества документов, которое равно 44379.
И получается, что серверу гораздо проще выбрать документы по одному периоду без индекса, чем документы из таблицы отбора по одному сотруднику по индексу.
Идём дальше

Цитата:

Сообщение от Lexusss

Без отбора по ТипуЧасов это еще не так было важно, но статистика по наполненности таблицы (а она наверняка тоже очень плотная) подсказала SQL, что результирующая таблица получится компактной. Присоединить к этому общий журнал - видимо показалось менее страшным, нежели сканить журнал по отбору.

А вот тут интересный момент.
Чем же так ТипЧасов помешал?
А, всё ясно!
Я опять умолчал о том, что записей, с типом часов Вечерние, в таблице строк документов всего 3, из 54080. (Каюсь, моя вина тут присутствует. Я – тот самый тупой одинысник, который входит в те самые 98%).
И тут всё становится ясно. Серверу гораздо проще найти одну запись из 54 тысяч без индекса, чем городить цепочку из отборов-соединений с использованием инексов.

И мне почему-то думается, что вышеизложенная теория не верна. И оптимизатор всё-таки ошибается.

Замечание. Если вместо Вечерних часов указать другие (основные, ночные, сверхурочные), то запрос выполняется по задуманному сценарию.

З.Ы. Я отключил оптимизатор для этого запроса от греха подальше, и всё стало работать значительно быстрее :)
К тому же, скоро количество записей с вечерними часами очень быстро начнёт расти, и тогда уж лучше сервер будет выполнять запросы по моему велению, а не по своему хотению :)

Гость

61 - 02.03.2012 - 12:26

Может кто присоединится из завсегдатаев форума? Влом спорить и рассказывать, что поиск по индексу с отбором по полю, не входящем в индекс - это два обращения к таблице, и что поиск по индексу не всегда быстрее скана таблицы и еще миллион других азбучных истин.
ПЫСЫ: С порядком цифр 50 или 100 - все таки угадал? ))) Хоть и не думал, что кому то может понадобится такая куча табелей.

Интернет-форум Краснодарского края и Краснодараг. Краснодар, Краснодарский край 2022г.

Интернет-форум Краснодарского края и Краснодара
г. Краснодар, Краснодарский край 2022г.