Вот столкнулся с интересной задачей.

Java приложение получает через сокет интенсивный (до несколько тысяч строк в секунду) поток данных и эти строки надо вставлять в таблицу базы данных.

Вставлять по одной строке через INSERT.. невозможно, так как база не успевает и данные теряются. Однако есть команда BULK INSERT [table] FROM [имя файла], которая работает раз в 10 быстрее. Поэтому возможно решить задачу путем записи потока в файлы (например, открывать ежесекундно новый файл) и закачивать каждый файл в базу. Таким образом данные будут поступать в базу порциями и с некоторой задержкой.

Такой подход работает, но возникают проблемы синхронизации - некоторые файлы база не может найти и т.д. Очевидно, что это решаемо, но требует больших усилий.

Какие другие подходы здесь возможны?

А кэшировать в памяти никак?

Вообще отдельные insertы не обязательно писать.

это создаст тебе 6 строк сразу.

Если данные приходят с паузами и операционки хватеае - кэшируй в памяти. даже 100 МБ это очень много строк, а памяти - ерунда.

Такой инсерт, наверное, не всеми базами поддерживается? На MSSQL так не получается.
За идею спасибо ! Но кеширование на диске выглядит предпочтительнее.

Это сообщение отредактировал(а) COVD - 8.2.2006, 20:32

Можно поинтересоваться, почему? Диск в сотни раз медленнее памяти. Если боитесь потери данных - так на то трансакции и существуют.
Как вариант для вашего решения - просто файлы в памяти. Хотя простое кэширование лучше конечно.

COVD, попробуй использовать пакетный режим, предусмотренный JDBC:

Сначала команды напихиваются в пакет, а потом дружно отправляются не сервер:





В случае использования PreparedStatement алгоритм примерно тот же, только во внутреннем цикла нужно добавить код по присвоению параметров, а вместо stm.addBatch(String sql) использовать беспараметрный pstm.addBatch().

Попробуй поиграться с размером пакета и посмотри, насколько будет быстрее. Ну и ним сообщи, нам тоже интересно

Это сообщение отредактировал(а) Stampede - 8.2.2006, 20:56

Я бы кешировал в памяти, но из памяти быстро в базу не получается вставить. Конструкция вставки нескольких строк одной командой INSERT у меня не получается, а BULK только под файлы заточен. А скорость записи из java в файл меня вполне устраивает. И заодно решается проблема бекапа данных и некоторые другие.

Спасибо, надо посмотреть.

Я уже сравнивал скорость вставки строк в таблицу через обычный Statement, Prepared и Callable. Измерял время вставки 1000 одинаковых строк. К своему удивлению обнаружил что разницы ощутимой нет. И даже от количества параметров не зависит - что 1, что 20. Обычно первый эксперимент длился несколько дольше, скажем 1000 мс, а все последующие около 800 мс. Очевидно, умный ДБ сервер кэширует запросы.

Вместе с тем, запись в файл и вставка в таблицу через BULK INSERT проходят в 8-10 раз быстрее. Из чего я делаю вывод, что основные тормоза из-за того, что вставка через команду INSERT осуществляется в транзакции, которую отключить нельзя иначе как только применив BULK.

Если batch вставка выполняется как одна транзакция, то возможно и будет эффект.

Не знаю, что за СУБД, но вообще это отключается Connection.setAutoCommit(false), а потом ручной commit.

Другая возможность - транзакция вообще не была открыта и поэтому при каждом запросе BEGIN/COMMIT происходит заного.

В это мслучае надо попробовать начать транзакцию самому (ну и закончить тоже, конечно) когда надо

По идее это можно через SQL (не знаю насчёт MSSQL)

execute("BEGIN");
execute("INSERT...");
:
:
:
execute("COMMIT");

Я честно говоря не знаю ни одной СУБД с поддержкой транзакций которая не начинала бы транзакцию при изменении данных.

В JDBC принудительно начать транзакцию можно connection.setSavepoint().

В книге по MSSQL написано что единственная возможность отключить транзакции - это перестартовать сервер в режиме что-то вроде SingleUserMode, т.е. это не рабочий режим.

А он по умолчанию true???
(у меня появилась одна идея насчет геморроя на работе)

Крме того, явное выполнение команд BEGIN TRANSACTION и COMMIT может привести к непредсказуемым результатам. В JDBC на самом деле делать нужно так:

Прежде всего, нужно указать режим автокоммита: Connectuin.setAutoCommit(boolean b). По умолчанию он стоит в true, то есть каждая выполняемая команда инициирует новую транзакцию, которая по выполнении команды автоматически фиксируется. Тут важно отметить, что завершение транзакция знаменует не только сохранение изменений, но и ряд других важных действий, таких как освобождение выделенных ресурсов и - внимание! - снятие удерживаемых блокировок.

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

Почему, в таком случае, дефолтным режимом сделан автокоммит? А это сделано в расчете на начинающих, которые пробуют всякие датабазные примеры "хелло ворлд" - чтобы у них не случилось конфуза: вроде данные вставлял, а результатов не видно.

Другое дело, что у этих же начинающих потом происходит конфуз другого рода, когда они узнают, что при вставке тысячи записей у них инициируется тысяча транзакций, но, похоже, в Sun посчитали, что это меньшее зло.

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



Обращаю внимание, не stm.execute("commit"), а посредством специально предусмотренного метода в JDBC API. Это нужно для того, чтобы и драйвер тоже знал, что транзакция - завершается. Потому что у драйвера - свое хозяйство, и его тоже надо содержать в чистоте и порядке

Вот так вот, если вкратце.

Да.

Прогнал тесты.

Время вставки 500 строк:

1. 843 мс - 500 раз выполнил st.executeUpdate("INSERT ..");
2. 532 мс - 500 раз выполнил st.executeUpdate("INSERT .."); но перед циклом сделал st.getConnection().setAutoCommit(false); а после цикла st.getConnection().сommit();
3. 390 мс - 500 раз выполнил st.addBatch("INSERT.."); и после цикла st.executeBatch(). Также отключил и сделал коммит. Если коммит в конце не делать, то быстрее на 10 %

Для 1000 строк:

1. 1300 ms
2. 900 ms
3. 400

Для 2000 строк:

1. 2800
2. 1500
3. 800

Таким образом, на MSSQL отключение транзакции средствами JDBC убыстряет запись в 1,5 - 2 раза. Batch вставка быстрее в 2 - 4 раза, причем отключение транзакции здесь существенно не влияет (убыстрение около 10 %).

Спасибо всем участникам обсуждения, особенно Stampede'у.

Это сообщение отредактировал(а) COVD - 9.2.2006, 00:40