Linked Server. Механика выполнения запроса на выборку данных.

Привет всем.

В этой короткой заметке, я проведу небольшое исследование использования IBProvider-a в качестве связанного сервера MSSQL. Основная цель — показать недостатки неявного управления транзакциями. Ну и, до кучи, узнаем что MSSQL хочет от IBProvider-а.

Будут использоваться:

  • MSSQL 2008 R2 Express Edition
  • Firebird 2.5
  • LCPI.IBProvider.3

Все эксперименты будут выполняться в «Microsoft SQL Server Management Studio».

Cоздаем Linked Server IBP_TEST_FB25:

  • Поставщик: LCPI OLEDB Provider for Interbase [v3]
  • Название продукта: IBP_TEST_FB25
  • Источник данных: IBP_TEST_FB25
  • Строка поставщика: ctype=win1251; truncate_char=false; nested_trans=true; support_odbc_query=true; dbtime_rules=2; dbclient_library=fbclient.dll; icu_library=icuuc30.dll; auto_commit=true;
  • Расположение: localhost:d:\database\ibp_test_fb25_d3.gdb
  • На вкладке «безопасность» указываем «удаленный вход с паролем».

В целом, про настройку связанного сервера можно посмотреть здесь. А лучше — здесь.

Открываем окно для выполнения запросов.

Неявное управление транзакциями

Три раза подряд выполняем скрипт:

select * FROM IBP_TEST_FB25...RDB$PROCEDURES;

После каждого запуска снимаем статистику с базы данных.

СтатистикаНачальноеЗапуск 1Запуск 2Запуск 3
Oldest transaction3559356635693572
Oldest active3560356735703573
Oldest snapshot3561356735703573
Next transaction3561356835713574
Next attachment ID58595959

Видим следующее:
Для первого запуска понадобилось 7 транзакций. Для второго и третьего — по 3 транзакции.
Все три запуска отрабатывают в рамках одного подключения — это работает пул подключений.

Курим несколько минут и повторяем эксперимент.

СтатистикаНачальное [Запуск 3]Запуск 4Запуск 5Запуск 6
Oldest transaction3572357535783581
Oldest active3573357635793582
Oldest snapshot3573357635793582
Next transaction3574357735803583
Next attachment ID59606060

Было создано новое подключение. На каждый запуск было потрачено по 3 транзакции.

На что тратятся первые семь транзакций?

  1. Запрос данных схемы DBSCHEMA_TABLES_INFO
  2. Запрос данных схемы DBSCHEMA_COLUMNS
  3. Запрос данных схемы DBSCHEMA_INDEXES
  4. Запрос данных схемы DBSCHEMA_CHECK_CONSTRAINTS_BY_TABLE
  5. Открытие таблицы через IOpenRowset::OpenRowset. MSSQL хочет проверить описания колонок — сами данные не выбираются.
  6. Подготовка (PREPARE) запроса на выборку данных (ICommandPrepare::Prepare)
  7. Выполнение запроса (ICommand::Execute)

На что тратятся транзакции в последующих запусках?

  1. Открытие таблицы через IOpenRowset::OpenRowset. MSSQL хочет проверить описания колонок — сами данные не выбираются.
  2. Подготовка (PREPARE) запроса на выборку данных (ICommandPrepare::Prepare)
  3. Выполнение запроса (ICommand::Execute)

При последующих запусках интересен факт того, что отсутствует фактическая подготовка запросов на уровне Firebird. Это отрабатывает пул запросов (на втором уровне IBProvider-a), который обнаруживает у себя идентичный свободный подготовленный запрос и задействует его.

К сожалению первый уровень про это не знает и всегда стартует транзакцию. А если бы знал, то при вызове ICommandPrepare::Prepare можно было бы сэкономить одну транзакцию.

И эта мысль теперь будет поедать мой мозг.

Явное управление транзакциями

Перезапускаем MSSQL. Перезапускаем «Microsoft SQL Server Management Studio».

Три раза выполняем скрипт:

begin distributed transaction;
select * FROM IBP_TEST_FB25...RDB$PROCEDURES;
commit;

После каждого запуска снимаем статистику с базы данных.

СтатистикаНачальноеЗапуск 1Запуск 2Запуск 3
Oldest transaction3591359635973598
Oldest active3592359735983599
Oldest snapshot3592359735983599
Next transaction3593359835993600
Next attachment ID61626262

