@thkala Я должен изучить это.
ользуюSQLite3 в одном из моих проектов, и я должен убедиться, что строки, которые вставляются в таблицу, являются уникальными в отношении комбинации некоторых из своих столбцов. В большинстве случаев вставленные строки будут отличаться в этом отношении, но в случае совпадения новая строка должна обновить / заменить существующую.
Очевидным решением было использование составного первичного ключа с условием конфликта для обработки коллизий. Выше этого:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);
стало так:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);
Это действительно обеспечивает ограничение уникальности, как мне нужно. К сожалению, это изменение также влечет за собой снижение производительности, которое намного превосходит мои ожидания. Я сделал несколько тестов, используяsqlite3
утилита командной строки, чтобы убедиться, что в остальной части моего кода нет ошибок. Тесты включают ввод 100 000 строк, либо в одной транзакции, либо в 100 транзакциях по 1000 строк в каждой. Я получил следующие результаты:
| 1 * 100,000 | 10 * 10,000 | 100 * 1,000 |
|---------------|---------------|---------------|
| Time | CPU | Time | CPU | Time | CPU |
| (sec) | (%) | (sec) | (%) | (sec) | (%) |
--------------------------------|-------|-------|-------|-------|-------|-------|
No primary key | 2.33 | 80 | 3.73 | 50 | 15.1 | 15 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld3 | 5.19 | 84 | 23.6 | 21 | 226.2 | 3 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld2, Fld3 | 5.11 | 88 | 24.6 | 22 | 258.8 | 3 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld0, Fld2, Fld3 | 5.38 | 87 | 23.8 | 23 | 232.3 | 3 |
В настоящее время мое приложение выполняет транзакции не более чем в 1000 строк, и я был удивлен 15-кратным падением производительности. Я ожидал не более чем трехкратного снижения пропускной способности и увеличения загрузки ЦП, как это было в случае с транзакцией 100 тыс. Транзакций. Я предполагаю, что для индексации, связанной с поддержанием ограничений первичного ключа, требуется значительно большее количество синхронных операций с БД, что делает мои жесткие диски узким местом в этом случае.
С помощьюРежим WAL действительно имеет некоторый эффект - увеличение производительности примерно на 15%. К сожалению, этого недостаточно.PRAGMA synchronous = NORMAL
казалось, не имеет никакого эффекта.
I мог бы иметь возможность восстановить некоторую производительность за счет увеличения размера транзакции, но я бы предпочел этого не делать из-за увеличения использования памяти и опасений по поводу скорости отклика и надежности.
Текстовые поля в каждой строке имеют переменную длину в среднем около 250 байт. Производительность запросов не имеет большого значения, но производительность вставки очень важна. Код моего приложения написан на C и (должен быть) переносим как минимум в Linux и Windows.
Есть ли способ улучшить производительность вставки без увеличения размера транзакции? Либо какая-то настройка в SQLite (что угодно, но не принудительно заставляет БД работать в асинхронном режиме) или программно в коде моего приложения? Например, есть ли способ обеспечить уникальность строк без использования индекса?
BOUNTY:
Используя метод хеширования / индексации, описанный в моем собственном ответе, мне удалось несколько смягчить падение производительности до точки, где это, вероятно, приемлемо для моего приложения. Однако кажется, что с увеличением числа строк в таблице наличие индекса делает вставки все медленнее и медленнее.
Я заинтересован в любой технике или настройке параметров, которые повысят производительность в этом конкретном случае использования, если только это не включает взлом кода SQLite3 или иным образом приводит к невозможности поддержки проекта.