Оптимизации SQLite

[Человек_Борща]

доброго времени суток!

Собственно разрабатываю БД под свой проект.
В XML стало напряжно хранить большой массив данных, да и управлять этим зверем тоже стало сложно, БД справится лучше.

Исходник БД во втором посте.

Таблица Groups и Childrens представляют собой древовидную структуру, хранимую в таблице tree, так же есть view отображающая последнее видимое состояние в UI (Здесь tree,view не описаны, не нужно).
Таблицы table_blobs1(1:1), table_blobs2(1:n) хранят некоторый массив данных, принадлежащих конкретной записи childrens.ID, в blob1 принадлежит 1 запись, в blob2 - бесконечное множество.

Так же я переложил всю работу на БД в части формирования статистики, с помощью триггеров, после INSERT, DELETE для групп, элементов, блобов, в таблице Stats обновляются соотв. поля.

Проблемы:
1. В блобах планируется хранить файлы. На сколько это разумно?
2. Можно ли блобы отделить от отновной БД, затем подключать их и использовать (JOIN к примеру)?
3. Каскадное удаление данных, если удаляется запись из Groups, все childrens.id, group.id (для них так же срабатывает триггер) ссылающиеся на удаляемую запись - так же стираются, при удалении записи из childrens, чистятся таблицы с BLOB'ми.
Я делаю это с помощью триггеров.
Тут же обновляется статистика, и tree, view.

итого такой подход очень дорого стоит в не большой БД на 5-10 тыс. записей, даже если это делать внутри транзакции.
Есть ли иной, более правильный подход?

4. Как правильно делать статистику? Сейчас я это делаю триггерами после вставки, удаления, для каждой записи. Но работает медленно на 20-30 тыс записей, на 50-60 тыс. - страх и ужос.

Как лечить?

Цель: на 100 тыс.записей тормозов быть не должно Рабочая нагрузка будет примерно 5-50 тыс. записей в каждой таблице, для blobs2 - 150 тыс. записей при достижении 50 тыс.записей в childrens.

[Человек_Борща]

И так DDL моей БД:

CREATE TABLE "childrens" (
  "Id" INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, 
  "kind" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "GroupID" INTEGER, 
  "Name" TEXT NOT NULL ON CONFLICT FAIL, 
  "Description" TEXT NOT NULL ON CONFLICT REPLACE, 
  "DateCreate" DATETIME DEFAULT (now()), 
  "blob1id" INTEGER, 
  "Deleted" BOOL NOT NULL ON CONFLICT REPLACE DEFAULT False, 
  "DateDeleted" BOOL);

CREATE TRIGGER "childrens_DelStats"
AFTER DELETE
ON "childrens"
FOR EACH ROW
BEGIN
     UPDATE stats SET ChildCount = ChildCount - 1;
END;

CREATE TRIGGER "childrens_addStats"
AFTER INSERT
ON "childrens"
FOR EACH ROW
BEGIN
     UPDATE stats SET ChildCount = ChildCount + 1;
END;

CREATE TRIGGER "childrens_CleanupBlobs2"
AFTER DELETE
ON "childrens"
FOR EACH ROW
BEGIN
  DELETE FROM table_blobs2 WHERE ParentID = OLD.id;
END;

CREATE TRIGGER "childrens_CleanupBlob1"
AFTER DELETE
ON "childrens"
FOR EACH ROW
BEGIN
  DELETE FROM table_blobs1 WHERE ParentID = OLD.id;
END;


CREATE TABLE "groups" (
  "Id" INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, 
  "kind" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 1, 
  "GroupID" INTEGER, 
  "Name" TEXT NOT NULL ON CONFLICT FAIL, 
  "Description" TEXT NOT NULL ON CONFLICT REPLACE, 
  "GroupKind" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "DateCreate" DATETIME DEFAULT (now()), 
  "Deleted" BOOL NOT NULL ON CONFLICT REPLACE DEFAULT False, 
  "DateDeleted" BOOL);

CREATE TRIGGER "Groups_UpdateTree"
AFTER DELETE
ON "groups"
FOR EACH ROW
BEGIN
  DELETE FROM tree WHERE (kind = 1) and (ParentID = OLD.id);
END;

