@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 или иным образом приводит к невозможности поддержки проекта.

Ответы на вопрос(5)

Ваш ответ на вопрос