Спасибо за понимание!
Времени ушло действительно немало, и очень надеюсь, что оно было потрачено не зря и материал окажется многим полезен. ;)
Спасибо так же моей семье, которая понимала и поддерживала меня все это время, мирясь с моей «недоступностью» и временами терпя меня такого «противного».
Получил еще прирост в скорости, за счет уменьшения длины идентификатора варианта:
-- получаем допустимые цифры в пустых ячейках
SELECT
-- формируем идентификатор варианта - укорачиваем его для более быстрого поиска
RIGHT(CONCAT('0',CAST(CellNo AS varchar(2)),CHAR(ASCII('a')+Value-1)),3) ID,
Да, красивое и быстрое решение.
Проверил запрос для Oracle на тех же вариантах — решает в пределах 4-х секунд:
1) 1.079 сек. (мое решение — 10.930 сек.)
2) 2.991 сек. (мое решение — 12.970 сек.)
3) 3.037 сек. (мое решение — 9.047 сек.)
Oracle запрос...
with x( s, ind ) as
( select sud, instr( sud, ' ' )
from ( select ' 15 34 89 3 2 6 8 47 9 9 2 11 2 5 9 74' sud from dual )
--from ( select ' 71 9 8 3 6 49 7 5 1 9 9 2 6 3 8 2 8 5 76 6 7 7 4 35 ' sud from dual )
--from ( select ' 7 9 42 3 4 8 5 7 1 6 9 3 5262 7 3 9 8 ' sud from dual )
union all
select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
, instr( s, ' ', ind + 1 )
from x
, ( select to_char( rownum ) z
from dual
connect by rownum <= 9
) z
where ind > 0
and not exists ( select null
from ( select rownum lp
from dual
connect by rownum <= 9
)
where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
or z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
or z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
+ trunc( ( ind - 1 ) / 27 ) * 27 + lp
+ trunc( ( lp - 1 ) / 3 ) * 6
, 1 )
)
)
select s
from x
where ind = 0
Здесь, согласен с вами – для компьютера это очень долго.
Мое решение – решение в лоб, сделанное на скорую руку. Основная цель была получить правильный результат за приемлемое время, при этом затратив минимум своих мыслительных ресурсов – решение находится при помощи 2-х основных несложных запросов и одного цикла.
Решение получено, теперь есть куда стремиться. ))
Думаю, основные тормоза происходят из-за выражения «t.VariantPath LIKE '%'+i.ID+'%'».
Да, конструкция OUTPUT может оказаться полезной новичкам, для того, чтобы увидеть какие изменения произошли по факту, особенно это может оказаться полезным при изучении ими оператора MERGE. Так что, возможно действительно стоит дописать отдельный раздел по этой конструкции. Допишу по мере появления свободного времени.
Я как-то упустил здесь упомянуть о конструкции OUTPUT, т.к. она не входит в мой обыденный рацион, т.е. в моем случае она не является жизненно важной конструкцией, хотя мне часто приходится проводить операции модификации данных используя DML в чистом виде. Просто у меня, за все время работы, выработалась привычка делать предварительную проверку, т.е. я сначала пишу SELECT со всеми необходимыми условиями, на котором убеждаюсь в правильности выборки данных, которые впоследствии будут подвергнуты модификации. После, уже этот SELECT переделывается в команду модификации. Видимо поэтому, как таковой надобности в конструкции OUTPUT я не испытываю, т.к. знаю наперед что будет добавлено, удалено или изменено. В статье, в первую очередь, я хотел показать важность умения пользоваться конструкциями оператора SELECT, для того чтобы человек умел правильно формулировать свои намерения.
Но у конструкции OUTPUT, я думаю, есть и более важное предназначение – она позволяет не только получить, но и зафиксировать (OUTPUT … INTO …) информацию о том, что уже произошло по факту, то есть после выполнения операции модификации. Она может оказаться больше полезна в случае логированния произошедших действий, в некоторых случаях, можно это использовать, как хорошую альтернативу тригерам (для прозрачности действий).
Да, я знаю, что при помощи APPLY можно вызывать функции, возвращающие TABLE, но и в таком виде их можно иногда использовать. Учебник посвящен именно теме чистого SQL (без применения TSQL) и в большей степени для людей, только начинающих его изучать. Первым делом я пытался донести понимание сути именно базовых конструкций, на них и сделан в первую очередь основной упор – три части посвящено именно им и только один раздел посвящен теме подзапросов, потому что на больших наборах они действительно очень неэффективны.
Я и сам стараюсь как можно реже прибегать к использованию подзапросов, поэтому я написал:
Но я бы рекомендовал в первую очередь всегда пытаться решить задачу стандартными конструкциями оператора SELECT, и, если этого не получается, прибегать к помощи подзапросов.
За счет того что в первом случае выполнится 2 подзапроса (один для получения emp.ID, а второй для emp.Name) для каждой строки, а в случае APPLY только один (и emp.ID и emp.Name получаются одним подзапросом).
Всему, свое время. Про подзапросы, я скорее всего, буду рассказывать в следующей части, и не в первую очередь, а где и как их применять это уже насколько хватит фантазии. ;) Я специально пока не затрагивал их, т.к. с подзапросами, начинающие, без знания других основных возможностей оператора SELECT, порой такие трехэтажные конструкции могут нагородить…
В вашем примере, вы наверно хотели сказать, что-то типа:
SELECT DepartmentID,SUM(Salary) SalaryAmount
FROM Employees emp
GROUP BY DepartmentID
HAVING EXISTS(SELECT * FROM Departments dep WHERE dep.ID=emp.DepartmentID)
или
SELECT DepartmentID,SUM(Salary) SalaryAmount
FROM Employees emp
GROUP BY DepartmentID
HAVING (SELECT TOP 1 dep.Name FROM Departments dep WHERE dep.ID=emp.DepartmentID ORDER BY dep.Name)=N'ИТ'
?
Примеры в данном случае абсолютно абстрактные и не несут какого-то определенного смысла.
В случае использования EXISTS, нет смысла писать (SELECT TOP 1), достаточно (SELECT *), оптимизаторы современных СУБД (по крайней мере могу судить по MS SQL и Oracle), воспримут это абсолютно одинаково.
Да, здесь вы правы, в зависимости от преобразуемого типа данных и возможности использования разных стилей, функция CONVERT может возвращать разный результат. Причем, для каждого типа данных может использоваться свой набор стилей. Конечно, в случае преобразования целого числа в вещественное (которое я привел в целях демонстрации целочисленного и вещественного деления) это не так критично, но в некоторых случаях это действительно важно, например, в указанном вами случае, когда делается преобразование числового значения в строку (varchar).
Хотел расширить ваш пример и показать в виде комментария, но объем для комментария получился немного большим. Поэтому, давайте я добавлю небольшой раздел про нюансы преобразования непосредственно в статью. Возможно данная информация, кому-то будет полезна.
Спасибо автору! Очень полезная библиотека!
Столкнулся с проблемой, когда в строках таблицы у нас есть объединенные колонки.
В этом случае новые строки формируются не верно и все съезжает (приложил картинку).
Было бы круто, если бы вы доработали свою библиотеку с учетом этого.
Времени ушло действительно немало, и очень надеюсь, что оно было потрачено не зря и материал окажется многим полезен. ;)
Спасибо так же моей семье, которая понимала и поддерживала меня все это время, мирясь с моей «недоступностью» и временами терпя меня такого «противного».
Теперь решение находится в пределах 6 секунд:
Думаю, на этом поставлю точку.
Уменьшил время выполнения почти в 2 раза:
Проверил запрос для Oracle на тех же вариантах — решает в пределах 4-х секунд:
1) 1.079 сек. (мое решение — 10.930 сек.)
2) 2.991 сек. (мое решение — 12.970 сек.)
3) 3.037 сек. (мое решение — 9.047 сек.)
Мое решение – решение в лоб, сделанное на скорую руку. Основная цель была получить правильный результат за приемлемое время, при этом затратив минимум своих мыслительных ресурсов – решение находится при помощи 2-х основных несложных запросов и одного цикла.
Решение получено, теперь есть куда стремиться. ))
Думаю, основные тормоза происходят из-за выражения «t.VariantPath LIKE '%'+i.ID+'%'».
Я не большой любитель судоку, но в таком варианте для меня это достаточно интересная задача. ))
Да, конструкция OUTPUT может оказаться полезной новичкам, для того, чтобы увидеть какие изменения произошли по факту, особенно это может оказаться полезным при изучении ими оператора MERGE. Так что, возможно действительно стоит дописать отдельный раздел по этой конструкции. Допишу по мере появления свободного времени.
Я как-то упустил здесь упомянуть о конструкции OUTPUT, т.к. она не входит в мой обыденный рацион, т.е. в моем случае она не является жизненно важной конструкцией, хотя мне часто приходится проводить операции модификации данных используя DML в чистом виде. Просто у меня, за все время работы, выработалась привычка делать предварительную проверку, т.е. я сначала пишу SELECT со всеми необходимыми условиями, на котором убеждаюсь в правильности выборки данных, которые впоследствии будут подвергнуты модификации. После, уже этот SELECT переделывается в команду модификации. Видимо поэтому, как таковой надобности в конструкции OUTPUT я не испытываю, т.к. знаю наперед что будет добавлено, удалено или изменено. В статье, в первую очередь, я хотел показать важность умения пользоваться конструкциями оператора SELECT, для того чтобы человек умел правильно формулировать свои намерения.
Но у конструкции OUTPUT, я думаю, есть и более важное предназначение – она позволяет не только получить, но и зафиксировать (OUTPUT … INTO …) информацию о том, что уже произошло по факту, то есть после выполнения операции модификации. Она может оказаться больше полезна в случае логированния произошедших действий, в некоторых случаях, можно это использовать, как хорошую альтернативу тригерам (для прозрачности действий).
Для тех, кто дошел до конца, решил дописать еще один раздел «Приложение – бонус по оператору SELECT».
Да, я знаю, что при помощи APPLY можно вызывать функции, возвращающие TABLE, но и в таком виде их можно иногда использовать. Учебник посвящен именно теме чистого SQL (без применения TSQL) и в большей степени для людей, только начинающих его изучать. Первым делом я пытался донести понимание сути именно базовых конструкций, на них и сделан в первую очередь основной упор – три части посвящено именно им и только один раздел посвящен теме подзапросов, потому что на больших наборах они действительно очень неэффективны.
Я и сам стараюсь как можно реже прибегать к использованию подзапросов, поэтому я написал:
Вот планы обоих запросов:
В вашем примере, вы наверно хотели сказать, что-то типа:
или
?
Примеры в данном случае абсолютно абстрактные и не несут какого-то определенного смысла.
В случае использования EXISTS, нет смысла писать (SELECT TOP 1), достаточно (SELECT *), оптимизаторы современных СУБД (по крайней мере могу судить по MS SQL и Oracle), воспримут это абсолютно одинаково.
Да, здесь вы правы, в зависимости от преобразуемого типа данных и возможности использования разных стилей, функция CONVERT может возвращать разный результат. Причем, для каждого типа данных может использоваться свой набор стилей. Конечно, в случае преобразования целого числа в вещественное (которое я привел в целях демонстрации целочисленного и вещественного деления) это не так критично, но в некоторых случаях это действительно важно, например, в указанном вами случае, когда делается преобразование числового значения в строку (varchar).
Про функции CAST, CONVERT и стили можно подробно почитать в MSDN – «Функции CAST и CONVERT (Transact-SQL)»: msdn.microsoft.com/ru-ru/library/ms187928.aspx
Хотел расширить ваш пример и показать в виде комментария, но объем для комментария получился немного большим. Поэтому, давайте я добавлю небольшой раздел про нюансы преобразования непосредственно в статью. Возможно данная информация, кому-то будет полезна.
У меня MS SQL 2014.