Переходим на клиент-сервер (советы), на примере MS SQL Server
Эта статья адресована прежде всего тем, кто работал с локальными базами и данных и теперь переходит на использование сервеных баз данных. Будет полезна так же и тем кто решил осваивать работу с базами данных сразу с серверных баз.
А собственно в чём проблема?
На первый взгляд подключаем сервер вместо того же парадокса и почти всё работает – так же можно бросить грид и посмотреть таблицу, так же можно выполнить селект. В чём же принципиальное отличие? Может не надо морочить голову и поменяв в BDE параметры Alias оставить всё как есть? Или просто заменить BDE компоненты на ADO? Ответ таков – что да, в простейшем случае всё работает, но не очень быстро даже при монопольном доступе и малых размерах таблиц, при увеличении размеров таблиц и подключении нескольких пользователей система начинает умирать, простые запросы выполнятся минутами, а то и часами… Вот тут-то и приходим к тому что простейшей заменой способа доступа к базе данных обойтись не получится.
Вот существенные моменты о которых надо помнить при разработке даже простейших систем клиент сервер, это ЧРЕЗВЫЧАЙНО важные моменты, их надо всегда учитывать!
1. Любые действия с базой данных происходят через сеть, поэтому программируя любые операции над базой данных програмист должен задать себе вопрос: а какой собственно сетевой трафик я создам этими действиями? Сколько мегабайт должно будет пройти через сетку в результате моих действий?
2. Приложение не работает с таблицами монопольно, другие клиенты тоже что-то делают с базой данных. Не блокируют ли мои действия другие программы? Если блокируют то как надолго? А действительно мне надо чтобы мои действия блокировали таблицы других клиентов?
3. Как именно будет мой запрос выполнятся сервером? Используйте индексы и ключи! Учтите что в отличие от локальных баз данных при отсутствии индекса SQL запрос будет выполнятся точно так же как и с индексом, никаких ошибок не будет, даже быстродействие на первых порах может не уменьшится, только потребление ресурсов процессора на сервере возрастёт – проблемы появятся позже… Всегда надо смотреть что именно сервер будет делать при запросе.
Помните что если вы не учли какой-то из этих моментов НИКАКИХ ошибок компиллятор вам не выдаст, а тестовое приложение на тестовых небольших базах данных в едином экземпляре будет работать великолепно, проблемы возникнут позже, когда програму начнут реально использовать.
Доступ к базам данных и производительность.
Если вы переходите на на клиент-сервер вам прийдётся отказаться от BDE. BDE – очень хорошая система для локальных баз данных и показывает отличную производительность на локальных таблицах при монопольном доступе. BDE вполне можно подключить чуть ли не к любым серверам баз данных, однако изначально разрабатывавшаяся с рассчётом на локальные базы данных BDE не даст вам хорошей производительности даже на небольших базах данных, при работе с большими базами данных и при большом количестве одновременных подключений BDE начинает работать исключительно медленно. Кроме того, Local SQL – диалект SQL для BDE довольно беден и многие сервера баз данных поддерживают несравненно более сложные и мощные диалекты SQL. ODBC – родной брат BDE в реализации MS – не блистал скоростью даже с локальными базами данных, не стоит его использовать и для на клиент-сервера. DAO – изначально был рассчитан на локальные базы от MS и с серверами не работает даже теоретически. Итого – из глобальных систем доступа к базам данных мы имеем ADO (OLE DB) и DBExpress. Я не специалист в DBExpress и не могу много сказать, но знакомство с документацией и отзывами пользователей привносит впечатление о достаточно слабой реализации и ограниченных возможностях. В то же время ADO в настоящее время пожалуй является одной из наиболее развитых и продвинутых систем. Кроме того не надо забывать что почти все производители серверов поставляют свои драйверы для доступа к базам данных.
Хм… Вы тут понаписывали, а я вот создал таблицу в парадоксе, подключился через BDE запустил Select, а потом сделал такую же таблицу в MS SQL Server, подключился через ADO запустил такой же Select – и получил что производительность парадокса на 2 порядка выше! В чём же прикол и на фига мне нужен тогда этот сервер и ADO?
Угу! Это действительно так! Но только в случае небольших локальных таблиц и при однопользовательском доступе! Если вы положите табличку в сетевую папку, сделаете её размером в сотню-другую тысяч записей, будете премежать Select с Update, и запустите десяток приложений с разных компьютеров которые будут это проделывать с этой табличкой одновременно, то получите картину производительности такую, что MS SQL Server + ADO будет работать почти с той же скоростью что и при монопольном доступе, а вот производительность парадокса при таком раскладе упадёт практически до нуля.
А может ну их эти драйвера! Может проще подключится как нибудь напрямую, через какие-нибудь API или вообще через сокеты? Зачем эти потери времени, дополнительные слой програм? Может я лучше всё сам и вручную сделаю лучше?
Должен разочаровать – совершенно бессмысленное занятие. Обычно никаких таких специальных API и не существует. Для подавляющего большинства баз данных единственным средством общения с внешним миром является SQL. И всё что делает драйвер – это передаёт этот SQL запрос от вашего приложения серверу, сервер внутри себя этот запрос компиллирует и выполняет, генерируя поток данных который передаётся клиенту. Обычно драйвера выполняют ещё и довольно сильное кэширование потока данных на клиенте и асинхронную передачу данных по запросу, что обеспечивает довольно значительное увеличение производительности, поэтому даже исхитрившись написать свой собственый драйвер к серверу вы вряд ли сможете достичь производительности коммерческих продуктов, во всяком случае это достаточно сложная и трудоёмкая задача.
Итак ADO. Что мне использовать?
Легче сказать что не использовать: категорически не рекомендуется использовать компонент TADOTable. Вся идеология клиент-сервера базируется на SQL запросах. В идеале НЕТ НИКАКИХ других способов взаимодействия с сервером кроме SQL запроса, поэтому TADOTable будет лишь эмулировать поведение TADOQuery, а в реале это приведёт к очень неоптимальным запросам и резкому падению производительности. Везде где это можно используйте SQL запросы.
Сетевой трафик
Вернёмся к сетевому трафику. Вот чего не следует делать, так это посылать запросы типа
Select * From MyTable
Перед тем как послать такой запрос обязательно подумайте во-первых – в данный конкретный момент а все ли записи вам нужны? Ограничте их количество либо условием выборки:
Select * From MyTable Where MyField='что-то там'
Либо указанием директивно взять не более чем несколько записей :
Select top 10 * From MyTable
Синтаксис данного запроса не является стандартом SQL, это будет работать для MS SQL Server, для других серверов смотрите их документацию.
Во-вторых – а все ли поля вам нужны? Даже если вам нужно 100 полей из 200 и их перечисление в запросе превратит этот запрос из запроса в одной строчке в запрос размером с килобайт он будет выполнятся существенно быстрее! Всегда ограничивайте запрос только теми полями которые вам непосредственно нужны в данный момент!
А если мне всё-таки в программе нужны все 1000000 записей таблицы?
Сложный вопрос… Есть несколько вариантов решения:
1) Подумать как изменить архитектуру, чтобы не надо было н клиент тащить столько информации – это самый лучший выход
2) Брать информацию по кускам, разбить запрос на несколько мелких.
3) Снять блокировки устанавливаемые при взятии данных (об этом позже)
4) Перенести логику на сервер, написать хранимую процедуру которая вместо вашего приложения будет работать с выборкой непосредственно на сервере
Блокировки.
Пожалуй один из наиболее болезненных вопросов. Всегда надо помнить, что пока ваша программа что-то делает с данными, кто-то другой тоже с ними чего-то делает. В 95% случаев ваши действия не должны мешать другим, а другие вам. Рассмотрим ситуацию – у вас на сервере таблица содержащая данные о погоде по стране. Вам надо узнать погоду в Москве – и вы запрашиваете эти данные. А программа вашего коллеги – Васи в это время обновляет данные о погоде, которые получает от гидрометеоцентра. Вопрос: должны ли Васины действия мешать Вам? Очевидно нет! Вам всё равно что делает Вася – вам нужна погода, если она ещё не обновлена, то Вас устроят данные которые вносились час назад, а если обновлена – то свежие. Но Вася по своей наивности пошёл простым путём – открыл таблицу эксклюзивно и меняет данные – ваш запрос не проходит! Итого – всегда надо думать нужно ли блокировать и если нужно то что именно. Иногда блокировки нужны, для обеспечения целостности данных. Например проходит платёж и он должен пройти по всем таблицам и всем ведомостям, причём ГАРАНТИРОВАННО по всем, если в какой-то таблице данные не обновились то платёж не прошёл и изменения надо отменить, кроме того пока проходит платёж нельзя чтобы кто-то тоже изменял данные, и даже читал данные – это может привести к тому что данные будут неправильными в следствии того что в каких-то таблицах данные уже изменены а в каких то нет. Вот тут то и нужна жестокая блокировка… но надо учесть, что на момент блокировки остальные клиенты будут ждать её окончания, следовательно Вам нельзя заблокировать таблицу и начать выспрашивать пользователя какие-то данные, а пользователь уморившись решит вообще перекурить, а то и забудет комп и пойдёт домой и останутся все клиенты без данных на очень долго. Следовательно, надо к началу блокировки иметь все нужные данные, а начав запросы которые блокируют таблицу, надо их завершить в самые кратчайшие сроки. Код в программе который выполняется во время блокировки должен быть минимальный, никаких дополнительных действий и длительных циклов, тем более никаких опросов операторов и чтения чего-то из сети. Теперь допустим Вася получил погоду в виде таблицы гигабайтного размера, и решил одним махом обновить данные в вашей рабочей таблице, для чего запускает один единственный Update который за какие-то пол часа обновляет несколько десятков миллионов записей. Только пока такой Update идёт использовать таблицы очень проблематично… Таблица заблокирована или совсем, или настолько загружена что толку с неё всё равно не много… А Вам нужны данные сейчас а не через пол часа. Что делать? А делать надо не вам а Васе… И сделать ему надо такую простую штуку как разбить свой запрос на обновление на несколько более мелких, с небольшими перерывами между ними, например крутить в цикле и делать обновление по 1000 записей за раз. Тогда между запросами на обновления будут проходить и ваши запросы, хотя и медленнее, но вся система не будет намертво заблокирована. Правда это приведёт что Васины действия по обновлению займут в 3-4 раза больше времени, но это не так страшно как полная недоступность системы в течение длительного времени.
Для MS SQL Server'а есть такие специальные конструкции SQL – hints, они указывают как именно серверу производить запрос, на результат запроса они не влияют. Привыборке данных если нет необходимости в особой тщательности выборки можно применять специальный hint. Рассмотрим на предыдущем примере – Вам нужна погода по всей стране и вы делаете запрос из таблицы. Вам совершенно всё равно успел Вася обновить какой-то город или нет, вас интересуют те данные которые есть сейчас, мало того, вы собираетесь эти данные только показывать, поэтому Ваши действия не должны мешать Васе, ему не надо ждать пока вы покажете данные, он может продолжать менять данные не взирая на то что вы делаете. Для обеспечения текого патерна поведения можно применить запрос:
Select чего-то там From MyTable with (nolock) Where …
Вот этот hint "with (nolock)" и говорит о том что вы не блокируете таблицу, что ваша выборка не должна отжать ситуацию на данный момент, она отраает лишь ситуацию на момент её взятия! Используйте этот hint везде где это только возможно, это существенно ускорит работу системы при большом количестве одновременных запросов к серверу.
Теперь индексы. Что такое индекс – индекс это специальная системная стуктура для быстрого поиска информации. Допустим у вас запрос
Select * From MyTable Where FirstName='Вася'
Без индексов на этом поле сервер вынужден будет в цикле перебрать все записи в таблице чтобы найти нужные записи. При наличие индексов сервер найдёт записи не проходя по свей таблице, а сразу прочитав в индексе номера записей в которых есть это соответствие. На больших таблицах разница во времени виполнения этого запроса при индексированном и не индексированном поле FirstName может составлять несколько порядков! Мало того безиндексный перебор записей в таблице очень сильно загружает процессор на сервере и загрузку памяти, при многопользовательском доступе такие запросы без индексов могут доканать систему до состояния когда самый мощный сервер будет скорее мёртв чем жив. Поэтому желательно чтобы все поля перечисленные в Where были индексированы. Но в этой идилии есть и ложка дёгтя. Во первых – все Update, Delete и Insert приводят к обновлению индексов, поэтому если на таблице очень много индексированных полей, а запросы на изменнеия данных весьма часты, то дополнительные индексы могут скорее тормозить систему чем ускорять. Во-вторых при очень сложных вложенных запросах с Join и сложными Where конструкциями из многих полей оптимизатор запросов далеко не всегда может правильно угадать какими индексами пользоваться. Например есть список клиентов банка, в таблице есть поля Фамилия и НаличиеКредитки, оба поля индексированы. Есть запрос
Select * From Clients Where Фамилия='Иванов' and НаличиеКредитки=1
Если оптимизатор использует индекс для поля НаличиеКредитки то по этому полю он выберет индексом миллион клиентов а потом среди них будет очень долго перебором искать Иванова, а если оптимизатор использует индекс по полю Фамилия – то моментально найдёт пять Ивановых, из которых простым перебором найти тех у кого есть кредитка не составит труда…
Есть таблицы которые имеют справочный характер и меняются чрезвычайно редко. Например коды городов, стран, географические названия, название валют и т.д. и т.п. Для таких таблиц операции по их изменнению проводятся исключительно редко, а вот поиск по ним очень част. Настоятельно рекомендую использовать кластерные индексы для таких таблиц. Кластерный индекс заставит сервер физически отсортировать данные в таких таблицах в алфавитном порядке и запросы к ним будут идти быстрее. Кластерные индексы категорически нельзя применять для данных которые часто меняются, потому что любое изменение будет инициировать физическую перестройку всей таблицы.