Отправляет email-рассылки с помощью сервиса Sendsay
  Все выпуски  

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 87


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 87 (13 мая 2006 г.)

http://www.sql-ex.ru

Новым посетителям сайта

Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 58-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.


Новости сайта

§ График сравнения времени решения задач второго этапа добавлен в меню (рейтинги).
Ссылка прежняя: http://www.sql-ex.ru/chart.php.

§ В связи с праздниками в сотне некоторое затишье. Тем не менее, движение наблюдалось. Вплотную подошли ко второму сертификационному порогу:
arm (120, 27.980)
Eugeniе (120, 43.362)
gps (120, 173.066)

§ Продолжили свое восхождение к вершине:
Voland3D (119, 30.243)
FanOfBeer (111, 44.983)

§ Число подписчиков - 3107

Число участников рейтинга - 5697

Число участников второго этапа - 559

Сертифицировано на сайте - 61

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 LastSolved LastVisit
1 Кувалкин К.С. (Cyrilus) 138 138 20 320 484 5.460 23 Mar 2006 12 May 2006
2 Kamaev V.M. (Heromantor) 138 138 20 320 128 9.066 14 Mar 2006 25 Mar 2006
3 Бураков С.Г. (burakov58) 138 138 20 320 419 17.395 24 Mar 2006 07 Apr 2006
4 Тарасов Д.Б. (Gavrila) 138 138 20 320 208 19.424 22 Mar 2006 12 May 2006
5 Голубин Р.С. (Roman S. Golubin) 138 138 20 320 210 20.835 16 Mar 2006 10 May 2006
6 Валуев Д.И. (Fiolent) 138 113 20 320 971 48.000 02 May 2006 12 May 2006
7 Войнов П.Е. (pаparome) 137 137 20 316 203 1.769 15 Mar 2006 12 May 2006
8 Зверев Д.Л. (dimzv) 137 137 20 316 869 3.278 22 Mar 2006 04 Apr 2006
9 Юлдашев М.Р. (Snowbear) 137 137 20 316 239 3.448 14 Mar 2006 08 May 2006
10 Абашин П.И. (Dizil) 137 137 20 316 203 3.776 15 Mar 2006 10 May 2006
11 Самохвалов В. (ValdemarES) 137 137 20 316 117 7.788 14 Mar 2006 10 May 2006
12 Мурашкин И.В. (lepton) 137 137 20 316 25 7.943 17 Apr 2006 08 May 2006
13 Носков Н.В. (niko2) 137 137 20 316 274 8.472 06 Apr 2006 06 Apr 2006
14 Крижевич С.А. (yaff) 137 137 20 316 259 14.801 16 Mar 2006 05 May 2006
15 Иванов А.Н. (Goapsy) 137 137 20 316 128 16.076 18 Mar 2006 10 May 2006
16 Держальцев В.А. (MadVet) 137 137 20 316 357 18.639 08 Apr 2006 15 Apr 2006
17 Зырин В.Е. (Vezyr) 137 137 20 316 63 20.618 17 Mar 2006 27 Apr 2006
18 Страшников А.С. (EffEct) 137 137 20 316 305 59.972 16 Mar 2006 02 May 2006
19 Матвеева Ю.Б. (Julia_M) 137 137 20 316 137 68.931 24 Mar 2006 11 May 2006
20 Галиаскаров Э.Г. (Galogen) 136 138 20 315 522 100.285 28 Apr 2006 28 Apr 2006

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Ганопольский Р.М. (Родион1976) 32 68 74 32 106 213 12 May 2006
2 >Blech E.V. (woland) 45 45 76 9 85 945 12 May 2006
3 Колебанов Р.В. (Роман) 45 45 76 9 85 948 12 May 2006
4 >Ханин А. (hanl) 44 44 72 9 81 998 12 May 2006
5 >Качанов Н.А. (nickk) 44 44 76 3 79 1035 12 May 2006
6 >Карасёва Н. (vlksm) 41 41 73 0 73 1150 12 May 2006
7 >Rezvyakov (SanyOK) 36 36 52 17 69 1245 12 May 2006
8 >Kolotnina A. (Virna) 32 60 60 0 60 572 12 May 2006
9 >Ворожейкин А.Ю. (Vorozheikin) 39 39 60 0 60 1451 12 May 2006
10 >Vlad V. (Dalv) 28 28 47 4 51 1728 12 May 2006
11 Лемех М. (MLemeh) 29 29 50 0 50 1744 12 May 2006
12 >Усольцев А. (prospector) 28 28 50 0 50 1746 12 May 2006
13 Pankratov S.A. (Marine) 10 55 25 23 48 445 09 May 2006
14 frenkental (a2010) 18 77 39 0 39 171 12 May 2006
15 Стадольник (Rediska) 27 33 38 1 39 1942 12 May 2006
16 Кардаш Я.С. (Jaroslav) 26 32 37 0 37 1995 08 May 2006
17 Черкавский О.В. (Oleg555) 20 26 37 0 37 2010 12 May 2006
18 unfor2 (unfor) 24 24 37 0 37 2231 09 May 2006
19 Бульба Ю.А. (fireball) 24 24 37 0 37 2253 12 May 2006
20 Bobryk S.M. (SergeiB) 28 28 36 0 36 2189 10 May 2006
21 Белый Е.А. (ewhite) 26 26 31 3 34 2416 12 May 2006
22 >Barton J. (whiskybar) 23 23 34 0 34 2431 12 May 2006
23 >Cool (Breakbeat) 23 23 34 0 34 2442 12 May 2006
24 Nikolay (Na_Sa) 20 29 33 0 33 2026 11 May 2006
25 >Хмара Е.В. (Evgeny72) 17 58 32 0 32 599 12 May 2006
26 Chernyshkin N. (_boost_) 2 58 4 27 31 288 12 May 2006
27 Bala (shiv761) 23 23 30 1 31 2621 10 May 2006
28 Крупин Е.А. (john_kr) 22 22 31 0 31 2633 08 May 2006
29 Матюшин Ю. (matew) 18 20 25 5 30 2597 10 May 2006

