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

Откуда: у меня столько мыслей?
Сообщений: 761
Существуют две одинаковых таблицы:
Artciles (
ArticleCode varchar(40), -- обозначение предмета
VersionNumber int, -- номер версии предмета
VersionState int -- состояние предмета (=0 - предмет не используется, можно сказать удалён из системы, = 1- предмет используется в системе)
)
Одна таблица находится на сервере MSSQL, вторая - на сервере Oracle
На стороне MSSQL функционирует приложение, которое работает с предметами (создаёт новые версии предметов, изменяет состояние версий предметов), Необходимо все эти изменения перекидывать в БД Oracle (то есть если в оракле нет предмета из MSSQL, то его туда надо записать, если версия предмета в MSSQL больше, чем в оракле, то надо изменить версию предмета в Оракле и если состояния одинаковых версий предметов в разных БД не одинаковы, то также надо их изменить)
Ну тоесть по структуре таблицы одинаковые, по содержимому различны - в MSSQL хранится информация о всех вресиях предмета, в оракле храним информацию только о максимальной версии предмета.

Для выборки данных на запрос в Оракл получился запрос, в котором есть и union и куча одинаковых джойнов. В общем, довольно монстрообразный. Но никак сократить его не получается. Есть ли у кого-нибудь мысли по его оптимизации?
select d.ArticleCode, d.VersionNumber, d.VersionState, 'insert' as TypeAction
from (
  select m.ArticleCode, m.VersionNumber
  from (SELECT ArticleCode, max(VersionNumber) as VersionNumber FROM Articles group by ArticleCode) m
    inner join (
      select distinct ArticleCode from Articles 
      where OrderCode not in (SELECT ArticleCode FROM OPENQUERY(ORADB, 'SELECT ArticleCode FROM Articles'))
    ) n on n.ArticleCode=m.ArticleCode
) p
  inner join Articles d on d.ArticleCode=p.ArticleCode and d.VersionNumber=p.VersionNumber
union
select r.ArticleCode, r.VersionNumber, r.VersionState, 'update' as TypeAction
from (
  select a.ArticleCode, a.VersionNumber, a.VersionState
  from
    (SELECT ArticleCode, max(VersionNumber) as VersionNumber FROM Articles group by ArticleCode) v
    inner join Articles a on a.ArticleCode=v.ArticleCode and a.VersionNumber=v.VersionNumber
) r
  inner join (SELECT ArticleCode, VersionNumber, VersionState FROM OPENQUERY(ORADB, 'SELECT distinct ArticleCode, VersionNumber, VersionState FROM Articles')) t
  on t.ArticleCode=r.ArticleCode
    and (  
      t.VersionNumber<>r.VersionNumber
      or (t.VersionNumber=r.VersionNumber and t.VersionState<>r.VersionState)
    )

В верхней части запроса выбираем из MSSQL предметы, которых нет в Оракле (алиас n), из списка максимальных версий предметов (алиас m) выбираем версии для предметов из n (получаем алиса p) и уже потом в итоге добавляем к полученным данным состояние каждого предмета из алиаса d
В нижней части определяем максимальные версии предметов в MSSQL (алиас v) добаляем к ним из алиаса "а" состояния выбранных версий предметов (получаем алиас r) параллельно формируем всю информацию о предметах в оракле (алиас t) и выбираем из "R" предметы, у которых версия или состояние не соответствует данным в оракле
8 фев 16, 08:27    [18785604]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
Mr.Fontaine
Member

Откуда: у меня столько мыслей?
Сообщений: 761
Забыл указать версию MSSQL: 7.0
8 фев 16, 08:28    [18785608]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Mr.Fontaine
Забыл указать версию MSSQL: 7.0
Вы нас пугаете!
8 фев 16, 10:50    [18786118]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Mr.Fontaine
В верхней части запроса выбираем из MSSQL предметы, которых нет в Оракле (алиас n), из списка максимальных версий предметов (алиас m) выбираем версии для предметов из n (получаем алиса p) и уже потом в итоге добавляем к полученным данным состояние каждого предмета из алиаса d
В нижней части определяем максимальные версии предметов в MSSQL (алиас v) добаляем к ним из алиаса "а" состояния выбранных версий предметов (получаем алиас r) параллельно формируем всю информацию о предметах в оракле (алиас t) и выбираем из "R" предметы, у которых версия или состояние не соответствует данным в оракле

