Файл: Web'cepbep.docx

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

Если SQL-запрос выполняется не очень быстро, но очень редко, то можно не силь­но обращать внимания на его оптимизацию. Да, это утверждение верно, но только не для web-серверов, где производительность имеет критическое значение всегда.

Современные СУБД могут поддерживать вложенные SQL-запросы. В некоторых случаях программисты начинают ими злоупотреблять. При написании SQL- запросов старайтесь использовать минимальное количество операторов where, осо­бенно вложенных в секцию select.

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

Для повышения производительности иногда хорошо помогает вынос лишнего select в секцию from. Но иногда бывает и наоборот — быстрее будет выполняться SQL-запрос, в котором select вынесен из from в тело where. Это уже зависит от оп­тимизатора конкретной СУБД и конкретного случая. Но в целом лучше делать больше упор на join.

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

SELECT *

FROM tbPerson p WHERE p.idStatus=

(SELECT idStatus FROM tbStatus WHERE Nam.e='Работает')

Вам необязательно полностью понимать суть. Главное здесь в том, что в секции where выполняется вложенный SQL-запрос. Он будет генерироваться для каждой строки в таблице tbPerson, что может оказаться накладным (получается цикл, а цикл — враг производительности).

---

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

Если есть СУБД, которая не умеет работать с вложениями, то это приводит к необ­ходимости написания двух SQL-запросов. Первый будет получать статус:

SELECT idStatus FROM tbStatus WHERE Name='Работает'

А второй будет использовать его для выборки работников:

SELECT *

FROM tbPerson p

WHERE p.idStatus=Полученный Статус

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

Теперь посмотрим, как можно вынести select в секцию from. Это можно сделать так:

SELECT *

FROM tbPerson p,

(SELECT [idStatus] FROM tbStatus WHERE Name='Работает') s WHERE p.idStatus=s.idStatus

В этом случае будет выполнен SQL-запрос из секции from. А во время связывания результата с таблицей работников мы получим окончательный результат. Таким обра­зом, вложение не будет выполняться для каждой строки и, соответственно, не будет цикла. Но этот способ работает только для простых вариантов.

Представленные примеры слишком просты и могут выполняться за одно и то же время с точностью до секунды благодаря оптимизатору. Но при использовании бо­лее разветвленной структуры или сложного SQL-запроса можно сравнить время выполнения и выбрать наиболее предпочтительный вариант для определенной СУБД (напоминаю, что разные СУБД могут обрабатывать SQL-запросы по-разному).

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

SELECT *

FROM tbPerson p, tbStatus s WHERE p.idStatus=s.idStatus AND s.Name='Работает'

Или если использовать JOIN-подход, этот запрос можно написать так:

SELECT *

FROM tbPerson p

JOIN tbStatus s on p.idStatus=s.idStatus WHERE s.Name='Работает'

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

Есть мнение, что в таком запросе проверку на имя статуса также лучше перенести в

JOIN:

SELECT *

FROM tbPerson p

JOIN tbStatus s on p.idStatus=s.idStatus AND s.Name='Работает'

Лично я ни разу не видел, чтобы такой трюк повлиял на производительность, пото­му что оптимизатор запросов и без этого изменения видит, что нужно найти только те статусы, которые равны 'Работает'. Возможно, бывают случаи, когда такой под­ход быстрее, но я с таким не сталкивался. Из личного опыта могу сказать, что сле­дующий запрос с большой вероятностью выполнится абсолютно так же и за то же время:

---

SELECT *

FROM tbPerson p

JOIN tbStatus s on 1=1

WHERE p.idStatus=s.idStatus AND s.Name='Работает'

Здесь все связи указаны в разделе where, и на выполнение со стороны сервера это не влияет. Просто с точки зрения чистоты кода и простоты чтения так лучше не писать. Все связи должны быть после join on, а все фильтры должны быть в where: SELECT *

FROM tbPerson p

JOIN tbStatus s on p.idStatus=s.idStatus WHERE s.Name='Работает'

Частая проблема производительности запросов — табличные переменные.

— создаем временную таблицу из 5 случайных строк таблицы member declare @memberids table (id int); insert into @memberids

select top 5 MemberlD from Member order by newid()

— используем временную таблицу select * from Member m

join @memberids ids on m.MemberID = ids.id

В этом примере используется два SQL-запроса. В первом мы выбираем случайные 5 записей из таблицы Member и помещаем их в табличную переменную memberids. Второй запрос использует эту переменную, чтобы найти записи.

В моей тестовой системе этот запрос выполнялся 1,690 миллисекунд:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1690 ms.

Это очень долго для такого запроса, но проблема в том, что у меня тестовая база данных расположена на простой SD-карточке, которая не отличается высокой скоростью, и база данных относительно большая. Это сделано специально, чтобы тестировать запросы на производительность. К тому же у меня на ноутбуке всего 8 Гбайт оперативной памяти, хотя SQL Server для своей работы требует минимум 4 Гбайта, что можно с трудом выделить при наличии всего 8.

Тот же самый результат можно получить выполнением одного select без таблич­ных переменных:

select * from Member m

join (select top 5 MemberID as id from member order by newid()

) ids on m.MemberID = ids.id Этот запрос выполнялся у меня почти секунду:

SQL Server Execution Times:

CPU time = 219 ms, elapsed time = 886 ms.

Разница не сильно большая, но все же есть, запрос без табличной переменной вы­полняется быстрее почти в два раза.

А что, если мы поменяем количество случайных записей с 5 до 100. Производи­тельность первого варианта с табличной переменной упадет до 7 секунд, а вот вто­рой вариант у меня выполнился за 3 секунды.

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

---

select, но и в том, что ис­пользуется переменная с таблицей, которую сервер SQL может не оптимизировать.

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

Не имеет значения, как создается табличная переменная — в SQL-запросе, как это сделано в моем примере, или если эта переменная передается запросу из кода.

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

declare @memberids table (id int); insert into @memberids

select top 100 MemberlD from Member order by newid()

у меня уходит на это 130 миллисекунд.

Следующая потенциальная проблема SQL — приведение типов. Допустим, вы вы­полняете следующий запрос:

SELECT *

FROM Member m

WHERE m.Memberid = '123132'

Здесь я пытаюсь найти запись Member, где значение MemberId — строка с числом. Если поле MemberId является числом, а вы ищите по строке, то такой SQL выпол­нится корректно, но его производительность может быть невысокой. Сервер может отказаться от использования индекса и начать бежать по всем записям в таблице Member, конвертировать MemberID из числа в строку и только после этого произво­дить сравнение.

То же самое может произойти и если вы производите поиск по строке, но смеши­ваете Unicode и ANSI-строки:

SELECT *

FROM Member m

WHERE m.FirstName = N'Misha'

Если в таблице FirstName является просто однобайтовой строкой, то этот запрос может снова выполняться медленно, потому что мы сравниваем с Unicode-версией строки. Такое снова может привести к отказу от индекса и использованию конвер­тации для каждой строки в базе данных.

2. 
   Оптимизация базы данных
   

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

В некоторых СУБД, если не указать размер поля для хранения строки, он принимает максимально возможное значение или 255.

Это непростительное расточительство дискового пространства, если использовать тип данных ^ar, и создаваемая база данных становится неоправданно большой. А чем она больше, тем сложнее ее обработать, больше нужно читать с диска и требу­ется больше оперативной памяти для эффективного кеширования.

---

Если же уменьшить размер, то СУБД сможет максимально быстро загрузить данные в память и произвести поиск без обращения к жесткому диску. Если база данных не помещается в памяти, то приходится загружать ее по частям, а в худшем случае — использовать файл подкачки, который находится на диске и работает в несколько раз медленнее оперативной памяти.

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

Итак, чтобы ваша база данных была минимальной, вы должны использовать только необходимый размер полей. Например, для хранения номера телефона достаточно 10 символов char, и не надо использовать для этого 50. Для таблицы с 100 000 запи­сей это будут лишние 4 Мбайта информации. А если полей с завышенным разме­ром 10? В этом случае расход становится слишком большим. Если поле должно иметь размер более 100 символов, подумайте о том, чтобы использовать тип text или memo. Некоторым базам данных это действительно может помочь, потому что значения таких полей хранятся на отдельных страницах.

Можно использовать везде тип данных varchar, который более эффективно исполь­зует дисковое пространство, и в этом есть свой смысл.

В MS SQL Server для оптимизации можно попытаться использовать сжатие дан­ных. В случае с простым жестким диском это может дать результат, потому что именно диск может быть самым слабым местом системы. В случае с SSD это уже не так заметно, но тоже можно добиться определенного результата.

Итак, если использовать сжатие данных при хранении, то при обращении к жест­кому диску придется читать меньше данных, в некоторых случаях в несколько раз меньше данных, потому что базы обычно очень хорошо поддаются сжатию, если содержат повторяющиеся данные. За один раз можно прочитать больше данных и потом распаковать их на быстром процессоре.

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

---