Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: SQLite3 оптимизация хранения

Уважаемые форумчане! Требуется ваша помощь!

Есть таблица-словарь SQLite3, где перечисляется что-то (не важно что, суть не в этом)

Ранее создавал так:
CODE (text):

  1. CREATE TABLE table (
  2.     id INTEGER PRIMARY KEY AUTOINCREMENT,
  3.     obj TEXT UNIQUE
  4. );


Задумался, ведь тут по сути 3 поля с UNIQUE (rowid, id, obj), 2 с AUTOINCREMENT (rowid, id). Получается дублируются роли полей, выполняется возможно ненужная работа, нерациональность. Насколько я понимаю PRIMARY KEY в себе несёт UNIQUE и NOT NULL.

Может лучше так:
CODE (text):

  1. CREATE TABLE table (
  2.     obj TEXT PRIMARY KEY
  3. ) WITHOUT ROWID;


Подскажите пожалуйста, какие могут быть подводные камни нового подхода? Иными словами, какие минусы?
Ответ:
tonchikp пишет:
ведь тут по сути 3 поля с UNIQUE (rowid, id, obj)

ТУТ только 2 поля UNIQUE т.к. rowid и id - два именя для одного и того же поля

Проведем небольшой эксперимент:
CODE (SQL):

  1. CREATE TABLE table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj TEXT UNIQUE);
  2. INSERT INTO table1 (obj) VALUES ('aaaaa'),('bbbbb'),('ccccc');
  3. INSERT INTO table1 VALUES (25,'zzzzz');
  4. SELECT rowid,id FROM table1;

получим
CODE (text):

  1. id|id
  2. 1|1
  3. 2|2
  4. 3|3
  5. 25|25

как выдно, 1. значения совпадают 2. даже в загловке выдно, что это одно и то же поле (кстати, чтобы включить вывод заголовка в SQLite, дайте команду .header on, по умолчанию заголовки не выводятся)
Продолжим эксперименты, теперь создадим поле без AUTOINCREMENT

CODE (SQL):

  1. CREATE TABLE table2 (id INTEGER PRIMARY KEY, obj TEXT UNIQUE);
  2. INSERT INTO table2 (obj) VALUES ('aaaaa'),('bbbbb'),('ccccc');
  3. INSERT INTO table2 VALUES (25,'zzzzz');
  4. SELECT rowid,id FROM table2;

Результат в точности тот же, т.е. INTEGER PRIMARY KEY оказывается достаточно.

Попробуем еще поэкспериментировать, заменим INTEGER на INT:
CODE (SQL):

  1. CREATE TABLE table3 (id INT PRIMARY KEY, obj TEXT UNIQUE);
  2. INSERT INTO table3 VALUES (25,'zzzzz');
  3. SELECT rowid,id FROM table3;

CODE (text):

  1. rowid|id
  2. 1|25

Как видим, результат изменился, т.е. теперь id и rowid - два разных поля.

Т.е. резюме, поле rowid создается не всегда иногда это просто другое имя длы уже имеющегося поля. Когда конкретно? Тут есть некая магия, порой противоречащая здравому смыслу, надо просто заучить, либо пробовать и смотреть что получается (как пробовать я показал).
(Добавление)
tonchikp пишет:
Может лучше так:
Чтобы ответить на этот вопрос, нужно посмотреть на всю задачу целиком, а не только на структуру таблицы. Но, (мое скромное ИМХО) скорее всего нет, не лучше. Если поле id нужно, (где-то еще используется) то оставьте как есть. А если не нужно - можно его и убрать, физически таблица останется точно такая же. Но неявное поле rowid по умолчанию заводится не случайно, так в данной конкретной СУБД опримизированы индексы, что с ним лучше. Не претендую на истину в последней инстанции.

(Отредактировано автором: 28 Августа, 2017 - 14:05:43)

Вопрос: Запуск sqlite3 в bat-файле

Доброго времени суток!
Есть проблема - пока яростно торможу.
Задача: Нужно автоматически выбирать информацию из базы sqlite.
Мои потуги:
1. Скачал sqlite3.exe (
2. Сделал bat:
start Путь\sqlite3.exe < 1.txt (1.txt - файл с командами sqlite).
3. 1.txt:
.open main.db
.mode list
.separator |
.output test.txt
select * from Название_таблицы;
.exit
4. В режиме командной строки команды (п.3) работают, но если запускаю bat - команды из 1.txt не забираются.
Мануал по bat смотрел тут ( строки:
Miscellaneous
command < file {Redirects file output to command.}

Помогите, плиз!!!
Фраза "кури мануал" и "куда ты со своим рылом в Калашный ряд" не устраивают.
Ответ: > Мануал по bat смотрел тут ( строки:
> Miscellaneous
> command < file {Redirects file output to command.}
>
> Winnipuh, спасибо за ответ!
> Вроде заработало. Вывелись результаты в файл.
> Мешала команда start!
> Почему??

Имеет место непонимание синтаксиса командного интерпретатора.
Для вашего
start Путь\sqlite3.exe < 1.txt

этим command будет не `sqlite.exe', а `start'.
Вопрос: Оптимизация хранения файлов

Добрый день.

Раздумываю над вариантами хранения файлов
1) в отдельной таблице
2) в одной вместе с остальной атрибутивной информацией. Примерно так
CREATE TABLE [dbo].ayEDSStore(
	[IdDoc] [int] NOT NULL, -- идентификатор документа
	[IdFile] [int] NOT NULL, -- идентификатор файла
	[Name] [varchar](255) NOT NULL, -- наименование файла
	[DateSign] [datetime] NOT NULL DEFAULT (getdate()), -- когда добавлена ЭЦП
	[UserName] [varchar](255) NOT NULL, -- кто создал, учётка
	[Signature] varbinary(max) NULL, -- отсоединённая ЭЦП
	[Role] [varchar](100) NULL, -- характер работы (роль) подписавшего
	[Activity] tinyint NULL  -- 0 - подпись неактивна, 1- активна
) ON [PRIMARY]


Ненужные файлы будут удаляться, но атрибутивная информация должна остаться. Какой вариант лучше с точки зрения оптимизации дискового пространства?
Ответ: Glory,
Благодарю! Этого достаточно
Вопрос: SQLite3 Русские символы

здраствуйте, нужна ваша помощь.
Код на С++ в проект подключено sqlite3.h sqlite3.lib, все что необходимо.
Добавление записей в базу происходит через консоль (файл sqlite3.exe).
В некоторых столбцах записей имеются русские символы.
Подключение и получение записей происходит из кода проекта, все извлекается успешно, только вместо русских символов получаются следующие:
Ответ: Теперь все стало понятным. Благодарю.
Вопрос: Нужна помощь в установке sqlite3 модуль на хостинг

Приветствую форумчане. Нужно подключить расширение sqlite3 для хостинга. Суть проблемы, заказал скрипт, но при его установке на сайт выдает ошибку Fatal error: Class 'SQLite3' not found in, техподдержка хостинга R01 сказали, что у них версия 2.8 и обновляться они не планируют, а жаль, и предложили мне установить модуль самостоятельно, выслали мне мануал в котором я точно не разберусь.
вот что пишут
Для установки собственного расширения следуйте инструкции:

1. Включаем в панели управления нужную версию PHP и временно
выключаем Веб-сервер для минимизации потребления памяти.
2. Подключаемся к хостингу по SSH(
3. Переходим в каталог ~/tmp
cd ~/tmp
4. Загружаем дистрибутив нужного модуля в каталог ~/tmp
wget на дистрибутив, найденная в сети Интернет)
5. Распаковываем архив и переходим в папку с исходными кодами
tar -xf extension.tgz
cd extension
6. Прежде чем компилировать модуль, рекомендуется прочитать файлы README и INSTALL с
описанием возможных опций сборки модуля.
7. Конфигурируем и компилируем.
/opt/php/bin/phpize
./configure --with-php-config=/opt/php/bin/php-config #этот параметр обязателен, при
необходимости добавляем необходимые в соответствии с документацией.
make
В результате в подкаталоге .libs будет размещен скомпилированный модуль dbase.so
8. Так как добавить модуль в системную папку /opt/php/lib/php/extensions не представляется
возможным, создадим свою и добавим в нее новый модуль и символические ссылки на
стандартные модули:
mkdir ~/extensions
cp .libs/extension.so ~/extensions/
ln -s /opt/php/lib/php/extensions/* ~/extensions/
9. Необходимо создать свой php.ini в домашнем каталоге на основе существующего:
cp ~/etc/php.ini ~/
mcedit ~/php.ini
10. Отредактировать php.ini, указав новую папку с модулями и новое расширение.
extension_dir="/home/идентификатор/extensions/"
extension=extension.so

11. Чтобы изменения вступили в силу включаем или перезагружаем веб-сервер.

Буду благодарен, если подскажите, что и как сделать
Ответ: White Owl, поможешь установить? за рубли?
Вопрос: sqlite3. База данных

Спроектировал базу данных.
Сформировал ее в sqlite3
Помогите разобраться почему не работают связи и каскадное удаление. В чем ошибка?
Имеем 3 таблицы:
tab1: Название дисциплины(*), Название области знания
tab2: ФИО автора учебника(*), Название книги, Издательство, Год
tab3: ФИО автора учебника, Название дисциплины, Количество экземпляров, Заключение
(ФИО автора и название дисциплины - внешние ключи )
SQL-скрипт формирования базы данных:
CREATE TABLE tab1(dis text NOT NULL UNIQUE, oblzn text NOT NULL, PRIMARY KEY(dis));
CREATE TABLE tab2(fio text NOT NULL UNIQUE, name_kn text NOT NULL, izd text, god INT NOT NULL, PRIMARY KEY(fio));
CREATE TABLE tab3(fio text NOT NULL UNIQUE REFERENCES tab2(fio) ON UPDATE cascade ON DELETE cascade, 
dis text NOT NULL UNIQUE REFERENCES tab1(dis) ON UPDATE cascade ON DELETE cascade, 
ekz INT NOT NULL, zacl text NOT NULL, 
FOREIGN KEY(dis) REFERENCES tab1(dis), FOREIGN KEY(fio) REFERENCES tab2(fio));


Модератор: Тема перенесена из форума "MySQL".
Ответ: Забавно, но по ходу может) В утверждается, что "Added support for foreign key constraints" только начиная с 3.6.19. Это можно проверить - взять, например, свежую версию библиотеки и подсунуть в проект.
Вопрос: Sqlite3 и локальная сеть

есть программа на дельфи 7 и база данных из 4 таблиц. максимальное колич. записи в одной талице 5000000. использую sqlite 3.10.(январь 2016г.) локально все хорошо. теперь нужно чтобы читали и писали через локальную сеть 5...10 пользователей. положил базу в сетевой диск. при внесении изменений на другом компе изменения не показываются. при выходе из проги пишет ошибка отложенной записи .... .db-shm. исползую обертку sivak3 для доступа и управления бд. у кого есить опыт изпользования sqlite3 в локальной сети поделитесь опытом. знание английского около 0 поэтому не отправляйте читать англоязычную документацию. пожлюйста если можно куски кода и конкретные настройки.
Ответ: REZ-ult,



У тебя отсутствует слой логики.
Вопрос: Sqlite3 Данные из 3 таблиц одним запросом

Добрый день. Нужна помощь гуру sql запросов.
Работаю с sqlite3. Пытаюсь вытащить данные из 3 таблиц одним запросом.
Связь между таблицами по внешнему ключу такая: DBtableRooms (DBtableRooms.ID), DBtableSensors(DBtableSensors.RoomID) и DBtableDoors(DBtableDoors.RoomID)
Делаю такой запрос:
SQL
1
2
3
4
5
SELECT DBtableRooms.ID, DBtableRooms.Number, DBtableRooms.Name, DBtableSensors.ID, DBtableSensors.Name, DBtableDoors.ID, DBtableDoors.Name
FROM DBtableRooms
LEFT OUTER JOIN DBtableSensors ON (DBtableSensors.RoomID = DBtableRooms.ID) AND (DBtableSensors.ShowMC = 1)
LEFT OUTER JOIN DBtableDoors ON (DBtableDoors.RoomID = DBtableRooms.ID) AND (DBtableDoors.ShowACS = 1) 
WHERE DBtableRooms.ID = 3
Получаю результат (на скрине во вложении).
Не устраивает то, что в столбцах ID2 и Name2 (строки 2,3,4) значения повторяются, хотелось бы увидеть там пустые ячейки. Т.к. в помещениях разное количество дверей и датчиков и эти дублирования сильно напрягают.
Помогите доработать запрос или может связь между таблицами поправить нужно.
Ответ: Ну если нужно принципиально в одном запросе, то, боюсь, на Sqlite3 не получить 3х2=3.
Тогда вам на Oracle или MS SQL нужно, где подзапросы можно связать ещё и по Row_Number().
Или два независимых грида рядом, один для дверей, другой - для датчиков

Добавлено через 5 минут
посмотри на комнату с несколькими дверями и несколькими датчиками. С помощью твоего запроса. И придумай для начала, что там ты хотел бы "обнулить"
Вопрос: Структура для хранения большого объема исторических данных

Доброго дня!
Стоит задача оптимизации хранения истории значений по параметрам объектов.
Т.е. есть таблица объектов(Id, Name), параметров(Id, Name, ObjId), всех значений параметров(Id, ParameterId, ChangeTime, Value) и таблица с текущими(актуальными) значениями (ParameterId, ParameterValueId).
Последняя таблица нужна для быстрого отображения текущих значений.
А вот с выборкой значений за какой-то период беда. Также необходимо строить отчеты, усреднять, анализировать и т.д.
Прирост данных в таблицу со значениями примерно 2 млн строк в неделю. В будущем это значение будет только увеличиваться. сложно прогнозировать до какого значения, но полагаю 2 млн в сутки это реально.

Собственно вопрос: как лучше организовать хранение такого рода данных. СУБД MSSQL Server
Есть следующие идеи:
1. под каждый объект создавать отдельную таблицу и писать значения туда. Число объектов думаю будет порядка 100 000;
2. значения за последние сутки(т.к. в 50% случаях используются именно эти данные) хранить в отдельной таблице;
3. Разбивать данные по месяцам либо даже неделям и хранить их в отдельных таблицах.

Дайте совет у кого был такого рода опыт.

Спасибо.
Ответ: Data Vault, Anchor
Вопрос: Оптимизация запроса

Здравствуйте.
Прошу помощи в оптимизации sql-запроса.
Описание:
Есть 2 таблицы SprTovar и Sklad
Необходимо сформировать выборку содержащую информацию о всех товарах и остатков по ним.
SprTovar - справочная таблица по всем товарам, в которой есть поля типа Kod, Name и прочее.
Sklad - таблица остатков. В эту таблицу заносится информация по кол-ву товара на определенную дату при условии что на эту дату было движение.
Т.е. например Яблоко.
01.01.2015 остаток = 100 шт
10.01.2015 был приход в кол-ве 200 шт.
15.01.2015 было реализовано 50 шт

В таблице Sklad касаемо это товара будут следующие записи
01.01.2015 100 (т.е. изначальный остаток)
10.01.2015 300 (включая приход +200)
15.01.2015 250 (минус реализация -50)

При добавлении товара в какой либо документ, необходимо отображать весь список товаров с указанием остатков по каждой записи. Необходимо учитывать что записи об остатках в таблице Sklad может и не быть.
Для выборки использую такой запрос:
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.DATA, b.KodTov, Sklad.Kol
                           from (select MAX(DATA) as DATA, KodTov
                                        from Sklad
                                        where KodSklad=0
                                          and DATA<='14.04.2015'
                                        group by KodTov
                                ) b,
                                Sklad
                           where b.KodTov=Sklad.KodTov
                             and b.DATA=Sklad.DATA
                             and Sklad.KodSklad=0
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)

Описание ключевых полей
Sklad.DATA - дата, на которое сформирован остаток
Sklad.KodTov - уникальный код товара
Sklad.Kol - количество на дату
Sklad.KodSklad - код склада на котором хранится товар.
SprTovar.Kod - код товара
SprTovar.Name - наименование товара

Выборка выполняется верно. Но!
Не устраивает скорость выборки. При 5000 записей в таблице SprTovar и 300000 записей в таблице Sklad (информация за 3 года) запрос выполняется около 10 секунд. Хотелось бы ускорить процесс выборки.
Прошу помощи в оптимизации sql-запроса...
Также рад выслушать замечания об эффективном хранении остатков товаров.

Спасибо за потраченное время.
Ответ:
Андрей Рябенко
Пользователю нужно сделать изменения в документе за 20.03.2015.
Он их сделал - провел документ.
В итоге надо пересчитать остатки на конец марта, т.е. на 31.03.2015. Так?
А если ему взбрело в голову изменить документ за месяцев 5 назад, то придется
пересчитывать остатки на каждый конец месяца (т.е. на 5 дат)
Я понимаю, что нехорошо пользователю играться с задними числами. Но они пересчитывают.

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

Но даже если ты настаиваешь на хранении толпы остатков, то изменение пяти остатков это тот
же один запрос "update sklad set amount+:change where amount_date>:change_date". И
опять-таки операция редкая и риска нарваться на update conflict - нет.

Но повторю ещё раз: при хранении одного базового остатка, запрос получения остатка на
любую другую дату выливается в тривиальный и быстрый JOIN + GROUP BY.

Posted via ActualForum NNTP Server 1.5