Передача параметров в динамический запрос в T-SQL. Запросы t sql

MS SQL Server и T-SQL

Первый запрос на T-SQL

Последнее обновление: 05.07.2017

В прошлой теме в SQL Management Studio была создана простенькая база данных с одной таблицей. Теперь определим и выполним первый SQL-запрос. Для этого откроем SQL Management Studio, нажмем правой кнопкой мыши на элемент самого верхнего уровня в Object Explorer (название сервера) и в появившемся контекстном меню выберем пункт New Query:

После этого в центральной части программы откроется окно для ввода команд языка SQL.

Выполним запрос к таблице, которая была создана в прошлой теме, в частности, получим все данные из нее. База данных у нас называется university, а таблица - dbo.Students, поэтому для получения данных из таблицы введем следующий запрос:

SELECT * FROM university.dbo.Students

Оператор SELECT позволяет выбирать данные. FROM указывает источник, откуда брать данные. Фактически этим запросом мы говорим "ВЫБРАТЬ все ИЗ таблицы university.dbo.Students". Стоит отметить, что для названия таблицы используется полный ее путь с указанием базы данных и схемы.

После ввода запроса нажмем на панели инструментов на кнопку Execute, либо можно нажать на клавишу F5.

В результате выполнения запроса в нижней части программы появится небольшая таблица, которая отобразит результаты запроса - то есть все данные из таблицы Students.

Если необходимо совершить несколько запросов к одной и той же базе данных, то мы можем использовать команду USE, чтобы зафиксировать базу данных. В этом случае при запросах к таблицам достаточно указать их имя без имени бд и схемы:

USE university SELECT * FROM Students

В данном случае мы выполняем запрос в целом для сервера, мы можем обратиться к любой базе данных на сервере. Но также мы можем выполнять запросы только в рамках конкретной базы данных. Для этого необходимо нажать правой кнопкой мыши на нужную бд и в контекстном меню выбрать пункт New Query:

Если в этом случае мы захотим выполнить запрос к выше использованной таблице Students, то нам не пришлось бы указывать в запросе название базы данных и схему, так как эти значения итак уже были бы понятны:

SELECT * FROM Students

metanit.com

c# - Тайм-аут запроса/производительности запроса T-SQL

Если вы используете MS SQL 2008, вы можете/должны использовать параметры TableValue. По сути, вы отправляете свои данные в форме DataTable в хранимую процедуру.

Затем внутри вашей хранимой процедуры вы можете использовать параметры как "таблицу" и выполнить соединение или EXCEPT, или то, что вам нужно, чтобы получить ваши результаты.

Этот метод работает быстрее, чем использование функции для разделения, поскольку функции на сервере MS SQL очень медленные.

Но я думаю, что время берется из-за массивного ввода-вывода диска, который требуется для этого запроса. Поскольку вы просматриваете свой столбец UId, и поскольку они "случайные", здесь индекс не поможет. Двигателю придется прибегнуть к сканированию таблицы. Это означает, что вам потребуется какая-то серьезная производительность ввода-вывода на диске, чтобы получить результаты в "хорошее время".

Использование типа данных Uid, как в индексе, не рекомендуется. Однако это может не повлиять на ваше дело. Но позвольте мне спросить вас об этом:

Направляющие, которые вы отправляете из своего приложения, находятся в просто случайном списке указателей или здесь есть какие-то отношения деловых отношений или сущности? Возможно, ваша модель данных неверна для того, что вы пытаетесь сделать. Итак, как вы определяете, какие подсказки вы должны искать?

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

Например:

Наличие более быстрых дисков улучшит производительность
Если этот тип запросов запускается снова и снова, то больше памяти в поле поможет, потому что движок может кэшировать данные в памяти, и ему не нужно делать физические чтения
Если вы разделите таблицу, то механизм может распараллелить операцию поиска и быстрее получить результаты.
Если ваша таблица содержит много других полей, которые вам не всегда нужны, разделите таблицу в двух таблицах, где таблица1 содержит указатель и минимальный набор полей, а таблица2 содержит остальные, ускорит запрос совсем немного из-за требований ввода-вывода диска меньше
Много других вещей, чтобы посмотреть здесь.