CREATE TRIGGER "Groups_CleanupLinks"
AFTER DELETE
ON "groups"
FOR EACH ROW
BEGIN
  DELETE FROM links WHERE GroupID = OLD.id;
END;

CREATE TRIGGER "Groups_CleanupGroups"
AFTER DELETE
ON "groups"
FOR EACH ROW
BEGIN
  DELETE FROM Groups WHERE GroupID = OLD.id;
END;

CREATE TRIGGER "Groups_addStats"
AFTER INSERT
ON "groups"
FOR EACH ROW
BEGIN
     UPDATE stats SET GroupsCount = GroupsCount + 1;
END;

CREATE TRIGGER "Groups_DelStats"
AFTER DELETE
ON "groups"
FOR EACH ROW
BEGIN
     UPDATE stats SET GroupsCount = GroupsCount - 1;
END;


CREATE TABLE "stats" (
  "ChildCount" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "GroupsCount" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "Blobs1Count" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "Blobs2Count" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0);


CREATE TABLE "table_blobs1" (
  "Id" INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, 
  "ParentID" INTEGER, 
  "Path" TEXT NOT NULL ON CONFLICT REPLACE, 
  "CRC32File" TEXT, 
  "data" BLOB, 
  "Size" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0, 
  "CRC32" TEXT);

CREATE TRIGGER "BLOB1_DelStats"
AFTER DELETE
ON "table_blobs1"
FOR EACH ROW
BEGIN
     UPDATE stats SET Blobs1Count = Blobs1Count - 1;
END;

CREATE TRIGGER "BLOB1_addStats"
AFTER INSERT
ON "table_blobs1"
FOR EACH ROW
BEGIN
     UPDATE stats SET Blobs1Count = Blobs1Count + 1;
END;


CREATE TABLE "table_blobs2" (
  "id" INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, 
  "ParentID" INTEGER, 
  "Path" TEXT NOT NULL ON CONFLICT REPLACE, 
  "CRC32File" TEXT, 
  "Data" BLOB, 
  "CRC32" TEXT, 
  "Size" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT (-1), 
  "Width" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT (-1), 
  "Height" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT (-1));

CREATE TRIGGER "BLOB2_addStats"
AFTER INSERT
ON "table_blobs2"
FOR EACH ROW
BEGIN
     UPDATE stats SET Blobs2Count = Blob2Count + 1;
END;

CREATE TRIGGER "BLOB2_DelStats"
AFTER DELETE
ON "table_blobs2"
FOR EACH ROW
BEGIN
     UPDATE stats SET Blobs2Count = Blobs2Count - 1;
END;

[Аватар]

1. Файлы в блобах бы не держал
2. stats бы не создавал. Вся статистика запросом элементарно получается
3. Не вижу ни одного индекса. Естественно при удалении будут долго записи искаться. Но и обилие индексов убыстрив поиск замедлит удаление. Но они нужны в разумных пределах
4. Зачем несколько триггеров на удаление, в одном нельзя все сделать? Может SQLite не позволяет, не в курсе. Если позволяет - срочно объединить

[Человек_Борща]

1. Файлы в блобах бы не держал

тоже так думаю.

2. stats бы не создавал. Вся статистика запросом элементарно получается

Скорее всего переведу это в разряд view. и сниму триггеры.

3. Не вижу ни одного индекса. Естественно при удалении будут долго записи искаться. Но и обилие индексов убыстрив поиск замедлит удаление. Но они нужны в разумных пределах

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

4. Зачем несколько триггеров на удаление, в одном нельзя все сделать? Может SQLite не позволяет, не в курсе. Если позволяет - срочно объединить

позволяет скорее всего.
Триггер можно сделать для каждой записи и для STATEMENT(как понимать?) так с понимаем самого слова проблема, у него масса значений, в контексте БД что это? 1 запрос?

[Аватар]

Индексы для полей из WHERE. Составные если несколько полей

Судя по этому https://www.sqlite.org/lang_createtrigger.html есть только триггера FOR EACH ROW. Т.е. триггер срабатывает для каждой обрабатываемой строки таблицы, а не для всей команды редактирования в целом. Вообще-то не в курсе, не работаю с этой СУБД. Для каждой строки значит, что триггер отработает три раза при удалении одной командой трех строк