Интересует практика, так что MS это или другой сервак, не важно.
Задача такая, получить данные с удаленного сервера, и каждую запись дважды (с небольшими изменениями) вставить в таблицу. Как реализовано на данный момент:
;with source as
(
select f1 from [server].[table]
)
-- Вставляем первый раз, если в итоговой таблицы нет такой записи (это внутренняя проверка она не относится к вопросу, а просто нуна)
INSERT INTO destination (f1, f2)
OUTPUT INSERTED.f1 INTO @affected (f1)
SELECT s.f1, 0 as f2 FROM source as s WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1)
-- Вставляем дубликат с измененным значением по второму полю
INSERT INTO destination/*в предыдущей версии сообщения ошибся, написав t*/ (f1, f2)
SELECT d.f1, 1 AS f2
FROM destination as d WHERE EXISTS(SELECT NULL FROM @affected WHERE f1 = d.f1);
Иногда хранимка отваливается по таймауту выполнив только первый инсерт (то есть все записи с f2=0 в destination появляются, а вот с f2=1 нет). А инога за пару секунд все вставляется
Здравствуйте, rFLY, Вы писали:
FLY>Интересует практика, так что MS это или другой сервак, не важно. FLY>Задача такая, получить данные с удаленного сервера, и каждую запись дважды (с небольшими изменениями) вставить в таблицу. Как реализовано на данный момент: FLY>
FLY>;with source as
FLY>(
FLY> select f1 from [server].[table]
FLY>)
FLY>-- Вставляем первый раз, если в итоговой таблицы нет такой записи (это внутренняя проверка она не относится к вопросу, а просто нуна)
FLY>INSERT INTO destination (f1, f2)
FLY> OUTPUT INSERTED.f1 INTO @affected (f1)
FLY>SELECT s.f1, 0 as f2 FROM source as s WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1)
FLY>-- Вставляем дубликат с измененным значением по второму полю
FLY>INSERT INTO t (f1, f2)
FLY>SELECT d.f1, 1 AS f2
FLY>FROM destination as d WHERE EXISTS(SELECT NULL FROM @affected WHERE f1 = d.f1);
FLY>
FLY>Иногда хранимка отваливается по таймауту выполнив только первый инсерт (то есть все записи с f2=0 в destination появляются, а вот с f2=1 нет). А инога за пару секунд все вставляется
спасет обработка транзакции
BEGIN TRY
BEGIN TRAN Ins
-- Вставляем первый раз, если в итоговой таблицы нет такой записи (это внутренняя проверка она не относится к вопросу, а просто нуна)
INSERT INTO destination (f1, f2)
OUTPUT INSERTED.f1 INTO @affected (f1)
SELECT s.f1, 0 as f2 FROM source as s WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1)
-- Вставляем дубликат с измененным значением по второму полю
INSERT INTO t (f1, f2)
SELECT d.f1, 1 AS f2
FROM destination as d WHERE EXISTS(SELECT NULL FROM @affected WHERE f1 = d.f1);
COMMIT TRAN Ins
END TRY
BEGIN CATCH
ROLLBACK TRAN Ins
END CATCH
Здравствуйте, BlackEric, Вы писали:
BE>Первое что пришло в голову — вынести измененное поле в отдельную таблицу по принципу 1:N
Была мысль сделать так:
...
INSERT INTO destination (f1, f2)
SELECT d.f1, tmp.f2
FROM source as s INNER JOIN (select 0 as f2 union select 1 as f2) tmp on f2 is not null
WHERE NOT EXISTS(SELECT NULL FROM destination WHERE f1 = s.f1)
ORDER BY tmp.f2; -- Думаю это важно, так как индекс идет по f2
Здравствуйте, svanir, Вы писали:
S>спасет обработка транзакции S> BEGIN TRY S> BEGIN TRAN Ins S> -- Вставляем первый раз, если в итоговой таблицы нет такой записи (это внутренняя проверка она не относится к вопросу, а просто нуна) S> INSERT INTO destination (f1, f2) S> OUTPUT INSERTED.f1 INTO @affected (f1) S> SELECT s.f1, 0 as f2 FROM source as s WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1)
S> -- Вставляем дубликат с измененным значением по второму полю S> INSERT INTO t (f1, f2) S> SELECT d.f1, 1 AS f2 S> FROM destination as d WHERE EXISTS(SELECT NULL FROM @affected WHERE f1 = d.f1); S> COMMIT TRAN Ins S> END TRY S> BEGIN CATCH S> ROLLBACK TRAN Ins S> END CATCH
Дела без TRY и CATCH, то есть
BEGIN TRAN
INSERT 1
COMMIT TRAN
INSERT 2
Вариант с ролбэком решит проблему целостности (которая впрочем не стоит остро, так как на стороне клиента можно запустить хранимку, которая инсертит уже только вторую порцию). Меня больше интересует причина несрабатывания второго инсерта. Почему такое может происходить? Во время выполнения второго инсерта происходит взаимоблокировка? Если так, то как это обойти?
Здравствуйте, rFLY, Вы писали:
FLY> Была мысль сделать так: FLY>
FLY> ...
FLY> INSERT INTO destination (f1, f2)
FLY> SELECT d.f1, tmp.f2
FLY> FROM source as s INNER JOIN (select 0 as f2 union select 1 as f2) tmp on f2 is not null
FLY> WHERE NOT EXISTS(SELECT NULL FROM destination WHERE f1 = s.f1)
FLY> ORDER BY tmp.f2; -- Думаю это важно, так как индекс идет по f2
FLY>
Здравствуйте, wildwind, Вы писали:
W>И что помешало?
Ничего не помешало, но в начале хотел посоветоваться здесь. Может есть и другие варианты, более "правильные".
W>P.S. Не INNER а CROSS, и ORDER BY не нужен.
Да, с CROSS лучше. Но ORDER BY нужен в моем случае, так как строки выдаваемые запросом, которые потом использует INSERT, идут 0,1,0,1, а нужно все 0, а затем все 1. Или сервак сам умный и сначала вставит все 0, а затем 1? Поскольку индекс как раз по этому полю, я опасаюсь, что если вставлять будет так как выдает селект, т.е. через один, то это плохо скажется на последующих выборках.
Здравствуйте, rFLY, Вы писали:
FLY>Здравствуйте, wildwind, Вы писали:
W>>И что помешало? FLY>Ничего не помешало, но в начале хотел посоветоваться здесь. Может есть и другие варианты, более "правильные".
W>>P.S. Не INNER а CROSS, и ORDER BY не нужен. FLY>Да, с CROSS лучше. Но ORDER BY нужен в моем случае, так как строки выдаваемые запросом, которые потом использует INSERT, идут 0,1,0,1, а нужно все 0, а затем все 1. Или сервак сам умный и сначала вставит все 0, а затем 1? Поскольку индекс как раз по этому полю, я опасаюсь, что если вставлять будет так как выдает селект, т.е. через один, то это плохо скажется на последующих выборках.
Никак это не скажется на последующих выборках. Сортировка в данном случае лишь замедлит выполнение запроса.
Здравствуйте, BlackEric, Вы писали:
BE>Никак это не скажется на последующих выборках. Сортировка в данном случае лишь замедлит выполнение запроса.
Но разве сервер физически не разметит строки через одну? И если все таки разместит это роли не сыграет? Я далёк от таких тонкостей, поэтому и переживаю. В дальнейшем выборки будут только по f2=значение. Мне казалось, что ему (серверу) проще будет выбирать когда записи физически идут одна за другой.
Здравствуйте, rFLY, Вы писали:
FLY>Но разве сервер физически не разметит строки через одну? И если все таки разместит это роли не сыграет? Я далёк от таких тонкостей, поэтому и переживаю. В дальнейшем выборки будут только по f2=значение. Мне казалось, что ему (серверу) проще будет выбирать когда записи физически идут одна за другой.
Сервер их разместит так, как посчитает нужным. А после update все равно запись скорее всего будет записана на новую страницу. Главное — добиться использования индексов в читающем запросе.
Всем спасибо, в итоге вышел на вариант ниже (и добавлю обработку транзакций как предложил svanir). Но еще нужно для тех значений что вставил, проапдейтить третью таблицу. Все бы ничего, но INSERTED.f1 INTO @affected вставит кучу одинаковых значений в @affected, это можно как-то избежать? Да, количество записей никак не сказывается на правильности выполнении UPDATE other, но в памяти таблица @affected будет в 2 раза больше чем требуется
;with source as
(
select f1 from [server].[table]
)
INSERT INTO destination (f1, f2)
OUTPUT INSERTED.f1 INTO @affected (f1) -- Вопрос 2SELECT s.f1, t.f2 FROM source as s, (select 1 as f2 union select 1 as f2) as t
WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1);
-- Для вставленных строк, изменяем значение в другой таблицеUPDATE other SET status = 10 WHERE f1 in (SELECT f1 FROM @affected);
Здравствуйте, rFLY, Вы писали:
FLY> BE>Никак это не скажется на последующих выборках. Сортировка в данном случае лишь замедлит выполнение запроса.
FLY> Но разве сервер физически не разметит строки через одну?
Строки в таблице или в индексе?
FLY> И если все таки разместит это роли не сыграет? Я далёк от таких тонкостей, поэтому и переживаю.
Сервер разместит так, как ему удобнее. Сыграет это роль или нет, зависит от многих факторов, поэтому, если нет желания вдаваться в тонкости, лучше просто не переживать.
FLY> В дальнейшем выборки будут только по f2=значение. Мне казалось, что ему (серверу) проще будет выбирать когда записи физически идут одна за другой.
Если только по f2, то и индекс скорее всего использоваться не будет, так как при столь малой селективности без индекса будет быстрее.
Здравствуйте, wildwind, Вы писали:
FLY>> Но разве сервер физически не разметит строки через одну? W>Строки в таблице или в индексе?
В таблице.
FLY>> В дальнейшем выборки будут только по f2=значение. Мне казалось, что ему (серверу) проще будет выбирать когда записи физически идут одна за другой. W>Если только по f2, то и индекс скорее всего использоваться не будет, так как при столь малой селективности без индекса будет быстрее.
Нет, f2 это для упрощения примера я написал. На самом деле всего 2 индекса: праймари на id по автоинкременту, и второй нонкластеред строится по трем полям таблицы одно из которых как раз f2 (опять же для упрощения, в реале название другое)
Здравствуйте, Lepsik, Вы писали:
L>IF NOT EXISTS(SELECT * FROM @affected WHERE f1 = d.f1) L> INSERT INTO destination (f1, f2)
Откуда возьмется в условии выделенное? @affected — это таблица содержащая некий признак записей, которые были вставлены в первый раз. Только для них мы делаем вставку второй раз.
Здравствуйте, rFLY, Вы писали:
FLY>Всем спасибо, в итоге вышел на вариант ниже (и добавлю обработку транзакций как предложил svanir). Но еще нужно для тех значений что вставил, проапдейтить третью таблицу. Все бы ничего, но INSERTED.f1 INTO @affected вставит кучу одинаковых значений в @affected, это можно как-то избежать? Да, количество записей никак не сказывается на правильности выполнении UPDATE other, но в памяти таблица @affected будет в 2 раза больше чем требуется
Вопрос на самом деле интересный, вот только удастся ли что-либо выиграть, сократив размер табличной переменной в два раза.
Попытаться решить вашу задачу можно вернувшись к первоначальному варианту, но исключив коррелированный запрос с exists Т.е. вместо…
-- Вставляем дубликат с измененным значением по второму полюINSERT INTO destination(f1, f2)
SELECT d.f1, 1 AS f2
FROM destination as d WHERE EXISTS(SELECT NULL FROM @affected WHERE f1 = d.f1);
…использовать…
-- Вставляем дубликат с измененным значением по второму полюINSERT INTO destination(f1, f2)
SELECT d.f1, 1 AS f2
FROM @affected as d
Ведь в @affected уже содержаться все данные, которые вы вставили один раз и дополнительная проверка на существование записи не нужна.
Здравствуйте, Olaf, Вы писали:
O>Вопрос на самом деле интересный, вот только удастся ли что-либо выиграть, сократив размер табличной переменной в два раза.
Только это (т.е. размер), ничего большего.
O>Попытаться решить вашу задачу можно вернувшись к первоначальному варианту, но исключив коррелированный запрос с exists Т.е. вместо… O>
-- Вставляем дубликат с измененным значением по второму полю
O>INSERT INTO destination(f1, f2)
O>SELECT d.f1, 1 AS f2
O>FROM @affected as d
O>Ведь в @affected уже содержаться все данные, которые вы вставили один раз и дополнительная проверка на существование записи не нужна.
Для того чтобы не перегружать пример, я его сократил, но неудачно. На самом деле помимо f1 из [server].[table] получаю больший набор данных. Так что то что вы предложили верно в контексте того что я писал ранее, но на самом деле все чуточку сложнее:
все значения этих полей вставляются оба раза, а в f2 при вставке в первый раз записываем одно константное значение, а второй раз другое. Конечно все что нужно из [server].[table] можно сразу записать в @affected и потом из нее оба раза вставить с разными значениями f2. Но тогда табличная переменная будет еще больше. Мне вариант со вставкой в одном инсерте кажется более оптимальным. С учетом вышеописанного он примерно такой:
INSERT INTO destination (f1, f1_2, f1_3,..., f1_n, f2)
OUTPUT INSERTED.f1 INTO @affected (f1) -- Вопрос 2SELECT s.f1, s.f1_2, s.f1_3,..., s.f1_n, t.f2 FROM source as s, (select 1 as f2 union select 1 as f2) as t
WHERE NOT EXISTS (SELECT NULL FROM destination WHERE f1=s.f1);
И теперь мне @affected нужен только для апдейта еще одной таблицы. То есть в одну таблицу вставляем (дважды), а в другой помечаем апдейтом то что вставили.
Но ORDER BY нужен в моем случае, так как строки выдаваемые запросом, которые потом использует INSERT, идут 0,1,0,1, а нужно все 0, а затем все 1. Или сервак сам умный и сначала вставит все 0, а затем 1? Поскольку индекс как раз по этому полю, я опасаюсь, что если вставлять будет так как выдает селект, т.е. через один, то это плохо скажется на последующих выборках.