Также обратите внимание, что при отправке в SQL-операторы adhoc, у которых нет параметров, движок должен создавать план каждый раз, когда вы его выполняете. В этом случае это не имеет большого значения, но имейте в виду, что каждый план будет кэшироваться в памяти, выталкивая любые данные, которые могли быть кэшированы.

Наконец, вы всегда можете увеличить свойство commandTimeOut в этом случае, чтобы преодолеть проблемы с таймаутом.

Сколько времени занимает сейчас и какое улучшение вы хотите получить, надеясь получить?

qaru.site

sql - Оптимизация запросов T-SQL

Я работаю над некоторыми обновлениями для внутренней системы веб-аналитики, которую мы предоставляем нашим клиентам (в отсутствие предпочтительного поставщика или Google Analytics), и я работаю над следующим запросом:

select path as EntryPage, count(Path) as [Count] from ( /* Sub-query 1 */ select pv2.path from pageviews pv2 inner join ( /* Sub-query 2 */ select pv1.sessionid, min(pv1.created) as created from pageviews pv1 inner join Sessions s1 on pv1.SessionID = s1.SessionID inner join Visitors v1 on s1.VisitorID = v1.VisitorID where pv1.Domain = isnull(@Domain, pv1.Domain) and v1.Campaign = @Campaign group by pv1.sessionid ) t1 on pv2.sessionid = t1.sessionid and pv2.created = t1.created ) t2 group by Path;

Я тестировал этот запрос с 2 миллионами строк в таблице PageViews, и для его выполнения требуется около 20 секунд. Я дважды просматриваю кластерное сканирование индекса в плане выполнения, оба раза он попадает в таблицу PageViews. В столбце "Создать" в этой таблице есть кластерный индекс.

Проблема заключается в том, что в обоих случаях она повторяется по всем 2 миллионам строк, что, по моему мнению, является узким местом производительности. Есть ли что-то, что я могу сделать, чтобы предотвратить это, или я в значительной степени максимизирован с точки зрения оптимизации?

Для справки цель запроса - найти первое представление страницы для каждого сеанса.

EDIT: После большого разочарования, несмотря на полученную здесь помощь, я не смог заставить этот запрос работать. Поэтому я решил просто сохранить ссылку на страницу входа (и теперь выйти) в таблице сеансов, что позволяет мне сделать следующее:

select pv.Path, count(*) from PageViews pv inner join Sessions s on pv.SessionID = s.SessionID and pv.PageViewID = s.ExitPage inner join Visitors v on s.VisitorID = v.VisitorID where ( @Domain is null or pv.Domain = @Domain ) and v.Campaign = @Campaign group by pv.Path;

Этот запрос выполняется через 3 секунды или меньше. Теперь мне нужно либо обновить страницу входа/выхода в режиме реального времени, когда записи страниц записываются (оптимальное решение), либо периодически запускают пакетное обновление. В любом случае, это решает проблему, но не так, как я предполагал.

Edit Edit: добавление отсутствующего индекса (после очистки от прошлой ночи) уменьшило запрос до нескольких миллисекунд). Woo hoo!

qaru.site

SQL Server. Тяжелые запросы

Данный функционал доступен в версии SQL Server 2005 и выше, независимо от редакции (Express, Standard и т.д.)Скрипт основан на предсталвении sys.dm_exec_query_stats:

set transaction isolation level read uncommitted select top 100 creation_time, last_execution_time, execution_count, total_worker_time/1000 as CPU, convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime], qs.total_elapsed_time/1000 as TotDuration, convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur], total_logical_reads as [Reads], total_logical_writes as [Writes], total_logical_reads+total_logical_writes as [AggIO], convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO], case when sql_handle IS NULL then ' ' else(substring(st.text,(qs.statement_start_offset+2)/2,( case when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset)/2 )) end as query_text, db_name(st.dbid)as database_name, object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st where total_logical_reads > 0 order by AvgDur desc

