Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 142
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 142 (01 июня 2007 г.)
Новости сайта§ На этой неделе сайт временами был недоступен из-за проблем на хосте. Поскольку непрерывного простоя не было, то я не выполнял общую компенсацию времени. Поэтому пишите, если вы чувствуете себя пострадавшим. § Продвинулись в рейтинге: § На этой неделе сертифицированы: § Число подписчиков - 3548 Число участников рейтинга - 10566 Число участников второго этапа - 981 Сертифицировано на сайте - 159 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLДесять характерных ошибок в проектировании базы данных (окончание, начало в вып.138)Louis Davidson (оригинал: Ten Common Database Design Mistakes )Перевод Моисеенко С.И. Не использование хранимых процедур для обеспечения доступа к даннымХранимые процедуры - ваш друг. Используйте их всякий раз как метод для изоляции слоя базы данных от пользователей данных. Но не потребует ли это немного больше усилий? Первоначально конечно, но какая хорошая вещь не потребует немного больше времени? Хранимые процедуры делают разработку базы данных намного более прозрачной, и поощряют совместную разработку программистами, занимающимися вашей базой данных и функциональными возможностями. Вот несколько других интересных причин, которые демонстрируют важность хранимых процедур. ПоддержкаХранимые процедуры обеспечивают известный интерфейс к данным, и, на мой взгляд, это главное преимущество. Когда код, который осуществляет доступ к базе данных, собран в другом слое, недостаток производительности не может быть устранен без привлечения прикладного программиста. Хранимые процедуры дают профессионалу в базах данных власть изменять характеристики кода базы данных без привлечения дополнительных ресурсов, что делает выполнение незначительных изменений или же большой модернизации (например, изменение синтаксиса SQL) более легким. ИнкапсуляцияХранимые процедуры позволяют Вам так "инкапсулировать" любые структурные изменения, которые потребуется сделать в базе данных, чтобы влияние на пользовательские интерфейсы было минимально. Например, Вы первоначально моделировали один телефонный номер, но теперь потребовалось неограниченное число телефонных номеров. Вы могли бы оставить единственный телефонный номер в вызове процедуры, но хранить его в другой таблице в качестве временной меры, или даже постоянно, если Вы имеете "первичный" номер некоторого вида, который Вы всегда хотите показывать. Теперь могла бы быть построена хранимая процедура для обработки других телефонных номеров. При этом воздействие на пользовательские интерфейсы могло бы быть весьма незначительным, в то время как код хранимых процедур может существенно измениться. БезопасностьХранимые процедуры могут обеспечить специфичный и гранулированный доступ к системе. Например, Вы можете иметь 10 хранимых процедур, которые определенным образом обновляют таблицу X. Если пользователю необходимо обновить конкретный столбец в таблице, и Вам нужно гарантировать, что он никогда не обновит никакие другие столбцы, то Вы можете просто предоставить этому пользователю разрешение на выполнение только этой одной процедуры из десяти, которая и ему позволит выполнить необходимое обновление. ПроизводительностьЯ полагаю, что есть пара причин, согласно которым хранимые процедуры увеличивают производительность. Во-первых, если новичок пишет неоптимальный код (подобно использованию курсора для построчной обработки таблицы, содержащей десять миллионов строк, чтобы найти одно значение вместо использования предложения WHERE), процедура может быть переписана без воздействия на систему (кроме возвращения системе ценных ресурсов.) Вторая причина - повторное использование плана исполнения запроса. Если Вы не используете динамические запросы SQL в вашей процедуре, SQL Server может сохранить план, а не компилировать его при каждом выполнении. Верно, что в каждой версии SQL Server, начиная с 7.0, эта причина становится все менее значимой, поскольку SQL Server все лучше справляется с сохранением планов непосредственно передаваемых SQL запросов (см. примечание ниже). Однако хранимые процедуры все еще облегчают повторное использования плана и дают прирост производительности. В случае, когда необходимо ускорить непосредственно передаваемый код SQL, он может быть закодирован в хранимой процедуре без искажения. В 2005, есть установка базы данных "вынужденная параметризация" (PARAMETERIZATION FORCED), которая при включении заставит сохранять планы всех запросов. Это не покрывает более сложные ситуации, которые доступны процедурам, но может оказать большую помощь. Имеется также возможность, известная как plan guides, которая позволяет Вам отвергнуть план для известного типа запросов. Обе эти возможности должны помочь, когда хранимые процедуры не используются, хотя хранимые процедуры делают эту работу без всяких трюков. Этот список можно продолжить. Имеются также и недостатки, поскольку нет ничего совершенного. Для кодировки хранимых процедур может потребоваться больше времени, чем при использовании непосредственно передаваемых запросов. Однако количество времени, необходимое на проектирование интерфейса и его реализацию, стоит того, когда все сказано и сделано. Попытка кодировать общие объекты T-SQLЯ уже касался этого предмета ранее при обсуждении общих доменных таблиц, но данная проблема имеет более общий смысл. Каждый новый программист T-SQL, когда впервые начинает кодировать хранимые процедуры, думает: "очень жаль, что я не могу передать название таблицы как параметр в процедуру." Это действительно кажется весьма привлекательным: одна общая хранимая процедура, которая может выполнить свои операции на любой таблице, которую Вы выберете. Однако этого следует избегать, поскольку это может весьма отрицательно сказаться на производительности и фактически сделает жизнь более трудной, в конечном счете. Объекты T-SQL не делают "общее" легко, в значительной степени потому, что основной упор при проектировании в SQL Server сделан на облегчении повторного использования плана, а не кода. SQL Server работает лучше всего, когда Вы минимизируете неизвестности, чтобы он смог произвести наилучший возможный план. Чем больше делается для обобщения плана, тем меньше это позволяет оптимизировать его. Отметьте, что я специально не говорю о процедурах динамического SQL. Динамический SQL - великий инструмент, когда Вы имеете процедуры, которые нельзя другими способами сделать оптимизируемыми/управляемыми. Хороший пример - процедура поиска с множеством различных вариантов выбора. Предварительно откомпилированное решение с многочисленными условиями OR, возможно, даст худший план и неважные результаты, особенно если использование параметров является спорадическим. Однако главный пункт этого совета состоит в том, чтобы избегать кодирования очень общих объектов, таких, которые берут название таблицы и двадцать пар имя/значение в качестве параметра и позволяют Вам обновлять значения в таблице. Например, Вы могли бы написать процедуру, которая начинается так: CREATE PROCEDURE updateAnyTable Идея состоит в динамическом задании имени столбца и значения для передачи их в оператор SQL. Это решение ничем не лучше, чем простое непосредственное выполнение оператораUPDATE. Помимо этого, строя хранимые процедуры, Вы должны строить их ориентированными на каждую задачу, выполняемую для таблицы (или множестве таблиц.) Это дает Вам несколько преимуществ: · Правильно откомпилированные хранимые процедуры могут иметь единственный привязанный к ней компилированный план, многократно используемый в дальнейшем. Хороший метод - создать инструментарий для генерации кода на своем любимом языке программирования (даже на T-SQL), использующий метаданные SQL, для построения каждой конкретной хранимой процедуры для каждой таблицы вашей системы. Генерируйте все скучные, очевидные объекты, включая весь утомительный код для выполнения обработки ошибок, который является весьма существенным, чтобы не мучить себя многократным его написанием. В моей книге Apress, Pro SQL Server 2005 Database Design and Optimization, я предлагаю несколько таких "шаблонов" (Главным образом, для триггеров, но и для хранимых процедур тоже), все из которых имеют встроенную обработку ошибок. Я предлагаю Вам построить свою собственную систему (возможно, основанную на моей), чтобы использовать ее, когда Вам необходимо вручную строить триггеры/процедуры или что бы то ни было еще. Недостаточное тестированиеКогда приборная панель в вашем автомобиле говорит, что двигатель перегрет, на что Вы грешите в первую очередь? Двигатель. А почему не предположить, что сломан индикатор? Или что-то еще менее важное? Имеется две причины: · Двигатель - самый важный компонент автомобиля, и обычно винить нужно сначала самую важную часть системы. Поскольку профессионалы баз данных знают, первая вещь, на которую стоит грешить, когда бизнес-система работает медленно, - это база данных. Почему? Во-первых, потому, что это центральная часть большинства любых бизнес-систем, а во-вторых, потому что это также слишком часто верно. Мы можем сыграть свою роль в рассеивании этого представления, глубоко изучив систему, которую мы создали, и поняв пределы ее применения через тестирование. Но давайте сойдемся на том, что тестирование - это первая вещь, которая войдет в проектный план, когда пройдет немного времени. И что наиболее страдает от нехватки тестирования? Функциональные возможности? Возможно, немного, но пользователи заметят и будут жаловаться, что не работает кнопка "Save", и они не могут сохранить изменение строки, на редактирование которой они потратили 10 минут. Но что действительно становится осью во всем процессе - это глубокое испытание системы, чтобы удостовериться, что проект, над которым Вы (по-видимому) работали столь напряженно с самого его начала, фактически реализован правильно. Но, скажите Вы, пользователи приняли работу системы, разве это не достаточно хорошо? Проблема с этим утверждением состоит в том, что то, что обычно составляет "тестирование" для пользовательского принятия, обычно представляет тыканье вокруг и около для испытания функциональных возможностей, которые они понимают, и дают Вам добро, если небольшая часть системы работает. Это адекватное испытание? Нет, в любой другой промышленности это определенно неприемлемо. Вы хотите, чтобы таким образом проверяли ваш автомобиль? "Ну, мы проехали на нем медленно один раз вокруг этого квартала солнечным днем без проблем; это хорошо!" Когда этот автомобиль впоследствии поломается в первой же поездке по автостраде, или в первый же дождливый или снежный день, то водитель будет иметь полное право очень огорчиться. Слишком многие систем баз данных проверяются как тот автомобиль, только лишь с небольшим тыканьем вокруг, чтобы увидеть, как работают отдельные запросы и модули. Первый реальный тест совершается в рабочих условиях, когда пользователи пытаются сделать реальную работу. Это особенно верно, когда проект реализуется для единственного клиента (даже хуже, когда это - корпоративный проект, ибо управление больше заинтересовано в завершении работ, чем в качестве). Как правило, главные ошибки находятся быстро, особенно связанные с производительностью. Если вы сразу попробовали запустить систему при полной нагрузке со стороны пользователей, фоновых процессов, технологических процессов, процедур обслуживания системы, ETL и т.д., то Вы, весьма вероятно, обнаружите, что не ожидали всех этих проблем с блокировками, которые вызываются пользователями, создающими данные, в то время как другие читают их, или проблем с аппаратными средствами, вызванными их плохой настройкой. Могут пройти недели, прежде чем заглохнут крики "SQL Server не может обработать это" даже после того, как Вы сделали надлежащую настройку. Как только вы избавитесь от главных ошибок, крайние случаи (которые довольно редки, например, когда пользователь входит отрицательное число в качестве количества рабочих часов) начинают поднимать свои уродливые головы. То, с чем заканчивают на этом этапе, - программное обеспечение, которое время от времени терпит крах, что весьма напоминает нечто сверхъестественное (так как большое количество крайних багов обнаружатся способами, которые не очень очевидны и действительно трудно находимы). Теперь намного тяжелее выполнять диагностику и исправлять ошибки, поскольку теперь вы сталкиваетесь с тем, что пользователи работают с реальными данными и стараются выполнить свою работу. Плюс к тому у вас, вероятно, имеется менеджер или даже два, которые сидят за вашей спиной и говорят каждые 30 секунд: "Когда это будет сделано?", -даже при том, что обнаружение такого рода ошибок, которые приводят к незначительному (но все же важному) отклонению в данных, может занимать дни и недели. Если бы надлежащее тестирование было выполнено, никогда бы не потребовались недели испытания для поиска этих ошибок, поскольку надлежащий план тестирования учитывает все возможные типы отказов, кодирует их в автоматизированный тест, который многократно запускается. Хорошее тестирование не позволит обнаружить все ошибки, но приведет вас к состоянию, в котором большинство проблем исходного проекта сведено к минимуму. Если бы каждый настаивал на строгом плане тестирования как составной и неизменной части процесса разработки базы данных, то возможно настанет день, когда база данных не будет рассматриваться первым кандидатом для пересмотра при возникновении проблем с производительностью системы. РезюмеПроект базы данных и его реализация - краеугольный камень любых проектов, ориентированных на обработку данных (читайте 99.9 % бизнес-приложений), и должен рассматриваться таковым, когда Вы занимаетесь разработкой. Эта статья, по-видимому, несколько нравоучительная, служит больше напоминанием мне и тому, кто ее читает. Некоторые советы типа надлежащего планирования, использования нормализации, строгого следования стандартам именования и документации вашей работы - это вещи, которые даже лучшие администраторы баз данных и архитекторы данных вынуждены заставлять себя делать. В состоянии напряжения, когда менеджер менеджера вашего менеджера ругается на то, что процесс затягивается, не легко вернуться и напомнить им, что они платят Вам сейчас, или они платят Вам позже. Эти задачи приносят дивиденды, которые очень трудно оценить количественно, поскольку чтобы оценить количество успеха, ему должна предшествовать неудача. И даже когда Вы преуспеваете в одной области, слишком часто незначительные отказы неожиданно возникают в других частях проекта, что вообще делает незамеченными ваши успехи. Советы, которые я даю здесь, собирались многие годы; они перевели меня из разряда посредственных программистов/архитекторов базы данных в разряд хороших. Ни один из них не займет чрезмерного времени (кроме, возможно, проектирования и планирования), но все они потребуют больше времени, чем следование "легким путем". Давайте сойдемся на том, что если бы легкий путь оказался действительно легким в конечном счете, то я бы отставил более сложный путь в ту же секунду. Но этого нельзя сказать до тех пор, пока не будет получен конечный результат, и следует понять, что успех зависит от начальной точки в той же мере, что и от финальной. 26 февраля 2007 Полезная информация§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § В продаже еще имеется книга SQL. Задачи и решения, посвященная анализу ошибок, допускаемых при решении задач первого этапа. На сайте издательства Питер можно сделать заказ и познакомиться с содержанием. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