Изучаем SQL

Худшие методы - использование неуточненных имен

Christoffer Hedgate (оригинал: Worst Practice - Not Qualifying Objects With The Owner)
Перевод Моисеенко С.И.

Когда Энди недавно добавил его одну часть в ряду Худших Методов - о непродуманном добавлении столбца, это заставило меня вспомнить о худшей практике, с которой я пытаюсь сражаться в течение долгого времени. Возможно, она не столь специфична, как большинство других худших методов, но причина одного из худших методов, с которыми я часто сталкиваюсь, - это заурядная лень. Одной из ошибок, которую люди часто допускают только потому, что они ленивы, является то, что имена объектов не уточняются именем владельца. Я собираюсь обсудить это здесь, а в последующей статье я хочу обсудить некоторые другие ошибки, причиной которых является лень.

Использование уточненных имен

Никогда не забывайте уточнять названия объектов именем владельца объекта. Это улучшает производительность, по крайней мере, в силу двух причин. Прежде всего, когда Вы определяете имя объекта, не уточняя его именем владельца, SQL Server будет сначала искать объект, принадлежащий текущему работающему пользователю. Если такой объект не будет найден, будет использован объект с тем же самым именем, но принадлежащий dbo. Так, если пользователь по имени John подключился к Northwind и выполняет SELECT OrderID FROM Orders, SQL Server будет искать объект John.Orders в Northwind, и в случае его отсутствия будет использован dbo.Orders. Если бы он вместо этого выполнил запрос SELECT OrderID FROM dbo.Orders, SQL Server не нужно было бы искать объект по имени John.Orders. Это не может существенно повлиять на производительность, но ведь важно также избежать использования неожиданного объекта. Представьте, что приложение авторизуется как пользователь John и выполняет оператор SELECT OrderID FROM Orders. Это работает прекрасно до того дня, пока кто-то под логином John не решит создать таблицу с именем John.Orders (по той или иной причине). Теперь приложение внезапно начнет возвращать OrderIDs из новой таблицы John.Orders, и эту ошибку можно очень долго искать.

Другая причина повышения производительности в результате уточнения имен объекта именем владельца заключается в том, что есть хороший шанс, что SQL Server будет многократно использовать планы выполнения для операторов, записанных именно таким образом. Например, если Вы используете sp_executesql, чтобы выполнить динамические операторы SQL, Вы действительно использовать полностью уточненные имена объектов, чтобы SQL Server повторно использовал планы выполнения. Полностью уточненное имя объекта означает определение всех его четырех частей, то есть. Имя_сервера.имя_базы.имя_владельца.имя_объекта. Если Вы опустите любую из них, будет использоваться значение по умолчанию. Сервер по умолчанию и база данных - это конечно сервер и база данных, на которых выполняется запрос, а имя_владельца по умолчанию - это, как и ранее, dbo. Но даже при том, что используется имя по умолчанию, SQL Server не будет повторно использовать план выполнения для операторов, иначе специфицирующих уточненные имена объектов. Вы можете увидеть это сами в SQL Profiler, выполнив следующий небольшой скрипт. Стартуйте Profiler и подключите его к вашему серверу. Добавьте все события в классе событий Stored Procedures (хранимые процедуры) и удалите все другие события.

DECLARE @sql nvarchar(200)
DECLARE @params nvarchar(100)
DECLARE @intvar int

