#021<< #022

ТЕМА ДЛЯ ОБСУЖДЕНИЯ (для программистов)

Прислал Константин из г. Петрозаводска, cat2@onego.ru

Всякий культурный SQL-программист знает о возможности создания временных таблиц, но редко эту возможность использует. В то же время существует класс задач, решение которых без временных таблиц затруднительно или даже невозможно.
Напомню, что временные таблицы могут быть локальными (начинаются с префикса #) и глобальными (начинаются с префикса ##). Разница между ними в том, что локальные временные таблицы доступны только из создавшей ее сессии, а глобальные могут быть доступны другим сессиям.
Классическая задача - расчет оборотно-сальдовой ведомости с группировкой по счетам. То есть нам надо получить остаток денежных средств на счетах на начало периода, приход и расход за период, остаток на конец периода.

Имеется таблица журнала операций. Без излишних для этой задачи подробностей.

Поля:

Наименование        Тип                           Содержание

WorkDate                  DateTime                 Дата совершения операции
Acount                       Char(4)                    Счет бухучета
Quanity                      Numeric(12,3)          Количество единиц измерения.
                                                                    Положительное - приход,
                                                                    отрицательное - расход.
Price                           Numeric(12,2)          Цена за единицу измерения

Выглядит это примерно так

WorkDate                                Acount         Quanity         Price
Jan 19 1998 12:00AM             121                 -1.000             110.00
Jan 19 1998 12:00AM             101                 8.065              432.59
Jan 19 1998 12:00AM             105                 17.000             1,206.06
Jan 19 1998 12:00AM             105                 -2.000             1,206.06

Для решения задачи мы соединяем три запроса: остаток на начало, приход и расход за период. Сохраняем это во временной таблице и затем суммируем ее. Остаток на конец, естественно, подсчитываем.

set transaction isolation level read uncommitted
/*Как правило, такой расчет производится после выполнения всех операций в отчетном периоде, так что не стоит оставлять уровень изоляции по умолчанию (read committed). Опасности чтения незаверщенных транзакций практически отсутствует, и вы не мешаете другим пользователям своими блокировками*/
select Acount , sum(quanity*price) as SaldoStart,0 as Debet,0 as Credit
/*Столбим места для значений Debet и Credit*/
into #temp
/*Вот оно где записывается! Только для первого запроса!*/
from journal
where Workdate <'19980101'
group by Acount
union
/*Объединяем с приходом. Структуры таблиц должны совпадать! Названия полей не нужны, они берутся из первого запроса*/
select Acount , 0, sum(quanity*price), 0
from journal
where Workdate between '19980101' and '19980131'
and Quanity>0
group by Acount
union
select Acount, 0, 0, sum(quanity*price)
from journal
where Workdate between '19980101' and '19980131'
and Quanity<0
group by Acount
go
/*Окончание группы операций. GO - полезная штука. Освобождает память, которой всегда не хватает. */
select Acount, sum(SaldoStart), sum(Debet),
-sum(Credit), sum(SaldoStart+Debet+Credit) from #temp
group by Acount
/*Результатом объединения является таблица, в которой на каждый счет приходится три строки, так что нужно просуммировать еще раз. Бухгалтеры не любят минусов, меняем знак Credit*/
union
/*Для прикола объединяем еще и строку с итогами по всем счетам, и у нас получается полная таблица, не требующая дополнительной обработки клиентом*/
select 'Итого: ', sum(SaldoStart),sum(Debet),
-sum(Credit), sum(SaldoStart+Debet+Credit) from #temp
go
drop table #temp
/*Уничтожаем временную таблицу. Действие необходимое при отладке запроса в ISQW. Если все это предполагается поместить в хранимую процедуру, то можно опустить, уничтожение произойдет автоматически.*/
go
/*Еще раз заставили SQL "все забыть"*/

Если кому-то известно решение этой задачи без временной таблицы, буду рад узнать об этом.

Обсудить тему можно в форуме, где размещена эта статья:
http://book.by.ru/cgi-bin/book.cgi?book=SQLServer-Forum&i=974201953


СОВЕТ

Инструментарий контроля и оптимизации запросов к SQL Server 2000
(По материалам статьи Brad McGehee на swynk.com «SQL Server 2000 Performance Tuning Tools»)

SQL Server 2000 Query Analyzer
 
SQL Server 2000 Query Analyzer – это не только инструмент для разработки и отладки   Transact-SQL, он - также помогает анализировать эффективность исполнения кода запросов. Рассмотрим некоторые его важные особенности, которые могут использоваться для повышения эффективности запросов Transact-SQL.
 
Show Execution Plan
 
Всякий раз, когда Вы вводите запрос в Query Analyzer, Вы можете после его исполнения анализировать Execution Plan (план выполнения). Опция Show Execution Plan доступна через Query menu главного меню Query Analyzer, и должна быть включена перед запуском запроса на выполнение. После выполнения запроса, план его выполнения отображается в отдельном окне в графическом формате (см. соответствующую вкладку, которая появляется ниже окна результатов запроса), и показывает, как Query Optimizer выполнял запрос. План выполнения должен читаться справа налево. Графическое представление плана может представить достаточно много интересной информации для анализа эффективности (например, не использование имеющегося индекса), но существенная часть полезных данных о ходе исполнения запроса скрыта внутри каждого из шагов плана. Для этого нужно щёлкнуть мышкой по соответствующему блоку графической схемы исполнения запроса, и Вы получите детальную информацию о том, что Query Optimizer делал на каждом шаге выполнения. Подробное описание получаемой таким образом информации и способов её интерпретации для оптимизации кода запросов можно получить в Books Online. Кроме того, Query Analyzer может создать Execution Plan без фактического выполнения запроса, правда такой путь не гарантирует точных результатов.
 
Show Server Trace
 
Опция Show Server Trace также может быть использована в Query Analyzer для анализа   эффективности исполнения запросов и хранимых процедур. Включение этой опции позволит Вам увидеть трассировку исполнения запроса в виде строк, содержащих: текст команд   Transact-SQL; Event Class; длительность исполнения; утилизацию CPU и I/O.
 
Show Client Statistics
 
Включение в Query Analyzer этой опции позволяет получить статистические таблицы по исполнению запроса пользователя. Например, в виде таблиц может быть представлена следующая информация:
 
Число строк, к которым были применены операторы INSERT, UPDATE, и DELETE;
Число строк, к которым был применен оператор SELECT;
Число транзакций пользователя;
Число roundtrips сервера;
Число посланных байт;
Суммарное процессорное время исполнения.
 
 
SQL Analyzer Manage Index
 
SQL Analyzer Manage Index также может быть использован для анализа эффективности использования индексов Ваших таблиц. Опция Manage Indexes доступна в Tools меню, и позволяет Вам добавлять, редактировать, или удалять индексы любой таблицы. Таким образом, Вы можете экспериментировать с разными наборами индексов и составляющих их полей.
 
Manage Statistics Tool
 
SQL Server автоматически ведёт внутреннюю статистику по строкам данных во всех ваших таблицах. Эта статистика используется Query Optimizer, чтобы выбрать оптимальный план выполнения кода Transact-SQL. Но иногда, статистика SQL сервером используется не оптимально, с чем призван бороться   Manage Statistics Tool. С помощью этого инструмента, Вы можете в   Query Analyzer изменять автоматически созданную SQL сервером статистику,   а также добавлять, редактировать, или удалять различные варианты статистики. Подбор наиболее удачного варианта статистики может также положительно сказаться на эффективности исполнения запросов.
 
Index Tuning Wizard
 
Использование в Query Analyzer опции Index Tuning Wizard может помочь Вам создать оптимальные индексы для запросов. Правда, существует опасность того, что индекс, оптимальный для анализируемого Вами запроса, окажется совершенно не пригодным для всех остальных применений.
 
SQL Server 2000 Profiler
 
SQL Server 2000 Profiler является мощным инструментом обеспечения эффективности SQL Server, который позволяет Вам зафиксировать многие параметры взаимодействия прикладной программы c SQL сервером.
В терминах SQL Server 2000 Profiler, любое взаимодействие прикладной программ и сервера баз данных рассматривается как событие которое относится к может быть отнесено к стандартной группе в наборе Event Classes. Каждый Event Class включает одно или более однотипных событий. Например, Event Class "Performance" имеет восемь событий относящихся к   Execution Plan и Show Plan Statistics. Всего Profiler имеет 13 типов Event Classes. Вам будут предлагаться для анализа наборы таблиц, в которых фигурируют поля, подобные: NTUserName или ApplicationName, содержащие информацию о зафиксированных событиях, а также включён код Transact-SQL. Поскольку, событий может быть много, возможно наложение фильтров для выделения только интересующих Вас групп событий, а также пользователей, баз, таблиц или серверов.
Также, для упрощения анализа событий, Profiler имеет возможность создавать т.н. Trace Templates, которые являются файлами-описателями ваших манипуляций с выборками событий и фильтрами, что позволяет использовать одинаковые методы анализа много раз. Причём, Вы можете использовать эти Trace Templates в любое время и даже по расписанию, выгружая результаты исследования в файл. Для облегчения работы можно использовать Trace Wizard, который содержит ряд основных шаблонов для наиболее распространённых задач анализа.
Используя Profiler, Вы может анализировать не только свои творения, но и программы, написанные другими людьми, исходники которых от Вас закрыты, поскольку, Вам будет предоставлена возможность видеть весь процесс обмена между программой и сервером, начиная от кода запроса и кончая непосредственно данными.
С помощью набора созданных Вами Trace Templates, можно отслеживать и идентифицировать проблемы, причина которых кроется в коде программы, и которые проявляются из за не оптимальной или не верной формулировки запроса, что может существенно сказаться на производительности.
Другая особенностью Profiler является то, что Вы можете использовать его отчёты для оптимизации индексов в Index Wizard.
 
Index Wizard
 
SQL Server 2000 Index Wizard является опцией Profiler и вызывается из меню Tools. Фактически, эффективное использование Index Wizard может быть только после начала промышленной эксплуатации вашей базы данных. Это происходит потому, что объём базы данных изменяется во времени, и, вместе с этим, индексы оптимальные в начале или при тестировании могут стать не эффективными в реальной ситуации. Следует заметить, что ни какой Index Wizard не может заменить опытного DBA.
На этапе тестирования структуры таблиц и индексов, обычно, создают «рабочую нагрузку», которая имитирует реальное состояние базы данных. Создав после этого необходимый набор Trace Templates, Вы можете с помощью Query Optimizer и Index Wizard оптимизировать не только запросы, но и индексы, используемые в них. Кроме того, протестировав полностью все возможные варианты взаимодействия приложения и сервера, Вам станет ясно, какие из созданных на этапе проектирования индексы не будут использоваться в условиях, достаточно близко приближённых к реальным. Таким образом, Вы сможете не только оптимизировать код и индексы, но и избавить сервер от непродуктивного использования ресурсов.
Рекомендации, которые выдаёт Index Wizard, можно сохранить, для того, что бы в последующем сравнить их с аналогичными на промышленной системе.   Поскольку Index Wizard может выдавать только рекомендации, никогда не будет лишним их тщательно проверить. Особенно, если Вы собираетесь переносить   результаты своих изысканий на полигоне в промышленную среду.
 
System (Performance) Monitor
 
System Monitor - не относится к SQL Server 2000, а входит в поставку Windows 2000. Тем не   менее, любой администратор NT может контролировать с помощью него несколько сотен счётчиков-индикаторов эффективности Windows, и более 110 счетчиков SQL Server.
Наиболее полезными счётчиками SQL Server 2000 можно назвать: % Total Processor Time,   Memory Pages/Sec, Avg. Disk Queue Length и Buffer Cache Hit Ratio. Использование этих счётчиков уже неоднократно описывалось в предыдущих выпусках рассылки.
 
ГОТОВИМСЯ К ТЕСТУ ПО 1139А
 
ШПАРГАЛКА №5 Продолжение (обзор официального курса Microsoft)
Архив шпаргалок Вы найдёте на следующих сайтах:
http://pilgrim.rostov-na-donu.ru/sql/default.htm
http://mssqlhelp.com.ru
http://subscribe.ru/archive/comp.soft.winsoft.sqlhelpyouself
 
Резервирование системных баз данных
 
Поскольку системные базы данных содержат жизненно важную для функционирования сервера информацию, они подлежат обязательному резервному копированию. Особенно это критично, когда Вы вносите такие изменения в структуру баз сервера, которые могут повлиять на его работу. Минимальным требованием к резервированию системной информации является резервное копирование системных баз до и после изменений, вносимых в структуру его баз и системных объектов. В таком случае, возможные фатальные последствия таких изменений могут быть преодолены, путём восстановления предыдущего состояния из имеющихся копий.
Резервное копирование системной информации следует организовывать таким образом, что бы в любой момент времени у Вас была возможность восстановить любую системную базу данных, причём, состояние этой базы было бы актуально на этот момент и содержало все внесённые до этого момента изменения. Как вариант, официальный курс предлагает настроить расписание резервного копирования системных баз данных и обязательно делать копии после внесения изменений.
Копированию подлежат три системные базы: master (содержащая информацию обо всех системных объектах сервера), msdb (содержащая информацию о расписаниях, оповещениях и операторах SQLServerAgent), model (определяет конфигурацию по умолчанию для вновь создаваемых баз).
Если Вы не имеете резервных копий перечисленных баз данных, но нуждаетесь в их восстановлении, Вы можете пересоздать их с помощью утилиты rebildm, которая находится в BINN каталоге сервера.
Если с изменениями, после которых необходимо резервировать системную информацию, для баз model и msdb всё понятно, то для базы master можно заострить внимание, что изменения происходят при использовании операторов CREATE DATABASE, ALTER DATABASE, DROP DATABASE, а также операций их аналогов в SQL SEM. Кроме того, создавайте резервную копию после использования системных хранимых процедур: sp_logdevice, sp_addserver, sp_dropserver, sp_addlinkedserver.
 
Резервирование баз данных
 
О необходимости разработки плана резервного копирования баз данных, удовлетворяющего специфические требования каждого конкретного применения, мы уже не однократно говорили в предыдущих номерах рассылки. Однако существуют события, после которых необходимо в обязательном порядке изготовить «внеплановые» копии. Перечислим их:
1. Копия изготавливается сразу после создания базы данных. Без неё не возможно будет восстановить журнал транзакций, да и сами транзакции выгружать из журнала будет некуда.
2. После создания или перестройки индексов. Хоть это и не является непременным условием, но может избавить Вас от лишней головной боли, в случае, если Вам после этого понадобиться восстановить данные. Дело в том, что в копии до создания индексов содержится старая структура индексов. С другой стороны,   журналируется только факт создания индекса, а не сам индекс. Тогда, при восстановлении из старой копии и сохранённых до и после создания индекса записей журнала транзакций, сервер вынужден будет перестроить индекс автоматически, а на это уйдёт ой, как больше времени, чем на простое восстановление.
3. После инициации процесса очистки журнала транзакций (BACKUP LOG WITH TRUNKATE_ONLY или BACKUP LOG WITH NO LOG) также необходимо сделать копию, иначе Вам не удастся восстановить то, что было на этот момент в журнале.
4. Вам придётся сделать резервную копию и после того, как на сервере будет исполнена одна из не регистрируемых в журнале транзакций операция:
- WRITETEXT (без параметра WITH LOG);
- UPDATETEXT (без параметра WITH LOG);
- SELECT…INTO, создающий постоянную таблицу;
- BCP (bulk copy program) для массового ввода данных в базу из файла.
 
Нужно отметить, что процесс резервного копирования, как правило, проходит не заметно для пользователей и не мешает их работе, однако есть исключения. Приведём ниже список операций, которые запрещено делать во время резервного копирования:
1. Не допустимо создавать или изменять размеры баз данных ( CREATE/ALTER DATABASE).
2. Нельзя создавать или перестраивать индексы;
3. Выполнять не регистрируемые в журнале операции (те, что мы описали чуть выше);
 
Если резервирование уже началось, то сервер просто не даст Вам выполнить перечисленные операции. И наоборот, резервное копирование не начнётся, когда запущена одна из таких операций.
Ещё один примечательный момент: во время резервного копирования базы не могут автоматически расширяться. Так что старайтесь не делать копии, когда в вашу базу идёт активный ввод информации, вдруг места не хватит…

Продолжение следует

ПОЛЕЗНОСТИ

Статья «Введение в BackOffice 2.5» Виктора и Натальи Олифер
http://nt-applic.uiggm.nsc.ru/docs/BackOf_2.5/index.shtml
Материалы фестиваля MICROSOFT 2000/TECHED
http://www.msfestival.ru/festival2000/presentations.asp
Описание SQL на русском языке
http://muma.tusur.ru/~cse/docs/sql/sql-cont.html
Клуб сертифицированных специалистов
http://www.certification.ru/
Лекция «Основы современных баз данных» 1997г., С.Д. Кузнецов
http://0000.nm.ru/cs/lectures.htm

Новые технические статьи Microsoft:

Q271773 - SAMPLE: Как восстанавливать Actions, Functions и Sets Schemas для Microsoft OLAP Provider.
http://support.microsoft.com/support/kb/articles/Q271/7/73.asp
Q272269 - HOWTO: Восстановление XML данных с использованием SQL XML Query на ASP клиенте.
http://support.microsoft.com/support/kb/articles/Q272/2/69.asp
Q271649 - INFO: Не поддерживаемый метод отслеживания запросов через OLE DB API.
http://support.microsoft.com/support/kb/articles/Q271/6/49.asp
Q271242 - INF: Трастовые отношения Windows NT Доверие не используются для Multiprotocol Connection в случае SQL Server аутентификации.
http://support.microsoft.com/support/kb/articles/Q271/2/42.asp
Q229929 - INFO: Ключи системного для SQL Server Connection.
http://support.microsoft.com/support/kb/articles/Q229/9/29.asp
Q274551 - HOWTO: Генерация ODBC Trace в ODBC Data Source Administrator
http://support.microsoft.com/support/kb/articles/Q274/5/51.asp
Q200117 - INF: Не возможно удалить не обязательные компоненты SQL Server
http://support.microsoft.com/support/kb/articles/Q200/1/17.asp
Q277629 - INF: Сбои обновления SQL Server 2000, когда база данных не находится в состоянии Writable.
http://support.microsoft.com/support/kb/articles/Q277/6/29.asp
Q275312 - INF: Процедуры SQL Server для изменения Object Owner.
http://support.microsoft.com/support/kb/articles/Q275/3/12.asp
Q272210 - INFO: Системные Таблицы в SQL CE Database.
http://support.microsoft.com/support/kb/articles/Q272/2/10.asp
Q270896 - INFO: Query Analyzer для SQL CE не поддерживает Descriptive типы данных.
http://support.microsoft.com/support/kb/articles/Q270/8/96.asp
Q269662 - INF: Информация SQL Server 2000 Books Online относительно распределенных, разбитых на фрагменты Views.
http://support.microsoft.com/support/kb/articles/Q269/6/62.asp
Q268361 - INF: Последствия перемещения баз данных из SQL 2000 Enterprise Edition SQL 2000 Standard Edition.
http://support.microsoft.com/support/kb/articles/Q268/3/61.asp
Q272446 - INF: Profiler ограничивает максимальный размер Trace File до 1ГБ.
http://support.microsoft.com/support/kb/articles/Q272/4/46.asp
Q216434 - INFO: Как Internet Explorer определяет, являются ли безопасным ActiveX Controls.
http://support.microsoft.com/support/kb/articles/Q216/4/34.asp
Q253537 - Как определять выборки для Windows 2000 Indexing Service
http://support.microsoft.com/support/kb/articles/Q253/5/37.asp

Q227662 - SQL Отсутствуют счётчики Performance Monitor.
Q246262 - SQLFSTIN ускоряет OLEDB Inserts при использовании IROWSETFASTLOAD()
Q257731 - Err Msg "Rows Must Be Released" для SQLOLEDB и ADO
Q246438 - Вставка Date/Time значений в SQL Server через ADO не поддерживается MS
Q245267 - "OLEDB_SERVICES = -2" не отключает Autoenlistment
Q275927 - Err Msg при установке свойств ADO Recordset в Nothing

Заказать по почте перечисленные выше статьи можно с помощью этой ссылки:
mailto:mshelp@microsoft.com?subject=Q271773, Q272269, Q271649, Q271242, Q229929, Q274551, Q200117, Q277629, Q275312, Q272210, Q270896, Q269662, Q268361, Q272446, Q216434, Q253537, Q227662, Q246262, Q257731, Q246438, Q245267, Q275927

ДОСТУПНЫЕ РЕСУРСЫ РАССЫЛКИ:

СТРАНИЦА КАТАЛОГА
http://subscribe.ru/catalog/comp.soft.winsoft.sqlhelpyouself
Зеркало в Ростове-на-Дону и АРХИВ №1
http://pilgrim.rostov-na-donu.ru/sql/default.htm
Зеркало в Cанкт-Петербурге и АРХИВ №2 (возможны перебои в работе в предстоящие выходные)
http://mssqlhelp.com.ru
АРХИВ на SUBSCRIBE.RU
http://subscribe.ru/archive/comp.soft.winsoft.sqlhelpyouself
СТАТИСТИКА
http://subscribe.ru/stat/comp.soft.winsoft.sqlhelpyouself
ФОРУМ
http://book.by.ru/cgi-bin/book.cgi?book=SQLServer-Forum