Оптимизация запросов SQL и метаданных
Оптимизация запросов SQL и метаданных
Продолжение. Начало КГ №№ 51'2003, 9'2004
Введение. В то время как практически каждый разработчик может создавать запросы SQL, возможностью создавать максимально оптимизированные запросы, которые отличают действительно быстро работающее приложение от его собрата, показывающего приемлемую производительность, обладает далеко не каждый. В статье рассказывается, как можно оптимизировать работу приложений с сервером InterBase со стороны SQL, оптимизация и тонкая настройка самого сервера будет описана немного позже, в следующей статье.
Основы оптимизации. Тот факт, что сервер InterBase может принимать выражения PLAN в сценариях Dynamic SQL (Динамического SQL), часто вводит в заблуждение разработчиков, которые пытаются их использовать. Даже если не брать в расчет, что понимание плана исполнения запроса достаточно сложно, не говоря уже о его написании вручную, прямое использование планов является плохим путем оптимизации запросов.
Чтобы помочь оптимизатору в выполнении запросов, можно использовать несколько стратегий. Первая: полностью или частично переписать SQL по подозрению в том, что он не оптимально сформирован. Вторая: внести соответствующие изменения в структуру объектов базы данных (метаданные), что, правда, не всегда возможно, особенно на поздних этапах разработки проекта. И третья: провести обслуживание базы данных (если необходимо — процедуры архивации и восстановления), дабы исключить вероятность замедления работы запроса из-за накопления старых версий записей.
Рассмотрению подвергнется каждая из стратегий. Но первым шагом должно стать определение, почему запрос выполняется слишком долго. Отталкиваясь от этого, можно будет определить, какая из трех стратегий изменит положение вещей в лучшую сторону.
Диагностика. Для того чтобы начать работу по оптимизации, необходимо следующее. База данных с информацией, как можно ближе соответствующей той, что будет в ней содержаться в процессе работы. Текст запроса, который корректно выполняется (может, недостаточно быстро). Удаленный сетевой сервер (чтобы учесть затраты на передачу данных по сети, ведь большинство систем именно так и работают).
Замечание. Чтобы не прибегать к базам данных из стандартных примеров, так как они имеют небольшой размер и не могут в полной мере отразить изменения в скорости выполнения запросов, особенно при локальном подключении, когда сервер и клиент расположены на одной машине.
Время исполнения запросов. Когда запрос запускается на выполнение, происходят сразу несколько событий: текст SQL преобразуется в код BLR (что это такое — чуть ниже). Это происходит тогда, когда вы подготавливаете к исполнению ("prepare") сценарий DSQL или в хранимой процедуре или представлении, когда процедура или представление создается. Затем запрос оптимизируется. Этот шаг также всегда присутствует, когда запрос проходит подготовку к исполнению. После этого запрос выполняется — информация считывается с диска, и сервер производит над ней необходимые действия. Запись передается на сторону клиента по запросу самого клиента. СУБД InterBase/Firebird не возвращают набора данных до тех пор, пока клиент не затребует их. И последнее событие — когда запрос выполняется в контексте клиентского приложения, могут активизироваться другие действия обработчиками событий в клиентском приложении.
Преобразование и оптимизация, каким бы странным это не показалось, не занимают много времени, тем более что необходимость в этом возникает только при подготовке запроса, совсем не при каждом его исполнении. Поэтому первое, что требуется сделать, так это проверить работу запроса вне клиентского приложения. Если при этом проблема разрешилась, значит, к замедлению работы сервер не имеет никакого отношения и проблема лежит внутри кода самого приложения. Конкретизировать, где именно в коде содержится "медленное место", поможет любое из приложений, обладающее способностью мониторинга SQL — запросов, от IBConsole и до программы IBExpert.
Замечание. Обычно учитывается, что подготовка запроса занимает некоторое время, из которого часть приходится на работу сервера InterBase/ Firebird, остальное отнимают компоненты доступа к данным, которые могут во время подготовки запроса запрашивать информацию о метаданных.
Когда вы выполняете запрос вне своего приложения, помните, что большинство средств администрирования не делают выборки всех записей, а выбирают объем информации, достаточный для заполнения одного пользовательского экрана. Дополнительные записи для отображения передаются при навигации пользователем по набору данных. Так что, если вы используете в своей программе метод FetchAll, время выполнения запроса в программе может очень сильно отличаться от полученного в программе администрирования. Метод FetchAll иногда необходим в программах (например, в многозвенных приложениях), иногда нет, так что если из-за него наблюдаются замедления в работе, следует либо избегать использовать этот метод, либо постараться и минимизировать получаемый набор данных.
Когда запрос выполняется так же медленно вне контекста вашего приложения, проблема состоит или в скорости выполнения самого запроса, или в скорости передачи полученных данных. Точно установить причину поможет, например, такой инструмент, как InterBase PLANalyzer — при прогоне запроса он выдаст в цифровом отображении всю статистику по выполнению запроса. Тем не менее, крайне важно понимать, что общее время выполнения запроса складывается из времени его выполнения и времени передачи данных конечному пользователю. Например, сортировка результатов запроса по индексу уменьшает время выполнения запроса, но увеличивает время его передачи; отказ от использования индекса имеет прямо противоположный эффект. Если проводить сравнение обоих результатов, то получается, что, как правило, эффективнее использовать сортировку без индекса. Поэтому, чтобы точно определить причину медленного выполнения запроса, разработчику необходимо знать и время выполнения самого запроса, и время его передачи клиенту.
Большие наборы данных. Если время передачи данных очень велико, проверьте, какое количество данных возвращает запрос. В этом случае необходимо определиться, действительно ли приложению для работы необходимо столько записей? Если это не критично, следует ограничить объем возвращаемой клиенту информации на уровне запроса с применением WHERE или JOIN. Вышеописанный метод также помогает решить проблемы передачи данных в медленных сетях.
Количество записей. Количество количеству — рознь. Когда запрос возвращает относительно немного записей, стоит озаботиться тем, сколько записей приходится считать серверу Firebird/InterBase для получения требуемого результата. К примеру, если по полю, указанному в секции WHERE текста запроса нет индекса, серверу придется перебрать все записи в таблице, чтобы определить, попадает ли запись под условие WHERE.
Чтобы получить информацию о том, сколько записей сервер считал для выполнения запроса, можно воспользоваться статистикой, доступной во всех ведущих средах администрирования серверов InterBase/ Firebird: EMS IB Manager, IBEx-pert, PLANalyzer. Самым оптимальным случаем является тот, когда количество считанных записей практически равно количеству записей возвращенных. В запросе, который является объединением двух таблиц по INNER JOIN, серверу для получения одной записи требуется считать как минимум одну запись из одной таблицы и одну запись из другой таблицы. Таким образом, количество считанных записей должно быть примерно в два раза больше, чем записей возвращенных.
Если вы определили, что запрос считывает значительно больше записей, чем необходимо для результирующего набора данных, тогда производительность можно улучшить созданием индекса по используемым в запросе полям. Если индекс уже создан, но сервер его не использует, следует рассмотреть причины, по которым сервер не использует его. Если индекс или несколько индексов используются, но количество считываемых записей остается большим, надо улучшить такое свойство индекса (или индексов), как селективность.
Стратегии передачи данных. Если количество записей верно, но передача данных происходит очень медленно, возможно, что использование индекса замедляет передачу результатов выборки. Следует обратиться к статистике чтения записей из таблиц, а также сортировки, и рассмотреть эффективность выборки индекса, она может быть ниже, чем даже показывают средства оценки эффективности выполнения запросов. Индекс может снизить время выполнения только в том случае, если он уменьшает количество считанных записей.
Пропускная способность сети. Сеть с низкой пропускной способностью или слабое телефонное соединение может иногда снизить скорость работы приложения. Но даже пропускная способность сети в 100Mbps считается недостаточной и является одним из "бутылочных горлышек". Вот почему основой хорошего проектирования клиент — серверных приложений считается снижение объема данных, пересылаемых от сервера баз данных клиентскому приложению. Используйте секцию WHERE, избегайте запросов вида SELECT * в случаях, когда клиенту наличие всех колонок без надобности.
Когда же сделано все возможное, а приложение все еще работает медленно, то есть присутствует замедление передачи данных по сети, попробуйте следующее. Попытайтесь использовать компрессию данных, спроектируйте промежуточный сервер приложений или прокси-сервер, или используйте специфические компоненты для сжатия данных при их передаче по сети. Попробуйте кэшировать таблицы, которые редко или практически не изменяются на клиентской стороне (так называемая модель briefcase).
Продолжение следует.
Денис "Denver" Мигачев dtm@tut.by
Продолжение. Начало КГ №№ 51'2003, 9'2004
Введение. В то время как практически каждый разработчик может создавать запросы SQL, возможностью создавать максимально оптимизированные запросы, которые отличают действительно быстро работающее приложение от его собрата, показывающего приемлемую производительность, обладает далеко не каждый. В статье рассказывается, как можно оптимизировать работу приложений с сервером InterBase со стороны SQL, оптимизация и тонкая настройка самого сервера будет описана немного позже, в следующей статье.
Основы оптимизации. Тот факт, что сервер InterBase может принимать выражения PLAN в сценариях Dynamic SQL (Динамического SQL), часто вводит в заблуждение разработчиков, которые пытаются их использовать. Даже если не брать в расчет, что понимание плана исполнения запроса достаточно сложно, не говоря уже о его написании вручную, прямое использование планов является плохим путем оптимизации запросов.
Чтобы помочь оптимизатору в выполнении запросов, можно использовать несколько стратегий. Первая: полностью или частично переписать SQL по подозрению в том, что он не оптимально сформирован. Вторая: внести соответствующие изменения в структуру объектов базы данных (метаданные), что, правда, не всегда возможно, особенно на поздних этапах разработки проекта. И третья: провести обслуживание базы данных (если необходимо — процедуры архивации и восстановления), дабы исключить вероятность замедления работы запроса из-за накопления старых версий записей.
Рассмотрению подвергнется каждая из стратегий. Но первым шагом должно стать определение, почему запрос выполняется слишком долго. Отталкиваясь от этого, можно будет определить, какая из трех стратегий изменит положение вещей в лучшую сторону.
Диагностика. Для того чтобы начать работу по оптимизации, необходимо следующее. База данных с информацией, как можно ближе соответствующей той, что будет в ней содержаться в процессе работы. Текст запроса, который корректно выполняется (может, недостаточно быстро). Удаленный сетевой сервер (чтобы учесть затраты на передачу данных по сети, ведь большинство систем именно так и работают).
Замечание. Чтобы не прибегать к базам данных из стандартных примеров, так как они имеют небольшой размер и не могут в полной мере отразить изменения в скорости выполнения запросов, особенно при локальном подключении, когда сервер и клиент расположены на одной машине.
Время исполнения запросов. Когда запрос запускается на выполнение, происходят сразу несколько событий: текст SQL преобразуется в код BLR (что это такое — чуть ниже). Это происходит тогда, когда вы подготавливаете к исполнению ("prepare") сценарий DSQL или в хранимой процедуре или представлении, когда процедура или представление создается. Затем запрос оптимизируется. Этот шаг также всегда присутствует, когда запрос проходит подготовку к исполнению. После этого запрос выполняется — информация считывается с диска, и сервер производит над ней необходимые действия. Запись передается на сторону клиента по запросу самого клиента. СУБД InterBase/Firebird не возвращают набора данных до тех пор, пока клиент не затребует их. И последнее событие — когда запрос выполняется в контексте клиентского приложения, могут активизироваться другие действия обработчиками событий в клиентском приложении.
Преобразование и оптимизация, каким бы странным это не показалось, не занимают много времени, тем более что необходимость в этом возникает только при подготовке запроса, совсем не при каждом его исполнении. Поэтому первое, что требуется сделать, так это проверить работу запроса вне клиентского приложения. Если при этом проблема разрешилась, значит, к замедлению работы сервер не имеет никакого отношения и проблема лежит внутри кода самого приложения. Конкретизировать, где именно в коде содержится "медленное место", поможет любое из приложений, обладающее способностью мониторинга SQL — запросов, от IBConsole и до программы IBExpert.
Замечание. Обычно учитывается, что подготовка запроса занимает некоторое время, из которого часть приходится на работу сервера InterBase/ Firebird, остальное отнимают компоненты доступа к данным, которые могут во время подготовки запроса запрашивать информацию о метаданных.
Когда вы выполняете запрос вне своего приложения, помните, что большинство средств администрирования не делают выборки всех записей, а выбирают объем информации, достаточный для заполнения одного пользовательского экрана. Дополнительные записи для отображения передаются при навигации пользователем по набору данных. Так что, если вы используете в своей программе метод FetchAll, время выполнения запроса в программе может очень сильно отличаться от полученного в программе администрирования. Метод FetchAll иногда необходим в программах (например, в многозвенных приложениях), иногда нет, так что если из-за него наблюдаются замедления в работе, следует либо избегать использовать этот метод, либо постараться и минимизировать получаемый набор данных.
Когда запрос выполняется так же медленно вне контекста вашего приложения, проблема состоит или в скорости выполнения самого запроса, или в скорости передачи полученных данных. Точно установить причину поможет, например, такой инструмент, как InterBase PLANalyzer — при прогоне запроса он выдаст в цифровом отображении всю статистику по выполнению запроса. Тем не менее, крайне важно понимать, что общее время выполнения запроса складывается из времени его выполнения и времени передачи данных конечному пользователю. Например, сортировка результатов запроса по индексу уменьшает время выполнения запроса, но увеличивает время его передачи; отказ от использования индекса имеет прямо противоположный эффект. Если проводить сравнение обоих результатов, то получается, что, как правило, эффективнее использовать сортировку без индекса. Поэтому, чтобы точно определить причину медленного выполнения запроса, разработчику необходимо знать и время выполнения самого запроса, и время его передачи клиенту.
Большие наборы данных. Если время передачи данных очень велико, проверьте, какое количество данных возвращает запрос. В этом случае необходимо определиться, действительно ли приложению для работы необходимо столько записей? Если это не критично, следует ограничить объем возвращаемой клиенту информации на уровне запроса с применением WHERE или JOIN. Вышеописанный метод также помогает решить проблемы передачи данных в медленных сетях.
Количество записей. Количество количеству — рознь. Когда запрос возвращает относительно немного записей, стоит озаботиться тем, сколько записей приходится считать серверу Firebird/InterBase для получения требуемого результата. К примеру, если по полю, указанному в секции WHERE текста запроса нет индекса, серверу придется перебрать все записи в таблице, чтобы определить, попадает ли запись под условие WHERE.
Чтобы получить информацию о том, сколько записей сервер считал для выполнения запроса, можно воспользоваться статистикой, доступной во всех ведущих средах администрирования серверов InterBase/ Firebird: EMS IB Manager, IBEx-pert, PLANalyzer. Самым оптимальным случаем является тот, когда количество считанных записей практически равно количеству записей возвращенных. В запросе, который является объединением двух таблиц по INNER JOIN, серверу для получения одной записи требуется считать как минимум одну запись из одной таблицы и одну запись из другой таблицы. Таким образом, количество считанных записей должно быть примерно в два раза больше, чем записей возвращенных.
Если вы определили, что запрос считывает значительно больше записей, чем необходимо для результирующего набора данных, тогда производительность можно улучшить созданием индекса по используемым в запросе полям. Если индекс уже создан, но сервер его не использует, следует рассмотреть причины, по которым сервер не использует его. Если индекс или несколько индексов используются, но количество считываемых записей остается большим, надо улучшить такое свойство индекса (или индексов), как селективность.
Стратегии передачи данных. Если количество записей верно, но передача данных происходит очень медленно, возможно, что использование индекса замедляет передачу результатов выборки. Следует обратиться к статистике чтения записей из таблиц, а также сортировки, и рассмотреть эффективность выборки индекса, она может быть ниже, чем даже показывают средства оценки эффективности выполнения запросов. Индекс может снизить время выполнения только в том случае, если он уменьшает количество считанных записей.
Пропускная способность сети. Сеть с низкой пропускной способностью или слабое телефонное соединение может иногда снизить скорость работы приложения. Но даже пропускная способность сети в 100Mbps считается недостаточной и является одним из "бутылочных горлышек". Вот почему основой хорошего проектирования клиент — серверных приложений считается снижение объема данных, пересылаемых от сервера баз данных клиентскому приложению. Используйте секцию WHERE, избегайте запросов вида SELECT * в случаях, когда клиенту наличие всех колонок без надобности.
Когда же сделано все возможное, а приложение все еще работает медленно, то есть присутствует замедление передачи данных по сети, попробуйте следующее. Попытайтесь использовать компрессию данных, спроектируйте промежуточный сервер приложений или прокси-сервер, или используйте специфические компоненты для сжатия данных при их передаче по сети. Попробуйте кэшировать таблицы, которые редко или практически не изменяются на клиентской стороне (так называемая модель briefcase).
Продолжение следует.
Денис "Denver" Мигачев dtm@tut.by
Компьютерная газета. Статья была опубликована в номере 11 за 2004 год в рубрике программирование :: разное