-- Создание оператора, используя полностью уточненные имена
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM dbo.Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 3
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Выполните тот же оператор еще раз, повторно используя предыдущий план выполнения
-- Замечание: SP:ExecContextHit в Profiler
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Тот же оператор, но не использующий полностью уточненные имена
-- Замечание: здесь нет SP:ExecContextHit в Profiler
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

Когда Вы запустите скрипт, то должны увидеть SP:CacheInsert, когда выполнен первый запрос, но когда он выполняется снова, используя только измененное значение @empid, Вы вместо этого увидите SP:ExecContextHit. Это означает, что SQL Server смог повторно использовать план выполнения. Наконец, когда третий оператор выполнен, Вы снова увидите SP:CacheInsert, и это даже при том, что результат запроса не изменился по сравнению с предыдущим случаем. Причина, как я объяснил выше, заключается в том, что второй оператор не уточняет имя Orders так, как это делалось в первом операторе.

Указание имени владельца важно не только для выполнения оператора SELECT. Не менее важно, возможно, даже более важно, уточнять объекты именем владельца при их создании. Например, если бы Джон создал таблицу, не указывая ее владельца, то он сам стал бы этим владельцем. Это означает, что любой другой пользователь, который захочет выполнить оператор SELECT для этой таблицы, должен уточнять имя таблицы его именем, делая выборку из нее. Если бы они также никогда не забывали уточнять имена объектов, это была бы не такая большая проблема, но так как мы знаем, что люди ленивы (или не знают, что означает уточнение имени), они, вероятно, будут это делать далеко не всегда. Итак, когда Джон создает свою таблицу, его оператор должен с CREATE TABLE dbo.TableName ... Есть, конечно, исключения, когда Вы действительно хотите создать таблицу с Джоном (John) в качестве владельца, но в таких случаях он должен указать это, то есть написать CREATE TABLE John.TableName ... Почему это важно? Представьте, что Джон создает эту таблицу в базе данных на тестовом сервере и сохраняет оператор в виде скрипта. Позже, когда эта таблица должна быть создана в рабочей базе данных, запустить скрипт мог кто-то иной, в результате чего таблица получит другого владельца.

Еще более важно уточнять имена объектов, используемых внутри хранимой процедуры. В противном случае, объекты, которые не уточнены именем владельца и упоминаются в операторах SELECT, INSERT, UPDATE и DELETE, по умолчанию будут приписаны владельцу хранимой процедуры, а не человеку, выполняющему ее. Опять таки, эти объекты могли бы быть теми, что Вы и хотите, но всегда лучше явно уточнять имя. Если процедура используется как механизм контроля доступа к данным в таблицах (то есть прямой доступ к таблицам ограничен, и все пользователи должны использовать процедуры, которые выбирают из этих таблиц данные), вы можете столкнуться с другой проблемой. Если имена для этих таблиц, доступ к которым ограничен, не будут уточнены в процедуре, то только создателю процедуры будет разрешен к ним доступ.

Наконец, если Вы не уточните имена объектов, используемых в процедурах, содержащих операторы CREATE/ALTER/DROP TABLE, TRUNCATE TABLE, CREATE/DROP INDEX, UPDATE STATISTICS, и командах DBCC, Вы можете разрушить систему. Причиной является то, что разрешение имен объектов происходит во время исполнения; при этом используется имя пользователя, который выполняет процедуру в качестве владельца по умолчанию для объектов, имена которых не полностью уточнены. Представьте, что пользователь Джейн имеет таблицу по имени Orders, где она хранит информацию своих заказов. Теперь ей нужно быстро и легко удалить эту информацию, поэтому она создает примерно такую процедуру:

CREATE PROCEDURE TruncateOrders
AS
BEGIN
    TRUNCATE TABLE Orders
END

Она тестирует процедуру, и та работает прекрасно, ее таблица Orders очищается. Потом она переносит процедуру в рабочую среду. Когда однажды Джону потребуется удалить данные из таблицы Orders, принадлежащей Джейн, он выполняет эту процедуру (выполняя EXEC Jane.TruncateOrders). Догадайтесь, что случится? Ну, если есть таблица с именем John.Orders, то она будет очищена, а если нет, то будет очищена dbo.Orders, поскольку dbo - это значение по умолчанию, которое использует SQL Server, когда не находит объект, принадлежащий текущему пользователю.

Итак, по причинам, рассмотренным выше, я рекомендую Вам сделать своей привычкой всегда уточнять все имена объектов, используя синтаксис имя_владельца.имя_объекта. И я говорю, что Вы должны писать таким образом не только ваши процедуры, я хочу, чтобы Вы сделали своей привычкой всегда поступать таким образом, даже в том случае, когда вы пишите запросы для быстрого одноразового исполнения. Если Вы сделаете это своей привычкой, то никогда не забудете это сделать, когда это окажется действительно необходимым и важным.

03.10.2004

Полезная информация

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

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

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное