Синхронизация таблиц SQLite с MSSQL

@Graf1385 · Регистрация: 09.10.2015

Author24 — интернет-сервис помощи студентам

Приветствую всех. Друзья подскажите как лучше синхронизировать две идентичных по структуре таблицы одна в локальной базе SQLite другая на MSSQL? Короче нужно перенести недостающие строки из SQLite в MSSQL . Объём таблицы может достигать более 3 гб. Может есть какие то инструменты для таких манипуляций или эффективней всего будет просто отправлять запрос с условием типа

SQL

1	IF NOT EXISTS(SELECT [id] FROM Table1 WHERE [id] = @id) BEGIN INSERT INTO Table1(id, Value1) VALUES(@id, @value1) END

@hoolygan · 15.02.2018, 14:47

Такого вида запрос может затормозить систему - так как будет организован цикл - а 3 Гб - это не 1 сотня тыс записей.
Как вариант - сначала импортировать из SQLite в сиквел сервер в отдельную таблицу (тут способ выбирайте сами).
А далее

T-SQL

1
2
3

insert table 1 (val1, vla2)
select t2.val1, t2.val2
from table2 t2 where not exists (select 1 from table1 t1 where t1.val1 = t2.val1 and t1.val2 = t2.val2)

Думаю сторона, куда гуглить более прояснилась

@Igr_ok · 15.02.2018, 15:15

Graf1385, берите только новые записи.
В SQLite

SQL

1	SELECT MAX(id) FROM table1

Полученное значение подставляем в MS SQL

SQL

1	SELECT * FROM table1 WHERE id>@id

@hoolygan · 15.02.2018, 15:23

Igr_ok, это только частный случай.
Если же айдишки (а в основном так и есть) ставятся на уровне таблицы как Identity - то в каждой таблице они будут свои, и никак не синхронизированы.

@Igr_ok · 15.02.2018, 15:45

Сообщение от hoolygan

Если же айдишки (а в основном так и есть) ставятся на уровне таблицы как Identity - то в каждой таблице они будут свои, и никак не синхронизированы.

Разве из поста ТСа не очевидно, что в SQLite данные импортируются с id?

@hoolygan · 15.02.2018, 16:00

Не по теме:

Igr_ok, Я привык не доверять людям, что используют циклы в простых запросах к таблицам :D
Это значит, что они могут не понимать до конца, что им нужно :D

@Diamante · 18.02.2018, 01:33

вообще, подобные вещи делаются без "посредников",средствами СУБД
шарп, при всем его "величии", лишний

@Graf1385 · 19.02.2018, 14:20 **[ТС]**

А как насчёт SqlBulkCopy я сейчас на него смотрю по скорости он вроде нормально, то есть получать данные из SQLITE в DATATABLE и отправлять на сервер, только не понятно если на прямую в таблицу данные лучше не копировать как в током случаи использовать временную таблицу отдельным запросом создавать временную таблицу. Тут ещё момент что использовать функции или хранимые процедуры не получиться так как имя таблицы в которую необходимо добавить данные заранее неизвестно имя программа получает из другой таблицы. Ещё проблемы с SqlBulkCopy то что он не умеет пропускать строку если такой ключ существует, но такую проверку можно сделать отдельным запросом а вот если использовать временную таблицу тогда я так понял это вполне возможно.

@Usaga · 19.02.2018, 14:42

Сообщение от Diamante

вообще, подобные вещи делаются без "посредников",средствами СУБД
шарп, при всем его "величии", лишний

В постановке вопроса сказано, что имеют место быть две разные СУБД. Так, что без "посредников" тут никак, если только в MSSQL нет встроенных средств импорта данных из SQLite.

Добавлено через 8 минут
Graf1385, в принципе, вам уже дали ответ в перовом же сообщении: выгружайте данные в промежуточную таблицу, а оттуда уже в основную. Промежуточную таблицу можно создавать в том же запросе- это не проблема.

Запросы лучше разбить на пачки по 500 (или менее!) строк, чтобы не блокировать всю базу на время долгой операции.

Тут есть один момент:

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

Есть другой, но он потребует изменение схем одной из двух баз (лучше всего базы-источника). Вам нужно в каждую таблицу, что хотите экспортировать, добавить колонку для хранения признака произведённого ранее экспорта. Для экспортированных данных выставлять признак и в последующих итерациях эти данные уже не использовать. Это будет на порядок (а со временем - на два) лучше, чем каждый раз всё перепроверять.

@Graf1385 · 20.02.2018, 07:03 **[ТС]**

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

@Usaga · 20.02.2018, 07:10

Graf1385, SqlBulkCopy никаких таблиц не создаёт. Что он делает и как им пользоваться можно посмотреть в документации, там и примеры есть.

@Graf1385 · 20.02.2018, 07:23 **[ТС]**

По поводу двух таблиц я так понимаю это нужно если запись ведётся несколькими клиентами одновременно, у меня запись ведётся каждым клиентом в разные таблицы, а вот читать могут несколько, то есть при подключении к MSSQL клиент создаёт новую таблицу если она не была создана ранние имена таблиц тоже уникальны на сколько это позволяет GUID. Просто я не очень представляю как мне использовать вторую таблицу. У меня вот какая идея я создаю буферную таблицу, создаю под неё триггер который при добавлении записей будет проверять есть ли запись в основной таблице если нет добавлять если есть удалять как такой вариант что посоветует?

Добавлено через 1 минуту
А по по поводу документации я уже прочитал, думал может есть какие то подводные камни.

@Usaga · 20.02.2018, 07:29

Graf1385, вам буферную таблицу порекомендовали чисто как оптимизацию. Вы в неё вываливаете всё одним быстрым движением, а уже внутри базы переносите данные через insert... select.. where not exists.... Т.е. такой подход быстрее будет работать, ибо он уже внутри базы будет происходить, но это вовсе не обязательно.

Если данных не особо много (или они разбиты на порции по N штук), то можно и отдельными запросами обойтись.

Сообщение от Graf1385

клиент создаёт новую таблицу если она не была создана ранние имена таблиц тоже уникальны на сколько это позволяет GUID