set transaction isolation level read uncommitted

select

top 100

creation_time,

last_execution_time,

execution_count,

total_worker_time/1000 as CPU,

convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],

qs.total_elapsed_time/1000 as TotDuration,

convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],

total_logical_reads as [Reads],

total_logical_writes as [Writes],

total_logical_reads+total_logical_writes as [AggIO],

convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],

case

when sql_handle IS NULL then ' '

else(substring(st.text,(qs.statement_start_offset+2)/2,(

case

when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2

else qs.statement_end_offset

end - qs.statement_start_offset)/2 ))

end as query_text,

db_name(st.dbid)as database_name,

object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

where total_logical_reads > 0

order by AvgDur desc

Комментарий

Первая строчка включает режим изоляции readuncommited — «грязное чтение». В бизнес-приложениях этот режим, конечно лучше не использовать, но для данной задачи, в которой нет требований по обеспечению целостности данных, он поможет не ждать (и не накладывать) блокировок системных объектов, особенно на нагруженной системе.В общем случае его мог бы заменить режим изоляции READ_COMMITED_SNAPSHOT, но его не всегда включают на уровне баз данных, а в данном случае он просто неактуален.

Получаемые столбцы:

creation_time — Время, когда запрос был скомпилирован. Поскольку при старте сервера кэш пустой, данное время всегда больше либо равно моменту запуска сервиса. Если время, указанное в этом столбце позже, чем предполагаемое (первое использование процедуры), это говорит о том, что запрос по тем или иным причинам был рекомпилирован.
last_execution_time — Момент фактического последнего выполнения запроса.
execution_count — Сколько раз запрос был выполнен с момента компиляцииКоличество выполнений позволяет найти ошибки в алгоритмах — часто в наиболее выполняемых запросах оказываются те, которые находятся внутри каких-либо циклов однако могут быть выполнены перед самим циклом один раз. Например, получение каких-либо параметров из базы данных, не меняющихся внутри цикла.
CPU — Суммарное время использования процессора в миллисекундах. Если запрос обрабатывается параллельно, то это время может превысить общее время выполнения запроса, поскольку суммируется время использования запроса каждым ядром. Во время использования процессора включается только фактическая нагрузка на ядра, в нее не входят ожидания каких-либо ресурсов.Очевидно, что данный показатель позволяет выявлять запросы, наиболее сильно загружающие процессор.

AvgCPUTime — Средняя загрузка процессора на один запрос.
TotDuration — Общее время выполнения запроса, в миллисекундах.Данный параметр может быть использован для поиска тех запросов, которые, независимо от причины выполняются «наиболее долго». Если общее время выполнения запроса существенно ниже времени CPU (с поправкой на параллелизм) — это говорит о том, что при выполнения запроса были ожидания каких-либо ресурсов. В большинстве случаев это связано с дисковой активностью или блокировками, но также это может быть сетевой интерфейс или другой ресурс.Полный список типов ожиданий можно посмотреть в описании представления sys.dm_os_wait_stats.
AvgDur — Среднее время выполнения запроса в миллисекундах.
Reads — Общее количество чтений.Это пожалуй лучший агрегатный показатель, позволяющий выявить наиболее нагружающие сервер запросы.Логическое чтение — это разовое обращение к странице данных, физические чтения не учитываются.В рамках выполнения одного запроса, могут происходить неоднократные обращения к одной и той же странице.Чем больше обращений к страницам, тем больше требуется дисковых чтений, памяти и, если речь идет о повторных обращениях, большее время требуется удерживать страницы в памяти.
Writes — Общее количество изменений страниц данных.Характеризует то, как запрос «нагружает» дисковую систему операциями записи.Следует помнить, что этот показатель может быть больше 0 не только у тех запросов, которые явно меняют данные, но также и у тех, которые сохраняют промежуточные данные в tempdb.
AggIO — Общее количество логических операций ввода-вывода (суммарно)Как правило, количество логических чтений на порядки превышает количество операций записи, поэтому этот показатель сам по себе для анализа применим в редких случаях.
AvgIO — Среднее количество логических дисковых операций на одно выполнение запроса.Значение данного показателя можно анализировать из следующих соображений:Одна страница данных — это 8192 байта. Можно получить среднее количество байт данных, «обрабатываемых» данным запросом. Если этот объем превышает реальное количество данных, которые обрабатывает запрос (суммарный объем данных в используемых в запросе таблицах), это говорит о том, что был выбран заведомо плохой план выполнения и требуется заняться оптимизацией данного запроса.Я встречал случай, когда один запрос делал количество обращений, эквивалентных объему в 5Тб, при этом общий объем данных в это БД был 300Гб, а объем данных в таблицах, задействованных в запросе не превышал 10Гб.В общем можно описать одну причину такого поведения сервера — вместо использования индекса сервер предпочитает сканировать таблицу или наоборот.Если объем логических чтений в разы превосходит общие объем данных, то это вызвано повторным обращениям к одним и тем же страницам данных. Помимо того, что в одном запросе таблица может быть использована несколько раз, к одним и тем же страницам сервер обращается например в случаях, когда используется индекс и по результатам поиска по нему, найденные некоторые строки данных лежат на одной и той же странице. Конечно, в таком случае предпочтительным могло бы быть сканирование таблицы — в этом случае сервер обращался бы к каждой странице данных только один раз. Однако этому часто мешают… попытки оптимизации запросов, когда разработчик явно указывает, какой индекс или тип соединения должен быть использован.Обратный случай — вместо использования индекса было выбрано сканирование таблицы. Как правило, это связано с тем, что статистикаустарела и требуется её обновление. Однако и в этом случае причиной неудачно выбранного плана вполне могут оказаться подсказки оптимизатору запросов.
query_text — Текст самого запроса
database_name — Имя базы данных, в находится объект, содержащий запрос. NULL для системных процедур
object_name — Имя объекта (процедуры или функции), содержащего запрос.
query_plan — План запроса. Достаточно щёлкнуть по выводу и в новом окне откроется план запроса. Для пользователей SQL Server 2005 есть особенности (см. ниже)

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

Для пользователей SQL Server 2005

Для получения графического представления в SSMS 2005 придется сохранить полученный план выполнения в файл с расширением .sqlplan и затем снова открыть его в SSMS. В данном случае все зависит от версии именно клиента — «графические» планы с SQL Server 2005 можно получать в SSMS 2008 без промежуточного сохранения в файл.

Автор: Дмитрий Костылёв

*** *** *** *** *** *** *** *** *** *** *** ***Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Facebook

Twitter

Вконтакте

Google+

sqlcom.ru

SQL Server 2005. Программирование на T-SQL (Урок 8)

Программирование на T-SQL

Синтаксис и соглашения T-SQL

Правила формирования идентификаторов

Все объекты в SQL Server имеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры.

Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словом SQL Server, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.

Завершение инструкции

Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке T-SQL точка с запятой не обязательна.

Комментарии

Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:

-- Это однострочный комментарий стиля ANSI

Также комментарии стиля ANSI могут вставляться в конце строки инструкции:

SELECT CityName – извлекаемые столбцы

FROM City – исходная таблица

WHERE IdCity = 1; -- ограничение на строки

Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов .

Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы.

Пример

многострочного

комментария

Одним из главных достоинств комментариев стиля С является то, что многострочные запросы в них можно выполнять, даже не раскомментируя.

Пакеты T-SQL

Запросом называют одну инструкцию T-SQL, а пакетом — их набор. Вся последовательность инструкций пакета отправляется серверу из клиентских приложений как одна цельная единица.

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

Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.

Отладка T-SQL

Когда редактор SQL обнаруживает ошибку, он отображает ее характер и номер строки в пакете. Дважды щелкнув на ошибке, можно сразу же переместиться к соответствующей строке.

В утилиту Management Studio версии SQL Server 2005 не включен отладчик языка T-SQL, — он присутствует в пакете Visual Studio.

SQL Server предлагает несколько команд, облегчающих отладку пакетов. В частности, команда PRINT отправляет сообщение без генерации результирующего набора данных. Команду PRINT можно использовать для отслеживания хода выполнения пакета. Когда анализатор запросов находится в режиме сетки, выполните следующий пакет:

SELECT CityName

FROM City

WHERE IdCity = 1;

PRINT 'Контрольная точка';

Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке «Сообщения» отобразится следующий результат:

(строк обработано: 1)

Контрольная точка

Переменные

Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:

DECLARE @Имя_Переменной Тип_Данных [,

@Имя_Переменной Тип_Данных, …]

Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символов Unicode, можно использовать следующую инструкцию:

DECLARE @UStr varchar(16)

Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLARE через запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменные a и b:

DECLARE

@a int,

@b int

Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULL и до включения в выражения должны быть инициализированы.

Задание значений переменных

В настоящее время в языке SQL предусмотрены два способа задания значения переменной — для этой цели можно использовать оператор SELECT или SET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что оператор SELECT позволяет получить исходное присваиваемое значение из таблицы, указанной в операторе SELECT.

Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:

SET @a = 1;

SET @b = @a * 1.5

Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:

DECLARE @c int

SET @c = COUNT(*) FROM City

SELECT @c

а следующий оператор выполняется вполне успешно:

DECLARE @c int

SET @c = (SELECT COUNT(*) FROM City)

SELECT @c

Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:

DECLARE @c int

SELECT @c = COUNT(*) FROM City

SELECT @c

Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).

Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.

Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.

Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.

Использование переменных в запросах SQL

Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще — с помощью переменной.

Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:

DECLARE @IdProd int;

SET @IdProd = 1;

SELECT [Description]

FROM Product

WHERE IdProd = @IdProd;

Глобальные системные переменные

В SQL Server имеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запроса SELECT, как в следующем примере:

SELECT @@CONNECTIONS

Здесь используется глобальная переменная @@CONNECTIONS для извлечения количества подключений к SQL Server со времени запуска программы.

Среди наиболее часто применяемых системных переменных можно отметить следующие:

@@ERROR - Содержит номер ошибки, возникшей при выполнении последнего оператора T-SQL в текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
@@IDENTITY - Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператора INSERT. Если в последнем операторе INSERT не произошла выработка идентификационного значения, системная переменная @@IDENTITY содержит NULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
@@ROWCOUNT - Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператора DELETE с конструкцией WHERE количество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.

! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.

Средства управления потоком команд. Программные конструкции

В языке T-SQL предусмотрена большая часть классических процедурных средств управления ходом выполнения программы, в т.ч. условная конструкция и циклы.

Оператор IF. . . ELSE

Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:

IF Логическое выражение

SQL инструкция I BEGIN Блок SQL инструкций END

[ELSE

SQL инструкция | BEGIN Блок SQL инструкций END]

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

Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.

В приведенном ниже примере условие IF не выполняется, что предотвращает выполнение следующего за ним оператора.

IF 1 = 0

PRINT 'Первая строка'

PRINT 'Вторая строка'

Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.

Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().

Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.

В следующем примере выражение IF EXISTS используется для проверки наличия у клиента с кодом 1 каких-либо заказов перед удалением его из базы. Если по данному клиенту есть информация хотя бы по одному заказу, удаление не производится.

IF EXISTS(SELECT * FROM [Order] WHERE IdCust = 1)

PRINT 'Невозможно удалить клиента поскольку в базе имеются связанные с ним записи'

ELSE

BEGIN

DELETE Customer

WHERE IdCust = 1

PRINT 'Удаление произведено успешно'

END

Операторы WHILE, BREAK и CONTINUE

Оператор WHILE в языке SQL действует во многом так же, как и в других языках, с которыми обычно приходится работать программисту. По сути, в этом операторе до начала каждого прохода по циклу проверяется некоторое условие. Если перед очередным проходом по циклу проверка условия приводит к получению значения TRUE, осуществляется проход по циклу, в противном случае выполнение оператора завершается.

Оператор WHILE имеет следующий синтаксис:

WHILE Логическое выражение

SQL инструкция I

[BEGIN

[BREAK]

Блок SQL инструкций

[CONTINUE]

END]

Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.

Оператор BREAK позволяет немедленно выйти из цикла, не ожидая того, как будет выполнен проход до конца цикла и произойдет повторная проверка условного выражения.

Оператор CONTINUE позволяет прервать отдельную итерацию цикла. Кратко можно описать действие оператора CONTINUE так, что он обеспечивает переход в начало цикла WHILE. Сразу после обнаружения оператора CONTINUE в цикле, независимо от того, где он находится, происходит переход в начало цикла и повторное вычисление условного выражения (а если значение этого выражения больше не равно TRUE, осуществляется выход из цикла).

Следующий короткий сценарий демонстрирует использование оператора WHILE для создания цикла:

DECLARE @Temp int;

SET @Temp = 0;

WHILE @Temp < 3

BEGIN

PRINT @Temp;

SET @Temp = @Temp + 1;

END

Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.

Оператор RETURN

Оператор RETURN используется для останова выполнения пакета, а следовательно, хранимой процедуры и триггера (рассматриваются в следующих лабораторных занятиях).

Еще записи по теме

www.ikasteko.ru

15 советов Как оптимизировать SQL запросы

Порыскав на досуге по тырнету, удивился, что специальных статей-руководств по оптимизации SQL-запросов нет. Перелистав различную информацию и книги, я постараюсь дать некоторое руководство к действию, которое поможет научиться писать правильные запросы.

1. Оптимизация таблиц.

Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины — text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

OPTIMIZE TABLE `table1`, `table2`…

Не стоит забывать, что во время выполнения оптимизации, доступ к таблице блокируется.

2. Перестройка данных в таблице.

После частых изменений в таблице, данная команда может повысить производительность работы с данными. Она перестраивает их в таблице и сортирует по определённому полю.

ALTER TABLE `table1` ORDER BY `id`

3. Тип данных.

Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.

4. NOT NULL и поле по умолчанию.

Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

5. Постоянное соединение с сервером БД.

Позволяет избежать потерь времени на повторное соединение. Однако стоит помнить, что у сервера может быть ограничение на количество соединений, и в том случае, если посещаемость сайта очень высокая, то постоянное соединение может сыграть злую шутку.

6. Разделение данных.

Длинные не ключевые поля советуют выделить в отдельную таблицу в том случае, если по исходной таблице происходит постоянная выборка данных и которая часто изменяется. Данный метод позволит сократить размер изменяемой части таблицы, что приведёт к сокращению поиска информации.

Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть — не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример — счётчик посещений.

Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе — текстовое, а третье числовое — считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже — отсыл к ключевому полю id из первой таблицы.

Теперь постоянные обновления будут происходить во второй таблице. При этом изменять количество посещений лучше не программно, а через запрос:

UPDATE second SET shows=shows+1 WHERE first_id=нужный_ид

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)

Стоит помнить, что это не очень актуально для сайтов с малой посещаемостью и малым количеством информации.

7. Имена полей,

по которым происходит связывание, к примеру, двух таблиц, желательно, чтобы имели одинаковое название. Тогда одновременное получение информации из разных таблиц через один запрос будет происходить быстрее. Например, из предыдущего пункта желательно, чтобы во второй таблице поле имело имя не first_id, а просто id, аналогично первой таблице. Однако при одинаковом имени становится внешне не очень наглядно что, куда и как. Поэтому совет на любителя.

8. Требовать меньше данных.

При возможности избегать запросов типа:

SELECT * FROM `table1`

Запрос не эффективен, так как скорее всего возвращает больше данных, чем необходимо для работы. Вариантом лучше будет конструкция:

SELECT id, name FROM table1 ORDER BY id LIMIT 25

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится "легче"; и производительнее.

Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.

Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.

Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.

Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

9. Ограничить использование DISTINCT.

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

Есть маленькая хитрость. Если необходимо просмотреть две таблицы на тему соответствия, то приведённая команда остановится сразу же, как только будет найдено первое соответствие.

SELECT DISTINCT table1.content FROM table1, table2 WHERE table1.content = table2.content

10. Ограничить использование SELECT для постоянно изменяющихся таблиц.

11. Не забывайте про временные таблицы типа HEAP.

Несмотря на то, что таблица имеет ограничения, в ней удобно хранить промежуточные данные, особенно когда требуется сделать

ergoz.ru

Передача параметров в динамический запрос в T-SQL / Хабр

Я не раз сталкивался с необходимостью построения динамического запроса и здесь есть ряд подводных камней о которых я расскажу ниже. Пример динамического запроса:declare @sql varchar(100) = 'select 1+1' execute( @sql) 1. Запуск строки через Execute создает отдельный блок кода, в котором текущие переменные будут не видны, но видны все временные таблицы.

2. Обратите внимание на передачу переменных со значением NULL. Любое слияние с NULL в результате даст NULL, следовательно, вместо запроса, вы можете получить пустую строку.

declare @i int declare @sql varchar(100) = 'select ' + cstr(@i) execute( @sql ) -- Ошибка 3. Передачу дат и времени. Даты лучше передавать в формате ГГГГММДД. При передаче параметров со временем следует обратить внимание на потерю точности. Для сохранения точности значения лучше передавать через временную таблицу. 4. Передача параметров с плавающей десятичной точкой имеет те же проблемы, что и передача времени внутрь построенного запроса.

5. Строковые значения – потенциально опасный код. Для начала внутри строки все одинарные кавычки должны быть продублированы. Сама строка заключена в одинарные кавычки.

Пример ошибочного кода:

Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + @str + '''', 'null' ) Execute( @sql ) -- запуск кода выдаст ошибку Правильный код:Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + replace( @str, '''', '''''') + '''', 'null' ) Execute( @sql ) 6. Подстановка списков в секцию IN. Основная опасность – пустой список. В этом случае секция будет иметь вид типа ‘поле IN ()’, что при компиляции вызовет ошибку. Как метод борьбы: в начало списка всегда включать NULL или заменить пустую строку на NULL. NULL можно сравнивать с любым типом данных. Сравнение с NULL всегда дает отрицательный результат, но при этом список гарантированно не пустой.Declare @list varchar(100) = '' iif @list = '' set @list = 'null' Declare @sql varchar(1000) = 'select number from documents where id in ('+@list+') ' Execute( @sql ) Вот пример безопасной передачи сложных параметров через временную таблицу:if OBJECT_ID('tempdb..#params') is not null drop table #params create table #params ( v1 int, v2 datetime, v3 varchar(100) ) insert #params values ( 1, getdate(), 'Строка ''1''') declare @sql varchar(1000) = ' declare @v1 int, @v2 datetime, @v3 varchar(100) select @v1 = v1 , @v2 = v2, @v3 = v3 from #params select @v1, @v2, @v3 ' execute(@sql) drop table #params Ну и на закуску маленькие хитрости:

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

Если обходится без переменных, то можно использовать следующий метод:

set @sql = 'select <VAR1> + <VAR2>' set @sql = replace(@sql, '<VAR1>', '1') set @sql = replace(@sql, '<VAR2>', '2') execute( @sql )

Кроме вышеперечисленных особенностей есть еще пару способов передачи параметров: 1. Использовать sp_executesql (как правильно мне подсказали в комментариях) 2. Обернуть запрос во временную хранимую процедуру и запускать ее. При большом количестве запусков этот способ даже эффективнее.

declare @sql varchar(200) = ' create procedure #test ( @p1 int, @p2 int) as select @p1 + @p2' execute( @sql ) exec #test 1, 2 exec #test 3,4 drop procedure #test

habr.com