Для первого запуска понадобилось 5 транзакций. Для второго и третьего — по 1 транзакции.
Все три запуска отрабатывают в рамках одного подключения.

Курим несколько минут и повторяем эксперимент.

СтатистикаНачальное [Запуск 3]Запуск 4Запуск 5Запуск 6
Oldest transaction3598359835983600
Oldest active3599359935993601
Oldest snapshot3599359935993601
Next transaction3600360136013602
Next attachment ID62636363

Было создано новое подключение. На каждый запуск было потрачено по 1 транзакции.

Стоит отметить следующий забавный факт — коммиты четвертого и пятого запуска доехали до сервера только к концу шестого запуска. Похоже это была фоновая (отложенная) фиксация распределенных транзакций и моё резвое переключение между окнами «Microsoft SQL Server Management Studio» и консоли, в которой я запускал gstat.exe.

Куда тратились транзакции?

Самый первый запуск:

  1. Запрос данных схемы DBSCHEMA_TABLES_INFO
  2. Запрос данных схемы DBSCHEMA_COLUMNS
  3. Запрос данных схемы DBSCHEMA_INDEXES
  4. Запрос данных схемы DBSCHEMA_CHECK_CONSTRAINTS_BY_TABLE
  5. [DTC] Операции, связанные непосредственно с селектом:
    — IOpenRowset::OpenRowset
    — ICommandPrepare::Prepare
    — ICommand::Execute

Пятая транзакция была создана в составе распределенной, которая управлялась координатором распределенных транзакций (DTC).

В последующих запусках MSSQL уже не запрашивает метаданные и в рамках одной транзакции выполняет:

  1. [DTC] Операции, связанные непосредственно с селектом:
    — IOpenRowset::OpenRowset
    — ICommandPrepare::Prepare
    — ICommand::Execute
Затраты на чтение схем метаданных

Как было видно из предыдущих экспериментов — у нас есть получение данных четырех схем: TABLES_INFO, COLUMNS, INDEXES, CHECK_CONSTRAINTS_BY_TABLE. Каждый такой запрос ест одну транзакцию. Потому что MSSQL запрашивает эти данные вне рамок нашей явной транзакции.

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

Перезапускаем MSSQL и последовательно выполняем:

Первый скрипт

begin distributed transaction;
select * FROM IBP_TEST_FB25...RDB$PROCEDURES;
commit;

Второй скрипт

begin distributed transaction;
select * FROM IBP_TEST_FB25...RDB$RELATIONS;
commit;

Статистика:

СтатистикаНачальноеСкрипт 1Скрипт 2
Oldest transaction361436193620
Oldest active36153620 3621
Oldest snapshot361536203621
Next transaction36163621 3622
Next attachment ID656666

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

Рассмотрим запуск второго скрипта. Трассировка взаимодействия MSSQL и IBProvider-а показывает, что запрос метаданных для таблицы RDB$RELATIONS никуда не делся. Но здесь отработал кэш данных схем, который «заполнился» в процессе запуска первого скрипта, выжил в пуле подключений и вернул закэшированные метаданные при запуске второго скрипта.

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

Кэш метаданных можно отключить, указав в строке подключения "schema_cache=0". В этом случае IBProvider всегда будет запрашивать метаданные (фактически — выполнять запросы к служебным таблицам) у IB/FB. Естественно, что провайдер будет стараться минимизировать объемы перекачиваемых данных, генерируя запросы с учетом указанных ему ограничений (например — имя таблицы).

В некоторых случаях, например когда база данных IB/FB доступна через медленные (типа VPN) соединения, отключение кэша метаданных существенно ускоряет работу. Потому что в провайдер закачиваются только нужные данные. И хотя они закачиваются каждый раз, эта неприятность частично сглаживается кэшем запросов, который контролирует все операции с базой данных — и пользовательские и служебные.

Вывод

Покупайте наших слонов!

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

Но если у вас:

  • данные смотрят не глаза, а программы
  • нужно прочитать информацию из нескольких таблиц

То лучше все таки немного напрячься и явно стартовать транзакцию (с уровнем изоляции — «повторямое чтение»). Чтобы не было потом мучительно больно.

И да пребудет с вами Великая Сила!

Оставить комментарий