В рамках запроса (сессии) любая временная таблица (начинающаяся с # или объявленная локально) - уникальна, даже если имя переиспользуется. Т.е. не обязательно создавать настоящую таблицу, в СУБД существуют понастоящему временные таблицы.

Сообщение от Graf1385

У меня вот какая идея я создаю буферную таблицу, создаю под неё триггер который при добавлении записей будет проверять есть ли запись в основной таблице если нет добавлять если есть удалять как такой вариант что посоветует?

Ну это уже фигня какая-то начинается.

Добавлено через 36 секунд
Вообще, это всё интереса ради делается или вы правда стокнулись с проблемами производителности при переносе данных?

@Graf1385 · 20.02.2018, 07:59 **[ТС]**

Нет не ради интереса, после первой установки программы на компьютере уже есть данные и их много вот их необходимо перенести в локальную базу данных для хранения потом на MSSQL для удалённого просмотра ну и хранения. Далее данные будут сохраняться в локальной базе данных по мере поступления ну и в удалённую тоже если есть связь с сервером а вот если связи нет будут пропуски, вот эти пропуски и необходимо синхронизировать. Проблема с производительностью есть по причине не опытности работы с СУБД по крайней мере с большими объёмами. Раньше в таких случаях я передавал данные в XML файлами там на сервере разворачивал во временную таблицу ну и добавлял. Тут такой подход не получиться так как имя таблицы неизвестно а передать его в аргументах функции у меня не получилось.

@Usaga · 20.02.2018, 08:02

Graf1385, самый простой способ - разбивать данные на порции и посылать. Так и у базы будет время на "отдохнуть" от ваших данных (обработать другие запросы) и данные потихоньку будут импортироваться. Если данных много, то тут ничего уже не выдумать.

@Graf1385 · 20.02.2018, 08:05 **[ТС]**

Хорошо спасибо за совет так и сделаю. Можно тему закрывать.

@Usaga 12078 / 8387 / 1281 Регистрация: 21.01.2016 Сообщений: 31,600
	20.02.2018, 07:10	11
	Graf1385, SqlBulkCopy никаких таблиц не создаёт. Что он делает и как им пользоваться можно посмотреть в документации, там и примеры есть. 0

@Graf1385 0 / 0 / 5 Регистрация: 09.10.2015 Сообщений: 229
	20.02.2018, 07:23 [ТС]	12
	По поводу двух таблиц я так понимаю это нужно если запись ведётся несколькими клиентами одновременно, у меня запись ведётся каждым клиентом в разные таблицы, а вот читать могут несколько, то есть при подключении к MSSQL клиент создаёт новую таблицу если она не была создана ранние имена таблиц тоже уникальны на сколько это позволяет GUID. Просто я не очень представляю как мне использовать вторую таблицу. У меня вот какая идея я создаю буферную таблицу, создаю под неё триггер который при добавлении записей будет проверять есть ли запись в основной таблице если нет добавлять если есть удалять как такой вариант что посоветует? Добавлено через 1 минуту А по по поводу документации я уже прочитал, думал может есть какие то подводные камни. 0

@hoolygan 360 / 287 / 76 Регистрация: 21.06.2016 Сообщений: 1,115
	15.02.2018, 15:23	4
	Igr_ok, это только частный случай. Если же айдишки (а в основном так и есть) ставятся на уровне таблицы как Identity - то в каждой таблице они будут свои, и никак не синхронизированы. 0

@Igr_ok 784 / 615 / 273 Регистрация: 04.08.2015 Сообщений: 1,707
	15.02.2018, 15:45	5
	Сообщение от hoolygan Если же айдишки (а в основном так и есть) ставятся на уровне таблицы как Identity - то в каждой таблице они будут свои, и никак не синхронизированы. Разве из поста ТСа не очевидно, что в SQLite данные импортируются с id? 0

@hoolygan
	15.02.2018, 16:00 #6
	Не по теме: Igr_ok, Я привык не доверять людям, что используют циклы в простых запросах к таблицам :D Это значит, что они могут не понимать до конца, что им нужно :D 0

@Diamante 3560 / 2501 / 1174 Регистрация: 14.08.2016 Сообщений: 8,206
	18.02.2018, 01:33	7
	вообще, подобные вещи делаются без "посредников",средствами СУБД шарп, при всем его "величии", лишний 0

@Graf1385 0 / 0 / 5 Регистрация: 09.10.2015 Сообщений: 229
	19.02.2018, 14:20 [ТС]	8
	А как насчёт SqlBulkCopy я сейчас на него смотрю по скорости он вроде нормально, то есть получать данные из SQLITE в DATATABLE и отправлять на сервер, только не понятно если на прямую в таблицу данные лучше не копировать как в током случаи использовать временную таблицу отдельным запросом создавать временную таблицу. Тут ещё момент что использовать функции или хранимые процедуры не получиться так как имя таблицы в которую необходимо добавить данные заранее неизвестно имя программа получает из другой таблицы. Ещё проблемы с SqlBulkCopy то что он не умеет пропускать строку если такой ключ существует, но такую проверку можно сделать отдельным запросом а вот если использовать временную таблицу тогда я так понял это вполне возможно. 0

@Usaga 12078 / 8387 / 1281 Регистрация: 21.01.2016 Сообщений: 31,600
	19.02.2018, 14:42	9
	Сообщение от Diamante вообще, подобные вещи делаются без "посредников",средствами СУБД шарп, при всем его "величии", лишний В постановке вопроса сказано, что имеют место быть две разные СУБД. Так, что без "посредников" тут никак, если только в MSSQL нет встроенных средств импорта данных из SQLite. Добавлено через 8 минут Graf1385, в принципе, вам уже дали ответ в перовом же сообщении: выгружайте данные в промежуточную таблицу, а оттуда уже в основную. Промежуточную таблицу можно создавать в том же запросе- это не проблема. Запросы лучше разбить на пачки по 500 (или менее!) строк, чтобы не блокировать всю базу на время долгой операции. Тут есть один момент: Текущий подход подразумевает постепенное увеличение времени импорта данных, так как, судя по всему, вы собираетесь каждый раз выгребать все данные и искать им соответствия в целевой базе. Это очень неэффективный способ. Есть другой, но он потребует изменение схем одной из двух баз (лучше всего базы-источника). Вам нужно в каждую таблицу, что хотите экспортировать, добавить колонку для хранения признака произведённого ранее экспорта. Для экспортированных данных выставлять признак и в последующих итерациях эти данные уже не использовать. Это будет на порядок (а со временем - на два) лучше, чем каждый раз всё перепроверять. 1

@Graf1385 0 / 0 / 5 Регистрация: 09.10.2015 Сообщений: 229
	20.02.2018, 07:03 [ТС]	10
	Да флаг отправлено или нет уже стоит я сразу это предусмотрел, конечно я не собирался перебирать миллионы строк каждый раз, вопрос стоял просто наеболиие эффективный способ. Типа варианта с SqlBulkCopy не кто не знает он не создаёт промежуточных таблиц а то получить что я создам ещё и SqlBulkCopy создаст, бред. Или может не стоит использовать его по другим причинам о которых я узнаю когда напишу кучу кода? 0

@Usaga 12078 / 8387 / 1281 Регистрация: 21.01.2016 Сообщений: 31,600
	20.02.2018, 07:29	13
	Graf1385, вам буферную таблицу порекомендовали чисто как оптимизацию. Вы в неё вываливаете всё одним быстрым движением, а уже внутри базы переносите данные через `insert... select.. where not exists...`. Т.е. такой подход быстрее будет работать, ибо он уже внутри базы будет происходить, но это вовсе не обязательно. Если данных не особо много (или они разбиты на порции по N штук), то можно и отдельными запросами обойтись. Сообщение от Graf1385 клиент создаёт новую таблицу если она не была создана ранние имена таблиц тоже уникальны на сколько это позволяет GUID В рамках запроса (сессии) любая временная таблица (начинающаяся с # или объявленная локально) - уникальна, даже если имя переиспользуется. Т.е. не обязательно создавать настоящую таблицу, в СУБД существуют понастоящему временные таблицы. Сообщение от Graf1385 У меня вот какая идея я создаю буферную таблицу, создаю под неё триггер который при добавлении записей будет проверять есть ли запись в основной таблице если нет добавлять если есть удалять как такой вариант что посоветует? Ну это уже фигня какая-то начинается. Добавлено через 36 секунд Вообще, это всё интереса ради делается или вы правда стокнулись с проблемами производителности при переносе данных? 0

@Graf1385 0 / 0 / 5 Регистрация: 09.10.2015 Сообщений: 229
	20.02.2018, 07:59 [ТС]	14
	Нет не ради интереса, после первой установки программы на компьютере уже есть данные и их много вот их необходимо перенести в локальную базу данных для хранения потом на MSSQL для удалённого просмотра ну и хранения. Далее данные будут сохраняться в локальной базе данных по мере поступления ну и в удалённую тоже если есть связь с сервером а вот если связи нет будут пропуски, вот эти пропуски и необходимо синхронизировать. Проблема с производительностью есть по причине не опытности работы с СУБД по крайней мере с большими объёмами. Раньше в таких случаях я передавал данные в XML файлами там на сервере разворачивал во временную таблицу ну и добавлял. Тут такой подход не получиться так как имя таблицы неизвестно а передать его в аргументах функции у меня не получилось. 0

@Usaga 12078 / 8387 / 1281 Регистрация: 21.01.2016 Сообщений: 31,600
	20.02.2018, 08:02	15
	Сообщение было отмечено Graf1385 как решение Решение Graf1385, самый простой способ - разбивать данные на порции и посылать. Так и у базы будет время на "отдохнуть" от ваших данных (обработать другие запросы) и данные потихоньку будут импортироваться. Если данных много, то тут ничего уже не выдумать. 1

	20.02.2018, 08:05

@Graf1385 0 / 0 / 5 Регистрация: 09.10.2015 Сообщений: 229
	20.02.2018, 08:05 [ТС]	16
	Хорошо спасибо за совет так и сделаю. Можно тему закрывать. 0

Синхронизация таблиц SQLite с MSSQL

Решение