Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 183
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 183 (29 марта 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 57-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Исправил баг, который заметила vlksm на странице статистики. § Обновилась страница отзывов: два новых отзыва от Дмитрия Борисенкова и Bhav Tailor. § Опять улучшего время второго этапа. Лидером по итогам второго этапа стал $erges (задач 138, время 1.521) § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3754 Число участников рейтинга - 15164 Число участников второго этапа - 1307 Число участников третьего этапа - 27 Сертифицировано на сайте - 255 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLХинт запроса OPTIMIZE FOR в SQL Server 2005decipherinfosys (оригинал: OPTIMIZE FOR query hint in SQL Server 2005 )Перевод Моисеенко С.И. В одной из публикаций нашего блога мы рассмотрели поведение считывания значений связанной переменной (bind variable peeking - Oracle) и parameter sniffing (SQL Server), которое имеет место, когда в SQL запросах используются связанные переменные на наборах данных, значения в которых распределены неравномерно. Для таких запросов или конкретных сценариев можно предписать выполнение конкретного плана запроса (plan guides - SQL Server или stored outlines - Oracle) или же предпочесть использование постоянных/литеральных значений вместо связанных переменных. Это один из сценариев, когда в типичной транзакционной системе использование связанных переменных может не дать желаемых результатов. В этой статье мы обсудим новый хинт запроса, который появился в SQL Server 2005 и называется "OPTIMIZE FOR". Это еще один способ решить проблему parameter sniffing в SQL Server. Эта опция связана с использованием plan guides (гидов плана) или может использоваться независимо для изменения построения плана выполнения оптимизатором. Этот хинт запроса дает указание оптимизатору использовать конкретное значение для локальной переменной, когда запрос проходит фазу оптимизации. Давайте рассмотрим пример его использования:
CREATE TABLE DECIPHER_TEST (COL1 INT IDENTITY NOT NULL, STAT_CODE INT NOT NULL) GO CREATE INDEX DECIPHER_TEST_IND_1 ON DECIPHER_TEST (STAT_CODE) GO SET NOCOUNT ON GO DECLARE @I INT SET @I = 1 WHILE (@I <= 1000000) BEGIN IF @I <=10 INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (0) IF (@I > 10 AND @I <= 10000) INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (30) IF (@I > 10000 AND @I <= 1000000) INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (90) SET @I = @I + 1 END
Мы использовали вышеприведенный скрипт, чтобы создать тестовую таблицу и наполнить ее 1 миллионом строк. Вот распределение данных:
SELECT STAT_CODE, COUNT(*) CNT_DISTR FROM DECIPHER_TEST GROUP BY STAT_CODE
STAT_CODE CNT_DISTR ----------- ----------- 0 10 30 9990 90 990000 Статистика тоже была собрана:
UPDATE STATISTICS DECIPHER_TEST WITH SAMPLE 100 PERCENT, ALL
Теперь давайте возьмем параметризованный запрос со значением параметра 90:
SET SHOWPLAN_TEXT ON GO DECLARE @STAT_CODE INT SET @STAT_CODE = 90 SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = @STAT_CODE
StmtText ------------------------------------- |-Table Scan(OBJECT:([AdventureWorks].[dbo].[DECIPHER_TEST]), WHERE:([AdventureWorks].[dbo].[DECIPHER_TEST].[STAT_CODE]=[@STAT_CODE])) Оптимизатор выполняет полное сканирование таблицы, что и должно делать, поскольку значение 90 достигается для большинства данных, в результате чего проход по индексу не даст никаких преимуществ. Теперь давайте заменим значение STAT_CODE на 0 и посмотрим, изменит ли теперь оптимизатор план выполнения на обход индекса, поскольку это значение используется лишь для 0.001% записей в таблице.
DECLARE @STAT_CODE INT SET @STAT_CODE = 0 SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = @STAT_CODE
StmtText ---------------------------------------------- |-Table Scan(OBJECT:([AdventureWorks].[dbo].[DECIPHER_TEST]), WHERE:([AdventureWorks].[dbo].[DECIPHER_TEST].[STAT_CODE]=[@STAT_CODE])) Как Вы можете видеть, план выполнения остается таким же, как прежде; это происходит потому, что план выполнения параметризованного запроса был кэширован во время предыдущего выполнения. Теперь давайте используем хинт "OPTIMIZE FOR", и посмотрим, сможем ли мы заставить оптимизатор оптимизировать запрос для значения STAT_CODE равного 0:
DECLARE @STAT_CODE INT SET @STAT_CODE = 0 SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = @STAT_CODE OPTION (OPTIMIZE FOR (@STAT_CODE = 0))
StmtText ------------------------------------- |-Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |-Index Seek(OBJECT:([AdventureWorks].[dbo].[DECIPHER_TEST].[DECIPHER_TEST_IND_1]), SEEK:([AdventureWorks].[dbo].[DECIPHER_TEST].[STAT_CODE]=[@STAT_CODE]) ORDERED FORWARD) |-RID Lookup(OBJECT:([AdventureWorks].[dbo].[DECIPHER_TEST]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) Как показывает приведенный выше план выполнения запроса, оптимизатор на этот раз выбрал индекс и выполнил RID look-up (поиск идентификатора записи), чтобы получить данные, которые не были представлены в индексе. Это имеет смысл, поскольку селективность значения данных такова, что поиск в индексе должен дать преимущество. Итак, что же случится теперь, если мы выполним запрос со значением 90? Так как мы вынудили оптимизатор всегда формировать оптимизированный план выполнения для заданного значения кода состояния 0, то когда мы используем значение 90, по-прежнему будет использоваться план с индексом, который показан выше. Так, насколько это хорошо? В нашем конкретном сценарии, скажем, значения STAT_CODE представлены следующим образом: 0: Запись готова для обработки В большинстве случаев код, который взаимодействует с данной таблицей, будет выполнять поиск, на основании значения кода состояния либо 0, либо 30. Ситуации, где будут использоваться 90 или 99, типично представляются только запросами, которые проверяют данных или формируют отчеты, при этом они будут выполняться меньшее число раз по сравнению с запросами в транзакционных системах. Поэтому в действительно транзакционной системе, где требуется один раз парсить запрос и много раз его выполнить, можно все еще использовать связанные переменные (параметризованные запросы) и иметь план выполнения, который лучше всего представляет распределение данных и является оптимальным. В системе же генерации отчетов можно использовать запрос, основанный либо на константах/литералах, либо на запросе без хинта "OPTIMIZE FOR". Ниже представлен тот же самый запрос, использующий постоянное значение (константу/литерал), и его план выполнения, демонстрируемый оптимизатором:
SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = 90
StmtText -------------------------------------- |-Table Scan(OBJECT:([AdventureWorks].[dbo].[DECIPHER_TEST]), WHERE:([AdventureWorks].[dbo].[DECIPHER_TEST].[STAT_CODE]=(90))) 23-10-2007 Полезная информация§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
