Проблема с поиском
Я с базами и SQL работаю совсем недавно.
Есть вот такая штука. С помощью запроса
То-есть на запрос search="123" выдает "FD45123" но не выдает " FD45123". Как это исправить и вообще из-за чего это? Согласно документации с msdn:
И еще такой вопрос. В моем запросе я не использую под запросы "явно". То-есть:
@" from dbo.Payments, dbo.Clients, dbo.Agreements, dbo.Countries , dbo.Departments" +
@" where dbo.Payments.PM_DocNumber LIKE '%" + search + @"' and dbo.Payments.PM_DP_ID=dbo.Departments.DP_ID and dbo.Clients.CL_ID=dbo.Payments.PM_CL_ID and dbo.Agreements.AG_Code=dbo.Payments.PM_DocNumber and dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID" +
@" group by dbo.Payments.PM_PT_ID, dbo.Payments.PM_ID, dbo.Payments.PM_DocNumber, dbo.Payments.PM_SumTarget, dbo.Clients.CL_FirstName, dbo.Clients.CL_MiddleName, dbo.Clients.CL_Name, dbo.Countries.CN_Name";
После того, как я написал этот запрос, я вычитал, что вообще необходимо делать под запросы. Например в моем случае что-то вроде: ищем PM_DocNumber, берем его PM_DP_ID, затем подзапрос- берем Departments.DP_ID = PM_DP_ID. И т.д.
За исключением глюка с пробелом никаких проблем не выявил. Принципиально ли разделять запрос на под запросы или MS SQL не видит разницы?
Я с базами и SQL работаю совсем недавно.
Есть вот такая штука. С помощью запроса
То-есть на запрос search="123" выдает "FD45123" но не выдает " FD45123". Как это исправить и вообще из-за чего это? Согласно документации с msdn:
Уверен, что правильно читал msdn? Уверен, что тебе надо %, а не *? И на какой платформе? Например, в Аксессе именно через *.
На мсдне, только относительно SQL-Transact написано, это диалект для SQL Server, так что при использовании других СУБД информация из мсдна может быть не актуальна. Читай только родные факи для СУБД.
@" from dbo.Payments, dbo.Clients, dbo.Agreements, dbo.Countries , dbo.Departments" +
@" where dbo.Payments.PM_DocNumber LIKE '%" + search + @"' and dbo.Payments.PM_DP_ID=dbo.Departments.DP_ID and dbo.Clients.CL_ID=dbo.Payments.PM_CL_ID and dbo.Agreements.AG_Code=dbo.Payments.PM_DocNumber and dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID" +
@" group by dbo.Payments.PM_PT_ID, dbo.Payments.PM_ID, dbo.Payments.PM_DocNumber, dbo.Payments.PM_SumTarget, dbo.Clients.CL_FirstName, dbo.Clients.CL_MiddleName, dbo.Clients.CL_Name, dbo.Countries.CN_Name";
Когда кто-то пишет такой запрос умирает котенок. :(
Был ли первый запрос протестирован отдельно от всех остальных? Если да, то как? Можно выложить полностью запрос, на котором тестировали LIKE? Вполне возможно, что намудрили где-то или еще что-то. У меня, например, никаких таких ситуаций никогда не наблюдалось.
Почему? Поясни пожалуйста.
По поводу тестирования запроса с LIKE, отдельно я не пробовал. Щас попробую и отпишусь.
@" from dbo.Payments" +
@" where dbo.Payments.PM_DocNumber LIKE '%" + search + @"'" +
@" group by dbo.Payments.PM_DocNumber, dbo.Payments.PM_SumTarget";
Как я догадываюсь проблема в моем первом "кривом" запросе. Проблема в том, что я не совсем понимаю логику под запроса.
P.S.: Я смотрел на msdn, так как работаю с MSSQL. Если быть точнее, то я пишу прогу на C# с подключением к MSSQL. Запрос со * не работает.
Когда будет исполняться эта строка, то будет производится декартово произведения ВСЕХ кортежей-записей по очереди. То есть у тебя будет таблица примерно из Н^5, где Н количество записей. При сотни записей уже ужасающее число будет. Как уместится 10ГБ данных в оперативной памяти за раз (с учетом того, что одна запись в исходной таблице занимает 1 байт, что уже заведомо не так)? А если больше сотни записей? Конечно, и на этот счет MSSQL имеет парочку трюков, но согласись, быстрее работать 5 раз с таблицами по Н, чем один раз с таблицей из Н^5 записей.
Кроме всего прочего, увеличится не только количество записей, но и длина каждой записи. А значит сравнения условий будет еще медленнее. Оптимизатор рыдает кровью, когда ему скармливают такие вещи.
В первую очередь подумай, можно ли использовать подзапросы, чтобы уйти от таблиц с Н^5 записей. Не проще ли разбить на две-три таблицы-подзапроса, а потом их склеить? Ведь когда ты делаешь подзапрос, то он вернет результатов гораздо меньше Н, что и уменьшит число записей, которые нужно поместить в оперативную память и так далее. По мимо всего прочего, два подзапроса к разным таблицам можно осуществлять параллельно, что еще ускорит. Сервера то уже давно не одним ядром гоняют весь этот ужас.
@" from dbo.Payments" +
@" where dbo.Payments.PM_DocNumber LIKE '%" + search + @"'" +
@" group by dbo.Payments.PM_DocNumber, dbo.Payments.PM_SumTarget";
Как я догадываюсь проблема в моем первом "кривом" запросе. Проблема в том, что я не совсем понимаю логику под запроса.
Часть проблемы решилась. Осталось понять, что же нагородили такого, что не выводит в первом запросе. Может, другие условия отсекают " FD45123"?
Посмотри JOIN. :rolleyes:
Попутно есть еще вопрос про под запросы:
согласно моей задаче, мне необходимо
а) по номеру найти строку в таблице Payments
б) из выделенной строки узнать значение ячеек (PM_CL_ID, PM_DP_ID, PM_DocNumber)
в) делать под запросы к разным таблицам используя значения ячеек из пункта (б).
Получается вложенность запросов пункт (б) с (в)PM_CL_ID, пункт (б) с (в)PM_DP_ID, пункт (б) с (в)PM_DocNumberтак. вот, как мне передать то самое значение (например PM_CL_ID) в под запрос?
По поводу JOIN. Я так понял, что при помощи данного оператора можно объединить несколько таблиц в одну по названным параметрам. Получается, что само по себе использование данного оператора приведет меня к тому же H^5, ну или если использовать под запросы как я выше описал, то H^4. То есть после JOIN останется одна таблица, состоящая из всех включенных таблиц. Правильно?
Смотри:
Видно, что таблица Countries лепится исключительно по таблице Agreements. Почему бы в FROM не загнать подзапрос:
FROM dbo.Payments, dbo.Clients, dbo.Departments,
(SELECT dbo.Countries.CN_Name AS CN_Name, dbo.Agreements.AG_Code AS AG_Code
FROM dbo.Agreements, dbo.Countries
WHERE dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID) AS PUTSOMENAME
WHERE бла-бла-бла , PUTSOMENAME.AG_Code=dbo.Payments.PM_DocNumber
Идея понятна?
То есть мы исключили две таблицы, обработали их быстрее, при этом сократили длину записи до двух элементов. И это мы не использовали еще JOIN! Можно оптимизировать и дальше, в таком или другом духе. Попробуйте потестировать данный запрос, заметите прирост в скорости скорее всего.
Я предполагаю, что такой конструкцией мы создаем таблицу PUTSOMENAME и в ней определяет столбцы CN_Name=dbo.Countries.CN_Name, AG_Code=dbo.Agreements.AG_Code согласно условиям WHERE... . Правильно?
Я предполагаю, что такой конструкцией мы создаем таблицу PUTSOMENAME и в ней определяет столбцы CN_Name=dbo.Countries.CN_Name, AG_Code=dbo.Agreements.AG_Code согласно условиям WHERE... . Правильно?
Бинго! Но не совсем верно.
dbo.Countries.CN_Name AS CN_Name создает альяс(псевдоним) CN_Name для dbo.Countries.CN_Name. Здесь сделано для удобства. А тут (SELECT бла-бла-бла) AS PUTSOMENAME не только. Так как в дальнейшем мы собираемся использовать ИМЕННО склееную таблицу, то нужно обращаться ИМЕННО к ней. А как это сделать, если любой другой подзапрос создаст новую таблицу? Только через альясы, что мы и сделали.
@" FROM dbo.Clients" +
@" (SELECT dbo.Countries.CN_Name AS CN_Name, dbo.Agreements.AG_Code AS AG_Code" +
@" FROM dbo.Agreements, dbo.Countries"+
@" WHERE dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID) AS COUNTRIES" +
@" (SELECT dbo.Payments.PM_SumTarget AS PM_SumTarget, dbo.Payments.PM_CL_ID AS PM_CL_ID, dbo.Payments.PM_DocNumber AS PM_DocNumber" +
@" FROM dbo.Payments" +
@" WHERE dbo.Payments.PM_DocNumber LIKE '%" + search + @"') AS PAYMENTS" +
@" INNER JOIN COUNTRIES ON dbo.Clients.CL_ID=PAYMENTS.PM_CL_ID and COUNTRIES.AG_Code=PAYMENTS.PM_DocNumber" +
@" group by PAYMENTS.PM_SumTarget, dbo.Clients.CL_FirstName, dbo.Clients.CL_MiddleName, dbo.Clients.CL_Name, COUNTRIES.CN_Name, PAYMENTS.PM_DocNumber ";
Только SQL ругается на ошибки рядом с SELECT, ) и AS. Никак не могу понять почему. Есть ли способ проверить, где именно ошибка? Я уже пытался проверять по частям, получается, что ошибка во втором под запросе. Но сколько бы я его не ковырял, ничего не изменяется.
Однако первоначальная проблема осталась. Запрос на данный момент выглядит так:
@" FROM dbo.Clients," +
@" (SELECT dbo.Countries.CN_Name AS CN_Name, dbo.Agreements.AG_Code AS AG_Code" +
@" FROM dbo.Agreements, dbo.Countries"+
@" WHERE dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID) AS COUNTRIES," +
@" (SELECT dbo.Payments.PM_SumTarget AS PM_SumTarget, dbo.Payments.PM_CL_ID AS PM_CL_ID, dbo.Payments.PM_DocNumber AS PM_DocNumber" +
@" FROM dbo.Payments" +
@" WHERE dbo.Payments.PM_DocNumber LIKE '%" + search + @"') AS PAYMENTS" +
@" WHERE dbo.Clients.CL_ID=PAYMENTS.PM_CL_ID and COUNTRIES.AG_Code=PAYMENTS.PM_DocNumber" +
@" group by PAYMENTS.PM_SumTarget, dbo.Clients.CL_FirstName, dbo.Clients.CL_MiddleName, dbo.Clients.CL_Name, COUNTRIES.CN_Name, PAYMENTS.PM_DocNumber ";
Выдает только ту, что без запятой. Почему так?
Быть может есть смысл проверить этот запрос в SQL service management Express? Только подскажите где там искать результат?
Кстати, надеюсь теперь котята выживут )))
моя программа составляет отчеты за день. В течении дня проводятся платежи (по другой, не моей программе). Для того, чтоб привязать платеж к определенному номеру заявки, необходимо ввести ее номер (собственно указать номер заявки, по которой проводится оплаты). Дальше начинается самое интересное. Некоторые невнимательные менеджеры копируют номер вместе с пробелом, соответственно по таблице клиентов номер без пробела, а по таблице платежей, с пробелом.
Данный мой запрос перво ориентируется на номер в платежах, и затем ищет такой-же в таблице договоров (dbo.Agreements.AG_Code=dbo.Payments.PM_DocNumber). Вот оно и несоответствие. В первой таблице ошибка не может быть допущена, т.к. номер составляется программно, а вот во второй- запросто.
Остается понять, как перестроить запрос так, чтоб искалось совпадение в таблице Agreements от PM_DocNumber. Т.е.:
dbo.Payments.PM_DocNumber LIKE '%dbo.Agreements.AG_Code'- только данный вариант не работает
Не уверен, что так правильно.
В целом запрос выглядит так:
FROM dbo.Clients,
(SELECT dbo.Countries.CN_Name AS CN_Name, dbo.Agreements.AG_Code AS AG_Code
FROM dbo.Agreements, dbo.Countries
WHERE dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID) AS COUNTRIES,
(SELECT dbo.Payments.PM_SumTarget AS PM_SumTarget, dbo.Payments.PM_CL_ID AS PM_CL_ID, dbo.Payments.PM_DocNumber AS PM_DocNumber
FROM dbo.Payments
WHERE dbo.Payments.PM_DocNumber LIKE '%5573') AS PAYMENTS
WHERE dbo.Clients.CL_ID=PAYMENTS.PM_CL_ID and PAYMENTS.PM_DocNumber LIKE '%'+COUNTRIES.AG_Code
group by PAYMENTS.PM_SumTarget, dbo.Clients.CL_FirstName, dbo.Clients.CL_MiddleName, dbo.Clients.CL_Name, COUNTRIES.CN_Name, PAYMENTS.PM_DocNumber
Как там дела с котятами? Так запрос корректен (с точки зрения языка)?
-------------------------------------------------------------
UPD
Ну или тригер на Инсерт повесить? Тоже вариант кстати, сама СУБД будет удалять лишние пробелы. Решений проблемы куча! Надо только подумать немного.
Второй вариант мне не знаком, но предполагаю, что он связан с вмешательством в базу (повесить триггер).
Судя по всему, придется оставить код как он есть. Дальше посмотрим, будут глюки, буду искать способы их решения.
В целом скорость выполнения запроса выросла и по данному запросу результат выдается верный. Так что я думаю оставить пока все как есть.
Огромное спасибо за внимание и советы.
Триггер это не совсем вмешательство в базу. Скорее в СУБД. MS SQL Server поддерживает триггеры. Прочитать про них и все, по шаблонам все вполне понятно и легко. Удалять пробелы не составит труда.
Но дело, конечно, ваше. Спрос с вас, да и все плюшки тоже вам ;)
FROM (SELECT dbo.Countries.CN_Name AS CN_Name, dbo.Agreements.AG_Code AS AG_Code, dbo.Countries.CN_ID AS CN_ID
FROM dbo.Agreements, dbo.Countries
WHERE dbo.Countries.CN_ID=dbo.Agreements.AG_CN_ID) AS COUNTRY
INNER JOIN (
(SELECT dbo.Clients.CL_FirstName AS CL_FirstName, dbo.Clients.CL_MiddleName AS CL_MiddleName, dbo.Clients.CL_Name AS CL_Name, dbo.Clients.CL_ID AS CL_ID
FROM dbo.Clients ) AS CLIENTS
INNER JOIN (SELECT dbo.Payments.PM_CL_ID AS PM_CL_ID, dbo.Payments.PM_DocNumber AS PM_DocNumber, dbo.Payments.PM_SumTarget AS PM_SumTarget
FROM dbo.Payments
WHERE dbo.Payments.PM_DocNumber LIKE '%5573') AS PAYMENTS ON CLIENTS.CL_ID=PAYMENTS.PM_CL_ID
) ON PAYMENTS.PM_DocNumber LIKE '%' + COUNTRY.AG_Code
Я правильно понимаю, запросы выполняется начиная с максимального уровня вложенности. То есть сначала
- ищется номер (dbo.Payments.PM_DocNumber LIKE '%5573')
- затем данные клиента (результат таблица CLIENTS)
- затем объединяется в одну таблицу согласно СLIENTS.CL_ID=PAYMENTS.PM_CL_ID
- затем ищем страну Countries.CN_ID=dbo.Agreements.AG_CN_ID
- и наконец объединяем все во едино
Моя логика верна?
Вы не подумайте, что я пренебрежительно отношусь к вашим советам. Я действительно внимательно прочитал их и решил пока оставить так. На данный момент ваши советы привели, как я полагаю, к большей стабильности в запросе. Сейчас мне необходимо переписать остальные запросы в моей программе. Собственно по этой причине прошу обратить внимание на мое пред идущее сообщение и прокомментировать его.
После чего я займусь изучением триггера в MSSQL. Я думаю, что в обозримом будущем проведу необходимые настройки.
Да и не мастер я по оптимизации запросов. Есть отдельные техники можно смотреть в гугле тут или тут. Достаточно объемная область, кстати.
Да и не мастер я по оптимизации запросов. Есть отдельные техники можно смотреть в гугле тут или тут. Достаточно объемная область, кстати.
Спасибо, посмешили советами в начале темы про "упаковку в подзапросы" и про H^5 строк =)
imAlex,
ваша логика относительно выполнения запроса может быть как верна, так и нет, или даже "сегодня верна, завтра нет". Это зависит от многих факторов, управляет всем этим безобразием (сиречь "порядком выполнения запросов") оптимизатор. Схема по которой выполняется запрос - называется план запроса. План можно посмотреть, например, в SSMS. Как читать планы есть в мсдн.
Кстати, весьма хороший совет вам дали на тему триггера. Только я бы советовал не пробелы там убирать, а вообще формировать строку для будущего поиска и писать ее в отдельное поле. Т.к. использование конструкции "like '%abc'" начисто убивает возможность использования индексов, что сильно снижает производительность (хотя это может быть конечно незаметно на маленьких таблицах), и сколько ни переставляй таблицы в запросах - эффекта будет ноль.
Извините, конечно, но новичку сразу надо было объяснять про план запроса, составленный оптимизатором, про индексированные и неиндексированные данные и так далее? Почти всегда ручная оптимизация лучше машинной (по крайней мере в SQL такое было лет 5-10 назад точно).
Почти вся оптимизация всегда сводится к гаданию на кофейной гущи по планам запроса, которые дергаются из СУБД. Очень часто помогает выделять из одного большого запроса несколько подзапросов (хотя бывает и наоборот, не спорю). Или вы считаете, что в данном запросе не стоило ничего оптимизировать? Если не заметили, то там сравнивались поля у двух таблиц. Даже если бы оптимизатор решил сначала их сравнивать, а потом склеивать, то все равно ушло бы побольше времени и памяти, чем если выделить это в подзапрос. Экономия даже чисто за счет того, что оптимизатор не решился бы выкинуть из полученной таблицы кучу столбцов (а они память кушают), а подзапросом это делается легко. Или я где-то не прав был? Мне надо было писать в первом же сообщении рекомендации из МСДН, а не объяснять на пальцах?
Совершенно верно!
Совершенно неверно. Уже в 2000-м, оптимизатор запросов работал и работает прилично. Конечно можно такого наваять что не оставить шансов оптимизатору. В таком случае ручная "оптимизация" конечно лучше =)
"Вот это прикол!" (с) "+100500". Ну вы даете! Да как же гадать, если в плане уже есть все, только нужно уметь читать то что сервер так любезно предоставил.
Да может и помочь. Даже простое добавление пробела вначале запроса может помочь. Только вот с оптимизацией это не связано.
Я считаю что, если вы уж решили помочь с оптимизацией
1) нужно было запросить предварительную информацию о: версии сервера, индексах, объемах данных, конкретном времени запроса, желаемом времени, действительных планах
2) предупредить, что вклейка строк параметров в текст запроса может привести к атакам sqlInjection и вообще это друной тон.
3) посоветовать привести синтаксис в соответсвие со стандартом анси, а именно переписать все на join. (кстати вы не поверите, декартово произведение с условиями в кляузе where таки приведет к операции inner join, и никак не к Н^5 строк, вот если убрать условия - то да, декартово произведение)
4) привести выражение к, что называется, SARG-able. Чтобы оно могло использоваться в поиске по индексу и не приводило к просмотру всего индекса или таблицы. Для этого можно как раз использовать, например, триггер.
Я обычно (кроме самых очевидных ситуаций) не берусь предсказывать поведение оптимизатора. По этому рассуждать о том, что решил бы оптимизатор не могу, особенно не зная индексы и данные. Сегодня данные одни, завтра статистика изменилась, и все, план другой - это отдельная большая тема. Теперь по поводу подзапросов. Сервер оптимизирует (т.е. решает как будет выполнять) запрос целиком, сервер гарантирует только логический порядок запроса, физически - он может делать что угодно. Может сначала выполнить подзапрос, поместить его во временную таблицу и юзать ее, может обойтись без этой таблицы, может подзапрос для каждой строки вызывать - для того чтобы понять что он делает и придумали планы. И если что-то идет не так как вы задумывали - это можно посмотреть в плане и постараться повлиятть на ситуацию. И относительно столбцов...сервер читает данные не столбцами, и даже не строками - он читает страницами по 8 кб. Он может читать страницы индекса или страницы таблицы (либо кластерного индекса) и абсолютно без разницы сколько вы там напишете полей, все равно, чтобы получить значение одного из них, сервер вынужден будет прочитать страницу в память целиком (и вот тут ваша экономия не сработает), и потом уже в памяти найти строку и поле, но на количество памяти это уже не повлияет, как вы догадываетесь.
Пишите, что хотите (по теме разумеется), я только высказал сомнения относительно таких способов оптимизации =)
Совершенно неверно. Уже в 2000-м, оптимизатор запросов работал и работает прилично. Конечно можно такого наваять что не оставить шансов оптимизатору. В таком случае ручная "оптимизация" конечно лучше =)
На вкус и цвет все фломастеры разные.
"Вот это прикол!" (с) "+100500". Ну вы даете! Да как же гадать, если в плане уже есть все, только нужно уметь читать то что сервер так любезно предоставил.
Читать, конечно, важно, но не всегда заканчивается этим. Ну нашли узкое место, а дальше что? Как дальше быть, переделывать запрос или допилить этот? Все в конце концов сведется к гаданию на кофейной гуще, просто гадать не на весь запрос, а на его узкие части. Я же не говорил, что оптимизация - случайный перебор всех вариантов. Сами же говорите, предсказать оптимизатор невозможно, и тут же говорите, что нет гаданий в "ручной оптимизации". Если уж мы имеем такой источник энтропии, то об оптимизации сложно говорить, как не о плясках с бубнами.
Я считаю что, если вы уж решили помочь с оптимизацией
1) нужно было запросить предварительную информацию о: версии сервера, индексах, объемах данных, конкретном времени запроса, желаемом времени, действительных планах
2) предупредить, что вклейка строк параметров в текст запроса может привести к атакам sqlInjection и вообще это друной тон.
3) посоветовать привести синтаксис в соответсвие со стандартом анси, а именно переписать все на join. (кстати вы не поверите, декартово произведение с условиями в кляузе where таки приведет к операции inner join, и никак не к Н^5 строк, вот если убрать условия - то да, декартово произведение)
4) привести выражение к, что называется, SARG-able. Чтобы оно могло использоваться в поиске по индексу и не приводило к просмотру всего индекса или таблицы. Для этого можно как раз использовать, например, триггер.
1) Первый же пост, когда я как бы легонько намекнул ТС о том, что СУБД бывают разные и оптимизаторы тоже у них отличаются.
2) А кто сказал, что переменная не проходит предварительную валидацию, на отсутствие спецсимволов?
3) Я советовал перейти на join, что ТС, кстати, и сделал. Через одно место, но сделал.
Я обычно (кроме самых очевидных ситуаций) не берусь предсказывать поведение оптимизатора. По этому рассуждать о том, что решил бы оптимизатор не могу, особенно не зная индексы и данные. Сегодня данные одни, завтра статистика изменилась, и все, план другой - это отдельная большая тема. Теперь по поводу подзапросов. Сервер оптимизирует (т.е. решает как будет выполнять) запрос целиком, сервер гарантирует только логический порядок запроса, физически - он может делать что угодно. Может сначала выполнить подзапрос, поместить его во временную таблицу и юзать ее, может обойтись без этой таблицы, может подзапрос для каждой строки вызывать - для того чтобы понять что он делает и придумали планы. И если что-то идет не так как вы задумывали - это можно посмотреть в плане и постараться повлиятть на ситуацию. И относительно столбцов...сервер читает данные не столбцами, и даже не строками - он читает страницами по 8 кб. Он может читать страницы индекса или страницы таблицы (либо кластерного индекса) и абсолютно без разницы сколько вы там напишете полей, все равно, чтобы получить значение одного из них, сервер вынужден будет прочитать страницу в память целиком (и вот тут ваша экономия не сработает), и потом уже в памяти найти строку и поле, но на количество памяти это уже не повлияет, как вы догадываетесь.
Да кто спорит с тем, что оптимизатор штука мутная? Но я согласен, что о гипотетических ситуациях можно говорить сколько угодно, автору это нисколько не поможет. Если нет данных по оптимизации, зачет переливать из пустого в порожнее. Когда скажет: вот физический план запроса, тут узкое место, можно как-то ускорить. И статистику по таблицам приведет, тогда можно говорить, а сейчас бессмыслено.
А если он вынужден прочитать 8 страниц с неиндексированными данными, вместо 4? Тут разве не сработает? Да там и не только в страницах оптимизация может проявиться, а в вычленении LIKE конструкции, которая испортит все старания оптимизатора, например. Разбитие на подзапросы (или использование join, по сути тот же подзапрос только спрятанный за красивым словом, хотя онигда подзапрос предпочтительнее), имхо, почти всегда помагает.
Да и вообще можно начать придираться к схеме данных, что она не находится в нормальной форме, и так далее. Можно из пальца высосать сколько угодно колких замечаний, главное не помагать, а критиковать уже написанные решения.
Пишите, что хотите (по теме разумеется), я только высказал сомнения относительно таких способов оптимизации =)
Я не претендую на лавры лучшего оптимизатора запросов SQL. Но если ТС спрашивает основы, то зачем ему сразу вываливать тонны информации, без которой он может обойтись пока? Когда придет время, сам откроет и прочитает.
[QUOTE=hivewarrior;]1) Первый же пост, когда я как бы легонько намекнул ТС о том, что СУБД бывают разные и оптимизаторы тоже у них отличаются.
2) А кто сказал, что переменная не проходит предварительную валидацию, на отсутствие спецсимволов?
3) Я советовал перейти на join, что ТС, кстати, и сделал. Через одно место, но сделал.[/QUOTE]
Ну в первом посте был помянут ms sql, так что просто версию уточнить, а не акцесс - совсем другая субд. Я впрочем буквоедством заниматься не хочу, суть-то не в этом. Насчет валидации - это по принципу "что не разрешено, то запрещено", а не "что не запрещено, то разрешено". Так что раз явно про контроль ничего не сказано - я бы упомянул. И лично от себя еще ТС советую, так вообще не делать, ибо моветон, а пользоваться для передачи специальными классами параметров благо это просто и не напряжно.
Джоин это вы правильно сказали, просто у ТС в исходном запросе ведь тоже джоин был, а никак не Н^5, только написано "в старом стиле", от которого мс советуют отказываться, а в новых версиях сиквела фишки типа *= для внешних соединения уже даже и не поддерживаются (если уровень совместимости не поставить).
[QUOTE=hivewarrior;]
Да кто спорит с тем, что оптимизатор штука мутная? Но я согласен, что о гипотетических ситуациях можно говорить сколько угодно, автору это нисколько не поможет. Если нет данных по оптимизации, зачет переливать из пустого в порожнее. Когда скажет: вот физический план запроса, тут узкое место, можно как-то ускорить. И статистику по таблицам приведет, тогда можно говорить, а сейчас бессмыслено.[/QUOTE] Оптимизатор я бы сказал не мутная, а слишком сложная =). Ну так вот и я про те же гипотетические ситуации, чего их оптимизировать-то, когда не известно откуда начал и к чему надо стремиться и вообще, а что я собственно оптимизирую.
[QUOTE=hivewarrior;]А если он вынужден прочитать 8 страниц с неиндексированными данными, вместо 4? Тут разве не сработает? Да там и не только в страницах оптимизация может проявиться, а в вычленении LIKE конструкции, которая испортит все старания оптимизатора, например. Разбитие на подзапросы (или использование join, по сути тот же подзапрос только спрятанный за красивым словом, хотя онигда подзапрос предпочтительнее), имхо, почти всегда помагает.[/QUOTE] 8 страниц данных, прочитаются медленее 4 страниц данных, потмоу что 8*8=64 кб, будут считываться какбэ дольше чем 8*4 = 32кб=). Причем не важно, индексированные данные у вас или нет, потому что коль скоро уж у вас сервер уже нашел те самы страницы, которые надо прочитать, то индексы ему уже какбэ и не нужны... Хотя в реальности чтение 8 и 4 страниц данных может иметь вовсе не линейную зависимость, ибо фрагментацию еще никто не отменял. Только как это связано с количеством столбцов в подзапросе и итоговом селекте? Если конечно у вас в итоговой выборке есть широкие столбцы, которые хранятся на отдельных страницах, либо вы исключите из нее столбцы, которые не входят в индекс - то запрос очень сильно ускорится, за счет меньшего кол-ва чтений - но у вас же в обоих запросах одинаковое число столбцов? join и подзапрос вообще понятия не кореллирующие...А like '%abc' по вашему не связан с количеством чтений?
Вы поймите, подзапросы очень полезная и нужная штука, иногда, без них никак. И для оптимизации их применять тоже можно, но не так как вы тут показали, хотя может быть даже эта "оптимизация" и помогла ТС (правда мое мнение, что разница в том, что в итоговом запросе ТС просто нет операции группировки, вот и быстрее), в таком случае поздравляю - вам надо играть в лотерею, у вас дар к угадыванию! =)
Вы лучше скажите мне, что быстрее?=)
,A.Name
,Count(*)
FROM dbo.Message M
JOIN dbo.Author A ON A.ID = M.Author
GROUP BY M.Author
,A.Name
,A.Name
,M.[Count]
FROM ( SELECT Author
,Count(*) AS [Count]
FROM dbo.Message
GROUP BY Author) M
JOIN dbo.Author A ON A.ID = M.Author
подразумевается, что у одного Author может быть много Message.
А как же распределённые БД? Там даже перестановка местами двух подзапросов помогает. Да и не всегда приведение типов помогает или любой другой прием. Поэтому и гадание, из-за того, что сложно предугадать, как надо оптимизировать. Алгоритм есть, но он не позволяет выйти к абсолютному максимуму, всегда приходится бултыхаться в локальных экстремумах. А это суть гадание на кофейной гуще, если нет четкого плана, дающий абсолютный максимум всегда.
Ну в первом посте был помянут ms sql, так что просто версию уточнить, а не акцесс - совсем другая субд. Я впрочем буквоедством заниматься не хочу, суть-то не в этом. Насчет валидации - это по принципу "что не разрешено, то запрещено", а не "что не запрещено, то разрешено". Так что раз явно про контроль ничего не сказано - я бы упомянул. И лично от себя еще ТС советую, так вообще не делать, ибо моветон, а пользоваться для передачи специальными классами параметров благо это просто и не напряжно.
Каюсь, не заметил в последнем предложении MS SQL. Мое упущение, что не внимательно читал пост. А валидация параметров запроса - другая тема, которая к топику имеет посредственное отношение.
Джоин это вы правильно сказали, просто у ТС в исходном запросе ведь тоже джоин был, а никак не Н^5, только написано "в старом стиле", от которого мс советуют отказываться, а в новых версиях сиквела фишки типа *= для внешних соединения уже даже и не поддерживаются (если уровень совместимости не поставить).
Это оптимизатор сведет запрос к джойнам, а работа этого оптимизатора - суть лишние такты процессора. И чем больше оптимизировать - тем больше тактов. Или я не прав? Все равно "в старом стиле" всегда медленнее.
8 страниц данных, прочитаются медленее 4 страниц данных, потмоу что 8*8=64 кб, будут считываться какбэ дольше чем 8*4 = 32кб=). Причем не важно, индексированные данные у вас или нет, потому что коль скоро уж у вас сервер уже нашел те самы страницы, которые надо прочитать, то индексы ему уже какбэ и не нужны... Хотя в реальности чтение 8 и 4 страниц данных может иметь вовсе не линейную зависимость, ибо фрагментацию еще никто не отменял. Только как это связано с количеством столбцов в подзапросе и итоговом селекте? Если конечно у вас в итоговой выборке есть широкие столбцы, которые хранятся на отдельных страницах, либо вы исключите из нее столбцы, которые не входят в индекс - то запрос очень сильно ускорится, за счет меньшего кол-ва чтений - но у вас же в обоих запросах одинаковое число столбцов? join и подзапрос вообще понятия не кореллирующие...А like '%abc' по вашему не связан с количеством чтений?
Чем отличаются эти две вещи по большому счету?
FROM (Table1 JOIN Table2 ON Table1.a=Table2.a)
JOIN Table3 ON Table1.b=Table3.b
SELECT *
FROM ( SELECT *
FROM Table1, Table2
WHERE Table1.a=Table2.a) AS tmp, Table3
WHERE tmp.b=Table3.b
И джойн никак не коррелирует с подзапросом? Даже логический план обоих запросов идентичный. Джойн просто красив, и я обеими руками только за использования джойна, так как он читается в сотни раз удобнее и так далее.
Вы поймите, подзапросы очень полезная и нужная штука, иногда, без них никак. И для оптимизации их применять тоже можно, но не так как вы тут показали, хотя может быть даже эта "оптимизация" и помогла ТС (правда мое мнение, что разница в том, что в итоговом запросе ТС просто нет операции группировки, вот и быстрее), в таком случае поздравляю - вам надо играть в лотерею, у вас дар к угадыванию! =)
У ТС вообще-то не работал LIKE, и я подсказывал скорее как починить его, чем про оптимизацию. Единственный совет по оптимизации был не лепить все таблицы в FROM и использовать JOIN. И дальше про оптимизацию когда начали говорить, сразу ссылки в гугл на логический и физический планы запросов. Никаких конкретных советов по оптимизации не давал, ибо их нет, а приводить список пожеланий при составлении запросов можно прочитать в любой новой книге по SQL. Все равно что-то упустил бы. Указал только на самый очевидный недочет.
Вы лучше скажите мне, что быстрее?=)
,A.Name
,Count(*)
FROM dbo.Message M
JOIN dbo.Author A ON A.ID = M.Author
GROUP BY M.Author
,A.Name
,A.Name
,M.[Count]
FROM ( SELECT Author
,Count(*) AS [Count]
FROM dbo.Message
GROUP BY Author) M
JOIN dbo.Author A ON A.ID = M.Author
подразумевается, что у одного Author может быть много Message.
Сакральный смысл добавления в GROUP BY A.Name мне не ясен, например. И "оптимизация подзапросом" сомнительна. Зачем было Count вносить в подзапрос? Только чтобы он хоть как-то оправдывал свое существование?
Первый в большинстве случаев будет быстрее, но соревноваться здесь будут скорее GROUP BY из первого и Подзапрос из второго в понижении производительности. Из-за того, что подзапрос не определен нельзя будет быстро выполнить соединение таблиц.
Если уж совсем буквоедствовать, то все зависит от количества Message, которые у одного Author. И даже физическое расположение таблиц тоже играет роль, если они на разных серверах или физических дисках. Можно даже извратиться и придумать условия, когда второе обгонит по производительности первое. Но оно нам надо?
[QUOTE=hivewarrior]Поэтому и гадание, из-за того, что сложно предугадать, как надо оптимизировать. Алгоритм есть, но он не позволяет выйти к абсолютному максимуму, всегда приходится бултыхаться в локальных экстремумах. А это суть гадание на кофейной гуще, если нет четкого плана, дающий абсолютный максимум всегда.[/QUOTE] Вот вы зачем-то отрицаете очевидные вещи. Я вам говорю, нет никакого гадания. В документации даже есть раздел Контрольный список для анализа медленно выполняемых запросов. Там по полочком разобраны все основные причины. Так же в книгах описаны техники написания "правильных" запросов с объяснениями почему так, а не иначе. Если вам это не часто требуется, а разбираться ради пары случаев в год - лень (как мне, например с яваскриптом, гы-гы), то это понятно, но это не повод говорить что оптимизация это гадание =). Что такое "абсолютный максимум всегда" - тоже непонятно, купите железку помощнее, будет еще "максимумее", еще мощнее - еще лучше и т.д. - так что речь не о максимуме, а об оптимальности. А достичь максимума, в том плане, что запрос начнет выполняться настолько хорошо, что дальнейшей возможной оптимизацией будет только апгрейд железа - очень даже можно, я бы даже сказал это должно быть одной из главных целей при написании sql кода.
[QUOTE=hivewarrior]Это оптимизатор сведет запрос к джойнам, а работа этого оптимизатора - суть лишние такты процессора. И чем больше оптимизировать - тем больше тактов. Или я не прав? Все равно "в старом стиле" всегда медленнее.[/QUOTE] Ммм...меня осенила догадка, т.е. вы думаете, что оптимизатор выборочно чтоль запросы оптимизирует? Т.е. типа "хорошие" он пропускает и так, без лишних тактов процессора, а "плохие" ему приходится еще и оптимизировать?? Наверное сбивает с толку слово "оптимизатор", на самом деле лучшая аналогия это "компилятор". Т.е. он текст вашего запроса, компилирует в понятные реляционному механизму инструкции. Только если в компиляторе не широкий набор вариантов оптимизации (хотя и то они есть), то у оптимизатора вариантов много и он выбирает то, как именно должны быть просмотрены таблицы и получены данные. По-этому абсолютно любой запрос, если не имеет уже скомпилированного плана, проходит этап оптимизации. Так что такты процессора тратятся всегда и в любом случае. Само собой в зависимости от сложности запроса различается и время компиляции. Но только здесь не такой же случай, если за именами таблиц не скрыты вьюшки со сложными запросами, то скорее всего оба запроса имеют тривиальное время компиляции, т.к. никаких хитровыдуманных конструкций там нет. А за "старый синтаксис" и вовсе отвечает не оптимизатор, а синтаксический анализатор.
Вот опять же, если вам интересно гляньте подробнее Обработка инструкций SQL.
[QUOTE=hivewarrior]И джойн никак не коррелирует с подзапросом? Даже логический план обоих запросов идентичный. Джойн просто красив, и я обеими руками только за использования джойна, так как он читается в сотни раз удобнее и так далее.[/QUOTE]Я имел ввиду, что join - это название логического оператора. А "подзапрос" - это термин обозначающий запрос в запросе, т.е. суть - разные термины и сравнивать что лучше и что хуже, это как сравнивать теплое с мягким.
Кстати, что вы называете "логическим" и "физическим" планами? Я таких не знаю, может в каких-то субд есть такое, а в ms sql есть реальный (actual) и оценочный (estimated), давайте уж говорить в терминах той субд о которой ведем дикусссию.
[QUOTE=hivewarrior]Сакральный смысл добавления в GROUP BY A.Name мне не ясен, например. И "оптимизация подзапросом" сомнительна. Зачем было Count вносить в подзапрос? Только чтобы он хоть как-то оправдывал свое существование?[/QUOTE]Ну смысл, например, посчитать, сколько у каждого автора сообщений. Пример есессно академический, можете вместо a.Name подставить какой-нить a.Nickname, т.е. две таблицы сообщений и их авторов связаны по имени автора, требуется вывести список авторов и кол-во сообщений каждого. Ну на самом деле не суть важно...
[QUOTE=hivewarrior] Первый в большинстве случаев будет быстрее, но соревноваться здесь будут скорее GROUP BY из первого и Подзапрос из второго в понижении производительности. Из-за того, что подзапрос не определен нельзя будет быстро выполнить соединение таблиц. [/QUOTE] Не угадали =)
Вот вы зачем-то отрицаете очевидные вещи. Я вам говорю, нет никакого гадания. В документации даже есть раздел Контрольный список для анализа медленно выполняемых запросов. Там по полочком разобраны все основные причины. Так же в книгах описаны техники написания "правильных" запросов с объяснениями почему так, а не иначе. Если вам это не часто требуется, а разбираться ради пары случаев в год - лень (как мне, например с яваскриптом, гы-гы), то это понятно, но это не повод говорить что оптимизация это гадание =). Что такое "абсолютный максимум всегда" - тоже непонятно, купите железку помощнее, будет еще "максимумее", еще мощнее - еще лучше и т.д. - так что речь не о максимуме, а об оптимальности. А достичь максимума, в том плане, что запрос начнет выполняться настолько хорошо, что дальнейшей возможной оптимизацией будет только апгрейд железа - очень даже можно, я бы даже сказал это должно быть одной из главных целей при написании sql кода.
Как бы максимум и минимум в контексте экстремумов вполне конкретные понятия. По полученным планам мы все равно будем начинать плясать в сторону локального экстремума от первоначального запроса. И спустимся мы в какой-нибудь локальный максимум, в то время как абсолютный максимум где-то рядом.
А пока нет четкого алгоритма (а его нет, есть только рекомендации, которые могут помочь ускорить запросы) это суть гадание на кофейной гуще. Пусть и более менее осмысленное, но гадание. Или это тоже будете отрицать? Тогда в студию полный алгоритм оптимизации любого запроса при любых условиях, и я сразу перестану воспринимать оптимизацию, как что-то близкое к эзотерике.
Ммм...меня осенила догадка, т.е. вы думаете, что оптимизатор выборочно чтоль запросы оптимизирует? Т.е. типа "хорошие" он пропускает и так, без лишних тактов процессора, а "плохие" ему приходится еще и оптимизировать?? Наверное сбивает с толку слово "оптимизатор", на самом деле лучшая аналогия это "компилятор". Т.е. он текст вашего запроса, компилирует в понятные реляционному механизму инструкции. Только если в компиляторе не широкий набор вариантов оптимизации (хотя и то они есть), то у оптимизатора вариантов много и он выбирает то, как именно должны быть просмотрены таблицы и получены данные. По-этому абсолютно любой запрос, если не имеет уже скомпилированного плана, проходит этап оптимизации. Так что такты процессора тратятся всегда и в любом случае. Само собой в зависимости от сложности запроса различается и время компиляции. Но только здесь не такой же случай, если за именами таблиц не скрыты вьюшки со сложными запросами, то скорее всего оба запроса имеют тривиальное время компиляции, т.к. никаких хитровыдуманных конструкций там нет. А за "старый синтаксис" и вовсе отвечает не оптимизатор, а синтаксический анализатор.
Вот опять же, если вам интересно гляньте подробнее Обработка инструкций SQL.
Да для оптимизатора даже количество букав в запросе важно, язык SQL все же текстовый. Дальше будет синтаксический анализатор строить дерево запроса. Если дерево хреновое, то и время на его оптимизацию будет потрачено уйма, так как не всегда оптимизатор может догадаться до каких-либо решений. Это программа, она лишена творческой жилки.
Думаете, почему еще живы ассемблерные вставки? Компиляторы C и C++ давно научились генерировать код сравнимый с человеческим, но только в общих объемах. Всегда в любом узком месте можно написать более оптимальный код, например, умножение на константу какую-либо. Так и тут, оптимизатор не всесилен и иногда "плохие" запросы ему даются с трудом, так как дерево запроса слишком запутано и не поддается нормальной оптимизации.
Я имел ввиду, что join - это название логического оператора. А "подзапрос" - это термин обозначающий запрос в запросе, т.е. суть - разные термины и сравнивать что лучше и что хуже, это как сравнивать теплое с мягким.
Кстати, что вы называете "логическим" и "физическим" планами? Я таких не знаю, может в каких-то субд есть такое, а в ms sql есть реальный (actual) и оценочный (estimated), давайте уж говорить в терминах той субд о которой ведем дикусссию.
Ну блин. Логический план - это логический план запроса) Я даже не знаю как это объяснить (а если говорить честно, то уже забыл давно). Это как бы архитектурно независимый (концептуальный) план запроса, который возвращает нужный результат. Логический план - суть дерево запроса, именно над ним и извращается оптимизатор. А потом еще логический переводится в физический, и по пути тоже над запросом извращается оптимизатор.
А физический план это уже с учетом всей архитектуры. Он уже может быть "реальный (actual) и оценочный (estimated)". Все зависит от того, будет ли этот запрос исполнен или эмулирован/симулирован.
Ну смысл, например, посчитать, сколько у каждого автора сообщений. Пример есессно академический, можете вместо a.Name подставить какой-нить a.Nickname, т.е. две таблицы сообщений и их авторов связаны по имени автора, требуется вывести список авторов и кол-во сообщений каждого. Ну на самом деле не суть важно...
Нет, именно GROUP BY A.Name. Смысл запроса понятен, просто группировка по A.Name не должна ничего давать. У каждой записи в A есть Name и ID, по которому мы и клеили. Зачем второй раз учитывать Name, если уже сгруппировано все по ID?
Не угадали =)
Объясните, почему так. Интересно же, где я маху дал(
Неужели GROUP BY в подзапросе так ускорил выполнение?
А пока нет четкого алгоритма (а его нет, есть только рекомендации, которые могут помочь ускорить запросы) это суть гадание на кофейной гуще. Пусть и более менее осмысленное, но гадание. Или это тоже будете отрицать? Тогда в студию полный алгоритм оптимизации любого запроса при любых условиях, и я сразу перестану воспринимать оптимизацию, как что-то близкое к эзотерике.[/QUOTE] Какие локальные максимумы и минимумы? Какие экстремумы? (вас матан чтоль еще не отпустил? =)) Хорошо. Я объясню конкретнее.
Ситуация. Есть два запроса, работают на одних и тех же данных, с одними и теми же таблицами, в одной базе данных, выдают одинаковый результат. Но! Один выполняется условно 5 минут, другой 1 секунду. Так вот набор шагов, которые необходимо предпринять, для того, чтобы план первого запроса изменился таким образом, чтобы время его выполнения сократилось с 5 минут до 1 секунды - я и называю оптимизацией (в контексте этого обсуждения, а не межгалактического масштаба конечно). И вот чтобы это осуществить - не нужно гадать, не нужно никаких абстракций и экстремумов - нужны конкретные исследования, анализ и действия. К вам вот подойдет клиент, положит так руку на плечо и скажет "Слушай, че-то у меня поиск долго работает, целую минуту жду!". Вы ему про экстремумы будете рассказывать?
Хмм...т.е. по вашей логике получается, что если я сокращу в запросе количество букв (например, укорочу названия таблиц), то типа как быстрее будет???8-O Черт, надо срочно переименовать все таблицы в A, B, C,...\palmface
Дерево это просто то во что был разобран запрос, какой запрос, аткое и дерево. Но, тем не менее, наконец-то светлая мысль! Я ее, кстати, уже озвучил во втором ответе вам: "Конечно можно такого наваять что не оставить шансов оптимизатору. В таком случае ручная "оптимизация" конечно лучше =)" btw, вы знаете, что уйма времени-таки не будет потрачена на оптимизацию, т.к. оптимизатор ограничен по времени при построении плана, этим и объясняется то, что иногда за отведенное время хороший план просто не успевает быть найден.
А физический план это уже с учетом всей архитектуры. Он уже может быть "реальный (actual) и оценочный (estimated)". Все зависит от того, будет ли этот запрос исполнен или эмулирован/симулирован.
От фантазер! Ну фантазер! Ну ладно =)
Я-то думал что дерево запроса, это дерево запроса, т.е. просто этакое expression tree, то, что было разобрано и чего там собссно планировать? Как кстати его посмотреть, этот план? Ну ладно оставим его в покое.
Теперь, стало быть, физический! Смотрим в справку Оператор Lazy Spool инструкции Showplan. Даже имеет свой значок в плане выполнения! Но минутку, что там написано: "Lazy Spool — это логический оператор." (а перед этим объясняется какие реальные физические действия скрываются за этим оператором). Смотрим еще: Оператор Union инструкции Showplan - "Union — это логический оператор.", Оператор Sort инструкции Showplan - "Sort — это логический и физический оператор." (ай, каррамба!!) и т.д. Смешались в кучу кони люди, в физическом плане - логические операторы. А все не потому что мир или майкрософт сошел с ума. А потому, что не надо придумывать собственные термины, да еще и награждать их таким смыслом, которого нет и никогда не было в оригинале. Я не спорю что наверняка в какой-нить умной книге по теории реляционных БД или в документации к другим субд есть и логический и физический и метафизический планы, и оптимизаторы извращенцы и прочая вурдалаки - но только не в контексте этой субд. Билив ми, а если не верите - марш читать документацию, а то я вам замучился ее пересказывать.
Кстати! А что такое "эмулирован/симулирован"? Я знаю сервер может компилировать запрос (тут как раз появляется оценочный план), знаю может просто синтаксис распарсить, знаю может вернуть только метаданные, знаю может выполнить (тут как раз фактический реальный план), но симулировать.... Может это типа как симулировать оргазм? Сервер че-то думал-думал, делал вид, гад что выполняет запрос, а сам - симулировал, сволочь этакая!
А вы попробуйте исключить из группировки поле Name, но оставить его в выборке, что будет? Ошипко компиляции будет. Так что смиримся с этим.
Неужели GROUP BY в подзапросе так ускорил выполнение?
Если честно и беззлобно, не стремлюсь никого обидеть, но маху вы дали, когда начали подробно рассказывать мне про ваше видение оптимизации и кофейную гущу =) В остальном - это ваше мнение, тем более, вы таки совместными с тс услилиями решили его проблему, бинго!
А касательно этого запроса, почему вы теперь решили что второй вариант лучше?
Подвох тут в том, что несмотря на то, что запросы по логике своей разные. В первом случае, мы когда пишем, вроде как думаем, что сначала будут соединены две таблицы, потом над этим множеством будет выполнена операция группировки. Во втором, что сначала мы формируем уже сгруппированное множество (тем самым сокращая количество строк), а потом соединяем и по-идее, соединение должно пройти быстрее из-за меньшего числа строк. На деле, оптимизатор обработает оба этих варианта по второму сценарию. Он и сам не дурней велосипеда, соображает как лучше. Если хватит терпения, можете скачать sql sever express, создать тестовые таблички, забить туда несколько строк данных и убедиться лично в правдивости моих слов.
Я привел этот пример, потому что хотел вложить в вас мысль, что не всегда все так очевидно. И всегда (!!) нужно прежде всего смотреть план, а то может вы будете оптимизировать то, что в оптимизации не нуждается. И это еще простой случай, представляете как возрастает число вариантов, с возрастанием числа таблиц? так что не надо "гадать" какой вариант выбрал сервер, надо это просто смотреть в плане и если ваше мнение не сходится с мнением сервера - то влиять на ситуацию при помощи тех самых рекомендаций/методв/техник. Все.
Если уж это вас не убедит - то уж я уж прям и не знаю.
И еще, личная просьба на будущее, вы либо изучайте мат.часть, чтоб не плавать в терминах, либо..ну, осторожней чтоль высказывайтесь в темах в которые, как бы это сказать, ну...не ваша сильная сторона (экий я, сегодня дипломат! =))
Ситуация. Есть два запроса, работают на одних и тех же данных, с одними и теми же таблицами, в одной базе данных, выдают одинаковый результат. Но! Один выполняется условно 5 минут, другой 1 секунду. Так вот набор шагов, которые необходимо предпринять, для того, чтобы план первого запроса изменился таким образом, чтобы время его выполнения сократилось с 5 минут до 1 секунды - я и называю оптимизацией (в контексте этого обсуждения, а не межгалактического масштаба конечно). И вот чтобы это осуществить - не нужно гадать, не нужно никаких абстракций и экстремумов - нужны конкретные исследования, анализ и действия. К вам вот подойдет клиент, положит так руку на плечо и скажет "Слушай, че-то у меня поиск долго работает, целую минуту жду!". Вы ему про экстремумы будете рассказывать?
То есть для вас оптимизация - нахождение локального (локальный, конечно, может оказаться глобальным, но не суть) минимума времени выполнения запроса. А для кого-то - нахождение глобального минимума. Понятное дело, что по вашим критериям, когда в локальный минимум загоняется что угодно по факу - оптимизация по факу не гадание на кофейной гуще. Если кому-то нужно глобальный минимум, а фак позволяет только привести изначальный запрос к локальному минимуму, то это гадание на кофейной гуще. Вопрос мировоззрения. Не надо все сводить к идиотическим ситуациям, про заказчиков и так далее. Я уже заметил, что гипотетические ситуации вы строить мастак, но давайте придерживаться практики. Пока нет четкого алгоритма, который дает на выходе запрос с глобальным минимумом выполнения - все это гадание на кофейной гуще.
С заказчиками же, сами знаете, никогда все на 100% не выкладывается (отдельные вещи да, но далеко не все), и не надо путать теплое с мягким. Если запрос медленный, то кровь из носа, но исправить или просить меньше денег из-за недоделок. У заказчика почти всегда есть цифры в ТЗ(а если нет, то их надо туда внести или его можно посылать подальше после, жестоко, но необходимо), так что бултыхание в локальных минимумах после шаманства над изначальным запросом вполне подходит в этой ситуации. Или я не прав?
Хмм...т.е. по вашей логике получается, что если я сокращу в запросе количество букв (например, укорочу названия таблиц), то типа как быстрее будет???8-O Черт, надо срочно переименовать все таблицы в A, B, C,...\palmface
Не надо начинать. Не стоит путать теплое с мягким. На входе синтаксического анализатора строка и чем она короче, тем быстрее работа. Потом от строки уйдет СУБД, но в начале она есть. Или строка сразу превращается в дерево запроса? Почитайте про сам язык SQL, если для вас это новость.
Дерево это просто то во что был разобран запрос, какой запрос, аткое и дерево. Но, тем не менее, наконец-то светлая мысль! Я ее, кстати, уже озвучил во втором ответе вам: "Конечно можно такого наваять что не оставить шансов оптимизатору. В таком случае ручная "оптимизация" конечно лучше =)" btw, вы знаете, что уйма времени-таки не будет потрачена на оптимизацию, т.к. оптимизатор ограничен по времени при построении плана, этим и объясняется то, что иногда за отведенное время хороший план просто не успевает быть найден.
Уйма не будет, а значит запрос не оптимизирован, и будет уже потрачено уйма времени на его выполнение. И еще, не надо придираться к словам. Вы же прекрасно поняли, что при хреновом дереве на оптимизацию будет потрачено уйма времени, но не обязательно его оптимизировать до конца. Или мне ставить оговорки на каждое предложение с какими начальными условиями оно будет верно? И потом на уточнение тоже уточнения накладывать? Хватит уже буквоедничать, здесь не глупые люди собрались.
От фантазер! Ну фантазер! Ну ладно =)
Я-то думал что дерево запроса, это дерево запроса, т.е. просто этакое expression tree, то, что было разобрано и чего там собссно планировать? Как кстати его посмотреть, этот план? Ну ладно оставим его в покое.
Теперь, стало быть, физический! Смотрим в справку Оператор Lazy Spool инструкции Showplan. Даже имеет свой значок в плане выполнения! Но минутку, что там написано: "Lazy Spool — это логический оператор." (а перед этим объясняется какие реальные физические действия скрываются за этим оператором). Смотрим еще: Оператор Union инструкции Showplan - "Union — это логический оператор.", Оператор Sort инструкции Showplan - "Sort — это логический и физический оператор." (ай, каррамба!!) и т.д. Смешались в кучу кони люди, в физическом плане - логические операторы. А все не потому что мир или майкрософт сошел с ума. А потому, что не надо придумывать собственные термины, да еще и награждать их таким смыслом, которого нет и никогда не было в оригинале. Я не спорю что наверняка в какой-нить умной книге по теории реляционных БД или в документации к другим субд есть и логический и физический и метафизический планы, и оптимизаторы извращенцы и прочая вурдалаки - но только не в контексте этой субд. Билив ми, а если не верите - марш читать документацию, а то я вам замучился ее пересказывать.
Раньше логических планов нет в MS SQL, а сейчас вылезли логические операторы. Как же они соотносятся, логический план и логический оператор? Значит, все же знаете, что это такое? Хватит уже юлить и факты подтасовывать. Или в MS SQL нет логического плана и логический оператор значит что-то другое. Либо и вы прекрасно знаете про логический план, но зачем-то пишете что-то несвязное и так далее.
Скажу сразу, с MS SQL не сильно знаком, больше просто подкован в теории запросов SQL, реляционной алгебре и прочих веселых штучках. И я честно сказал, что уже не помню точное значение терминов из книг. Но если в документации MS этого нет, то это не повод тут орать, что все дураки, один Стив Баллмер молодец. Может, время расширить кругозор?
Кстати! А что такое "эмулирован/симулирован"? Я знаю сервер может компилировать запрос (тут как раз появляется оценочный план), знаю может просто синтаксис распарсить, знаю может вернуть только метаданные, знаю может выполнить (тут как раз фактический реальный план), но симулировать.... Может это типа как симулировать оргазм? Сервер че-то думал-думал, делал вид, гад что выполняет запрос, а сам - симулировал, сволочь этакая!
Если честно и беззлобно, не стремлюсь никого обидеть, но маху вы дали, когда начали подробно рассказывать мне про ваше видение оптимизации и кофейную гущу =) В остальном - это ваше мнение, тем более, вы таки совместными с тс услилиями решили его проблему, бинго!
...
Извините, что вырезаю, но пост не влазит :D
...
И еще, личная просьба на будущее, вы либо изучайте мат.часть, чтоб не плавать в терминах, либо..ну, осторожней чтоль высказывайтесь в темах в которые, как бы это сказать, ну...не ваша сильная сторона (экий я, сегодня дипломат! =))
Так я и не понял, что вы хотели сказать. То есть таки GROUP BY ускорил на столько, что обогнали первый план? Или еще не так ускорил? Но зачем говорить, что таки ускорил и первый запрос будет исполняться по второму плану? И неужели, нельзя придумать ситуации, что этот запрос нельзя заставить выполняться оп первому плану? И снова я вижу ваше "по-идее по второму". Так по идее или точно? Гадаете на кофейной гуще, как и я... О каком разговоре может быть речь вообще с таким подходом? Скорее всего по второму плану, но я это не гадал! Ну что за лицемерие? И главное в конце не сказать, как все-таки это было выполнено, просто, мол думайте что хотите.
Или смотреть msdn? Но что-то не видно ни одной внятной статьи именно от Стива Баллмера. Только одна масенькая статеечка, больше ничего. Ну и хинты еще, но это не совсем то, о чем мы сейчас говорим.
Искать не только статьи от Стива Баллмера? Там тоже не так уж и много оптимизации именно самого T-SQL. В основном общие пожелания, типа используйте join и все такое, но это же не относительно одного T-SQL. Или читать не про T-SQL? Но тогда какого черта мы говорим? При чем тут оптимизатор и все такое, в какую сторону вы начали гнуть. Оптимизаторы у каждой СУБД очень разные - это ноухау каждой СУБД, и оптимизация под одну - не всегда оптимизация под другую. И зачем рассказывать новичку об именно оптимизаторах? Пусть общими приемами овладеет сначала.
Алсо, почти все общие приемы сравнивают JOIN и SUBQUERY? К чему бы это? Я согласен, что у каждого есть свои преимущества, и их надо изучать. Но орать громче всех, что это кардинально разные вещи и нельзя их сравнивать... Задавать вопросы о том, как эти два понятия коррелируют... Извините, или я конченный дурень, или вы что-то не договариваете.
[color=#CFCFCF]А я сегодня что-то хреновый дипломат (=[/COLOR]
Что такое "когда в локальный минимум загоняется что угодно" - что такое "загнать в минимум"??? Вы можете терминами программиста, а не хироманта изъясняться?
Что такое "глобальный минимум"? Ей богу, читаю, ну мутная вода какая-то! Как будто павел глоба прогноз рассказывает, т.е. под эту фразу можно абсолютно любой контекст подставлять! браво!
"Не надо все сводить к идиотическим ситуациям, про заказчиков и так далее" - Вы вообще как давно разработкой занимаетесь? Такое впечатление, что без году неделя, иначе вы бы знали что такие ситуации сплошь и рядом, и заказчик кстати не обязательно внешний. Так что, да-да, могут и подойти, и спросить в иных случаях.
Вообще все ваши эскапады с минимумами показывают, что вы, не просто не понимаете как оптимизировать, но непонимаете само понятие "оптимизация". Хотя начали вы со вполне конкретных вещей типа "гаданию на кофейной гущи по планам запроса, которые дергаются из СУБД", а потом, пытаясь вывернуться, скатились в какое-то унылое говно про минимумы. Стыдно товарищ! Ни одной конкретной мысли, ни примера, сплошное словоблудие.
"На входе синтаксического анализатора строка и чем она короче, тем быстрее работа." Да, конечно, я же и предлагал, таблицы покороче называть! Я рад что вы последовательны. маленький нюанс. Пусть бухгалтер Клавдия Петровна внесла новую проводку и сколько ресурсов займет прочитать из таблицы на одну проводку больше или один раз при генерации плана на несколько байт длинее запрос??? Вы серьезно думаете что "select * from MyTable" будет медленнее чем "select * from a"??? О чем тут говорить-то тогда...
"Раньше логических планов нет в MS SQL, а сейчас вылезли логические операторы." - Но планов то нет (!!!). Про операторы отдельный разговор, операторы есть и были всегда и физические и логические. На плане могут быть и те и те.
"Как же они соотносятся, логический план и логический оператор?" Да никак не соотносятся! Нет такого понятия "логический план" в этой субд. Ну нету! Ни в документации, ни в книгах, ни в статьях, ни в дискуссионных группах. И я вам приводил все аргументированно, со ссылками. А вы как раз несете что-то бессвязное. Есть просто план запроса - все! А если и слов-то таких не знаете - то как вообще вы про оптимизацию-то начали говорить, да еще советы давать?
Вы вот цитату английскую привели, а смысл-то поняли ее? Так я вам помогу "When estimated execution plans are generated, the Transact-SQL queries or batches do not execute." = Когда генерируется оценочный план, инструкции и запросы Transact-SQL не выполняются. Ну так правильно! Ведь как они могут выполняться, когда еще и плана-то нету! Я же писал про это (вы все-таки не читаете)! Сначала - компиляция/оптимизация, потом выполнение. Никаких эмуляций-симуляций, что за термины опять?
"Так я и не понял, что вы хотели сказать." - а вы и не поймете. Книжки идите читайте. Потом уже возвращайтесь. А то, извините, не владеющему основными понятиями трудно объяснить тему - из них состоящую, как ребенку невозможно объяснить что такое таблица умножения, когда он еще цифры не выучил.
На всякий случай вот Конкурс по оптимизации - как вы думаете, там тоже минимумы надо искать?? или реальной оптимизацией заниматься?
Все, я думаю я достаточно даже для таких "сказочных" как вы сказал.
Если будет еще хоть одно слово про "минимумы" или ваши абсурдные фантазии, высказанные в форме убеждений (которые могут приводить читателей к ложному пониманию), вместо полезной дискуссии о реальных вещах, из которой могут вынести что-то другие посетители форума (а не обороты полезные, разве что, гадалке на казанском, как сейчас) - ей богу, применю модераторские санкции.
Если желаете дальше продолжать дискуссию, каждую свою фразу - подкрепляйте соответсвующей ссылкой на документацию.
п.с.
Я думаю, понимающий народ, дико угорает над вашими сентенциями о работе сервера.
Ссылочку на темку тоже себе сохраняю, коллегам буду пересылать, вместо анекдотов =)