Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
Доброго времени суток!
Помогите пожалуйста с решением следующей задачи.

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
среда: ssms

Есть 2 таблицы test1 и test2, состав полей одинаковый (например, выгрузка за вчерашний и сегодняшний день). Объем - 1,5 млн записей.
В каждой таблице есть 3 поля: id1, id2, id3, которые содержат неповторяющиеся значения типа (допустим) int или NULL.

Задача: найти в таблице test2 строки, совпадающие со строками таблицы test1
1. удалить их из test1
2. скопировать их в стороннюю таблицу test3
строки, которые есть в test2 и не нашлись в test1 - скопировать в test3
строки, которые есит в test1 и не нашлись в test2 - не делать ничего

Строки считаются совпадающими если выполнены условия:
1. хотя бы 1 из id1, id2, id3 в test2 совпал с соответствующим id в test1
2. остальные id непротиворечивы. То есть либо они тоже совпадают, либо хотя бы один из сравниваемых id = NULL. То есть:

iif(test1.id1 <> test2.id1 or test1.id2 <> test2.id2 or test1.id3 <> test2.id3, 1, 0) = 0

Проблема: попытка прямого джойна по указанным условиям работает дни. Как использовать в данном случае merge не поняла (нам требуется проводить не только update\insert test1 и test2 но и сторонней таблицы), да и сможет ли это ускорить выполнение запроса?
25 фев 15, 13:14    [17309813]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
начните читать про индексы
25 фев 15, 13:26    [17309930]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
lless,

полагаете, что с NULLом можно использовать оператор <> ?

MERGE, похоже, тут не поможет, ибо есть посторонние таблицы.
Делайте постепенно:
Вставьте в test3 из test1 (2.)
Удалите вставленные строки из test1 (1.)
Вставьте в test3 из test2
25 фев 15, 13:27    [17309938]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Добрый Э - Эх
Guest
lless,

как вариант, покурить про SET-операторы (INTERSECT, EXCEPT)
25 фев 15, 13:38    [17310021]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
Добрый Э - Эх, Там вроде бы нет возможности задать условия, о которых пишет lless
25 фев 15, 13:45    [17310083]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Merdoc
Добрый Э - Эх, Там вроде бы нет возможности задать условия, о которых пишет lless
WHERE NOT EXISTS(SELECT test1.id1,test1.id2,test1.id3 INTERSECT SELECT test2.id1,test2.id2,test2.id3)
25 фев 15, 13:52    [17310135]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
iap,
И при этом сравниваются все три ID разом. А необходимо чтобы хотя бы одно из условий выполнялось (or)
25 фев 15, 13:53    [17310146]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Merdoc
iap,
И при этом сравниваются все три ID разом. А необходимо чтобы хотя бы одно из условий выполнялось (or)
Там же <> or <> or <>
То есть требуется хотя бы одно несовпадение.
INTERSECT же возвращает полное совпадение всех трёх.
Значит, NOT EXISTS - то, что надо.
25 фев 15, 14:00    [17310204]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
lless
Задача: найти в таблице test2 строки, совпадающие со строками таблицы test1
1. удалить их из test1
2. скопировать их в стороннюю таблицу test3
строки, которые есть в test2 и не нашлись в test1 - скопировать в test3
строки, которые есит в test1 и не нашлись в test2 - не делать ничего

Надо:

Есть в test1 и test2 - перенести из test1 в test3
Есть только в test2 - скопировать в test3
Есть только в test1 - ничего не делать

Предлагаю заменить на:

Есть в test1 и test2 - удалить из test1
Все записи из test2 - скопировать в test3

Результат тот же. Но выполнить проще.
25 фев 15, 14:00    [17310205]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Собственно, говоря, строго математически

x1<>x2 or y1<>y2 or z1<>z2 ==> not(x1=x2 and y1=y2) or z1<>z2
==> not(not(not(x1=x2 and y1=y2)) and z1=z2) ==>not(x1=x2 and y1=y2 and z1=z2)
25 фев 15, 14:05    [17310233]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
iap, в NOT EXISTS с INTERSECT попадут не только те где есть одно\два совпадения, но и полные несовпадения, где все id отличаются и соответствия нет.
25 фев 15, 14:08    [17310246]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
iap, Все сорри. Проглядел второе условие у автора и запутался. Спасибо!
25 фев 15, 14:10    [17310266]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Можно и EXISTS(SELECT... EXCEPT SELECT...) применить.
Но мы давным-давно здесь выяснили,
что в случае INTERSECT у индексов появляется шанс

Почему INTERSECT или EXCEPT? Да потому что они трактуют значение NULL как равное другому NULL.
25 фев 15, 14:16    [17310303]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
iap,

iap
Почему INTERSECT или EXCEPT? Да потому что они трактуют значение NULL как равное другому NULL.


Почитала про эти операторы, но не поняла, как их использовать в моей задаче. Мне же надо не только NULL равное другому NULL трактовать, но и NULL равным любому другому значению.

Примеры совпадающих и не совпадающих строк по моим условиям:
1. null a null = null a b
2. null a b != null a c
3. c null b != null a null

Напрямую прописанные мои условия выглядят так:

select test1.*
from 
	test1
	join test2 on (
		(test1.id1 = test2.id1
		or test1.id2 = test2.id2
		or test1.id3 = test2.id3)
		and iif(test1.id1 <> test2.id1 or test1.id2 <> test2.id2 or test1.id3 <> test2.id3, 1, 0) = 0 
	
	)


Про iff :
select iif(NULL <> 1, 1, 0)
возвращает 0

Но работает ОЧЕНЬ медленно. Нужна замена.
25 фев 15, 16:52    [17311375]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
Akina,

Согласна. Указанное мной искусственное усложнение логики связано с наличием в реальной задаче дополнительных условий на копирование в стороннюю таблицу.:) Но потеря времени не на этом этапе. И помочь оптимизировать я прошу саму "проверку на совпадение строк".

см. мой комментарий выше, надеюсь я смогла выразиться точнее..
25 фев 15, 17:10    [17311496]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
Maxx,

все таблицы индексированы
25 фев 15, 17:10    [17311500]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
lless,
iap же вам операторы уже расшифровал ведь.. и без жуктйо ф-ции iif
25 фев 15, 17:21    [17311569]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
lless,

читайте про NULL

Внимательно ещё раз просмотрел Ваши условия равенства строк.
Получается, Ваше "То есть" на самом деле должно выглядеть вот так?
(t1.id1=t2.id1 OR t1.id1 IS NULL AND t2.id1 IS NULL OR t1.id1 IS NOT NULL AND t2.id1 IS NOT NULL) AND (t1.id2 IS NULL OR t2.id2 IS NULL OR t1.id2=t2.id2) AND (t1.id3 IS NULL OR t2.id3 IS NULL OR t1.id3=t2.id3)
OR
(t1.id2=t2.id2 OR t1.id2 IS NULL AND t2.id2 IS NULL OR t1.id2 IS NOT NULL AND t2.id2 IS NOT NULL) AND (t1.id1 IS NULL OR t2.id1 IS NULL OR t1.id1=t2.id1) AND (t1.id3 IS NULL OR t2.id3 IS NULL OR t1.id3=t2.id3))
OR
(t1.id3=t2.id3 OR t1.id3 IS NULL AND t2.id3 IS NULL OR t1.id3 IS NOT NULL AND t2.id3 IS NOT NULL) AND (t1.id1 IS NULL OR t2.id1 IS NULL OR t1.id1=t2.id1) AND (t1.id2 IS NULL OR t2.id2 IS NULL OR t1.id2=t2.id2)
25 фев 15, 17:22    [17311573]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
lless
помочь оптимизировать я прошу саму "проверку на совпадение строк".

Думаю, нужно избавляться от функции путём увеличения числа логических операций и двигаться в сторону задействования индексов. Например
  SELECT test1.id
  FROM test1, test2 
  WHERE test1.id1 = test2.id1 OR test1.id1 IS NULL OR test2.id1 IS NULL
INTERSECT
  SELECT test1.id
  FROM test1, test2 
  WHERE test1.id2 = test2.id2 OR test1.id2 IS NULL OR test2.id2 IS NULL
INTERSECT
  SELECT test1.id
  FROM test1, test2 
  WHERE test1.id3 = test2.id3 OR test1.id3 IS NULL OR test2.id3 IS NULL

Собирать все три условия в один запрос страшненько - трудно придумать индекс, эффективно работающий для такого монстра. А так достаточно покрывающих индексов по (idX, id), остальные поля получить по id записи несложно.
Впрочем, эффективность этого решения зависит от того, насколько объёмна и селективна каждая отдельная выборка. Не заткнётся ли INTERSECT...
25 фев 15, 17:32    [17311634]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
iap,

Ладно, про NULL принято. Все-таки результат NULL <> 1 = UNKNOWN, а не FALSE.

Насчет списка условий, не совсем так. Контрпримером вашему списку будет приведенный мною ранее 2. null a b != null a c

Как мне кажется правильно так:

(t1.id1=t2.id1) AND (t1.id2 IS NULL OR t2.id2 IS NULL OR t1.id2=t2.id2) AND (t1.id3 IS NULL OR t2.id3 IS NULL OR t1.id3=t2.id3)
OR
(t1.id2=t2.id2) AND (t1.id1 IS NULL OR t2.id1 IS NULL OR t1.id1=t2.id1) AND (t1.id3 IS NULL OR t2.id3 IS NULL OR t1.id3=t2.id3)
OR
(t1.id3=t2.id3) AND (t1.id1 IS NULL OR t2.id1 IS NULL OR t1.id1=t2.id1) AND (t1.id2 IS NULL OR t2.id2 IS NULL OR t1.id2=t2.id2)



Вот и что с этим делать? :)
25 фев 15, 17:56    [17311801]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
lless
Member

Откуда: Москва
Сообщений: 6
Akina,

идею поняла, спасибо!. надо подумать :)
25 фев 15, 18:11    [17311867]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
Если (вдруг, что странно) окажется, что оптимизатор не может эффективно использовать индекс на OR-ах - так и вообще заменить каждый подзапрос на три UNION.
25 фев 15, 18:39    [17311997]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Akina
вдруг, что странно
Именно так и будет, однако!
26 фев 15, 08:57    [17313502]     Ответить | Цитировать Сообщить модератору
 Re: sql задача на сравнение данных в двух таблицах  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
iap
Именно так и будет, однако!
Неоднозначно... иногда используется merge index, иногда нет... а чё голову ломать, есть query analyzer, пусть пыхтит и придумывает рекомендации по набору индексов для (под)запросов. Если на выходе нечто невменяемое - пошинкуем на UNION.
26 фев 15, 09:22    [17313619]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить