IBProvider Developer's Blog

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 transaction	3559	3566	3569	3572
Oldest active	3560	3567	3570	3573
Oldest snapshot	3561	3567	3570	3573
Next transaction	3561	3568	3571	3574
Next attachment ID	58	59	59	59

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

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

Статистика	Начальное [Запуск 3]	Запуск 4	Запуск 5	Запуск 6
Oldest transaction	3572	3575	3578	3581
Oldest active	3573	3576	3579	3582
Oldest snapshot	3573	3576	3579	3582
Next transaction	3574	3577	3580	3583
Next attachment ID	59	60	60	60

Было создано новое подключение. На каждый запуск было потрачено по 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 transaction	3591	3596	3597	3598
Oldest active	3592	3597	3598	3599
Oldest snapshot	3592	3597	3598	3599
Next transaction	3593	3598	3599	3600
Next attachment ID	61	62	62	62

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

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

Статистика	Начальное [Запуск 3]	Запуск 4	Запуск 5	Запуск 6
Oldest transaction	3598	3598	3598	3600
Oldest active	3599	3599	3599	3601
Oldest snapshot	3599	3599	3599	3601
Next transaction	3600	3601	3601	3602
Next attachment ID	62	63	63	63

Было создано новое подключение. На каждый запуск было потрачено по 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 transaction	3614	3619	3620
Oldest active	3615	3620	3621
Oldest snapshot	3615	3620	3621
Next transaction	3616	3621	3622
Next attachment ID	65	66	66

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

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

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

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

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

Вывод

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

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

Но если у вас:

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

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

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