Мне кажется или вам просто нужно выбрать все записи Artciles плюс показать записи из Oracle, если они есть ?
8 фев 16, 10:54    [18786148]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
Mr.Fontaine
Member

Откуда: у меня столько мыслей?
Сообщений: 761
Glory, мне нужно найти изменённые и добавленные записи в MSSQL. Запрос с этой задачей справляется. Просто он какой-то трёхэтажный:
- сначала приходится определять версию предмета (есть правило, что работа проводится только с последней версией, все предыдущие версии предметов являются архивными, для работы со старыми остатками),
- затем джойнить с этой же таблицей, чтоб определить состояние этой версии,
- а уж потом сравнивать с оракловыми данными.
Не нравится трёхэтажность, но другого ничего придумать не смог. Да и с MSSQL 7.0 сталкиваюсь практически первый раз.
8 фев 16, 11:07    [18786218]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Mr.Fontaine
- сначала приходится определять версию предмета (есть правило, что работа проводится только с последней версией, все предыдущие версии предметов являются архивными, для работы со старыми остатками),
- затем джойнить с этой же таблицей, чтоб определить состояние этой версии,
- а уж потом сравнивать с оракловыми данными.
Не нравится трёхэтажность, но другого ничего придумать не смог. Да и с MSSQL 7.0 сталкиваюсь практически первый раз.

Тогда не надо рассказывать о выбранном вами способе реализации.
Покажите данные таблиц и конечный результат для них.
8 фев 16, 11:10    [18786244]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
select
 a.ArticleCode, a.VersionNumber, b.ArticleState,
 case
  when c.ArticleCode is null then 'insert'
  when c.VersionNumber <> a.VersionNumber or c.VersionState <> b.VersionState then 'update'
 end as TypeAction
from
 (select ArticleCode, max(VersionNumber) as VersionNumber from Articles group by ArticleCode) a join
 Articles b on b.ArticleCode = a.ArticleCode and b.VersionNumber = a.VersionNumber left join
 openquery(ORADB, 'SELECT ArticleCode, VersionNumber, VersionState FROM Articles') c on c.ArticleCode = a.ArticleCode
where
 c.ArticleCode is null or
 c.VersionNumber <> a.VersionNumber or
 c.VersionState <> b.VersionState
8 фев 16, 11:15    [18786289]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
Mr.Fontaine
d оракле храним информацию только о максимальной версии предмета.

Зачем тогда:
FROM OPENQUERY(ORADB, 'SELECT distinct ArticleCode, VersionNumber, VersionState FROM Articles')) t
В этой таблице ведь ArticleCode уникальный?

И получится что то вроде этого:
select d.ArticleCode, d.VersionNumber, d.VersionState, 
       CASE
           WHEN o.ArticleCode IS NULL THEN 'insert' 
           WHEN o.VersionNumber<>m.VersionNumber OR o.VersionState<>m.VersionState IS NULL THEN 'update' 
       END as TypeAction
from (
       SELECT ArticleCode, OrderCode,  max(VersionNumber) as VersionNumber 
       FROM Articles group by ArticleCode, OrderCode
    ) as m
    LEFT JOIN (
       SELECT ArticleCode, VersionNumber, VersionState 
       FROM OPENQUERY(ORADB, 'SELECT distinct ArticleCode, VersionNumber, VersionState FROM Articles') as q
    ) as o
        ON o.ArticleCode=m.OrderCode
WHERE
    o.ArticleCode IS NULL
    OR o.VersionNumber<>m.VersionNumber 
    OR o.VersionState<>m.VersionState
8 фев 16, 11:30    [18786411]     Ответить | Цитировать Сообщить модератору
 Re: Упросить запрос  [new]
Mr.Fontaine
Member

Откуда: у меня столько мыслей?
Сообщений: 761
invm, спасибо. Про сase что-то не подумал. Сначала сделал запрос на обновление данных, а потом только понял, что могут быть и новые данные и слепил новый запрос, что бы отметить тип 'insert', которые потом связан через union.
Вообще этот тип ('insert', 'update') родился уже при связывании двух отдельно сделанных запросов. Изначально в запросах было по три поля
8 фев 16, 11:58    [18786656]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить