Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Задача по оптимизации  [new]
hail_835
Guest
Возможно ли избавиться от избыточности идентичных под запросов полей res1 и res2?
@@VERSION = Microsoft SQL Server 2008 (SP2)

DECLARE @t1 table(
	id int identity,
	sotr_fio varchar(255),
	dt_ch datetime,
	dt datetime
)

INSERT INTO @t1(sotr_fio, dt_ch, dt) VALUES
('ИвановИИ', '2012-07-18', '2012-07-18'),
('ИвановИИ', '2012-07-18', '2012-07-19'),
('ИвановИИ', '2012-07-15', '2012-07-19'),
('ПетровПП', '2012-07-17', '2012-07-18'),
('ПетровПП', '2012-07-17', '2012-07-17'),
('ПетровПП', '2012-07-15', '2012-07-25')

select sotr_fio, 
	(
		select TOP 1 t2.id
		from @t1 t2
		where t2.sotr_fio = t1.sotr_fio
		ORDER BY dt_ch, dt DESC
	) res1, 
	(
		select TOP 1 t2.dt
		from @t1 t2
		where t2.sotr_fio = t1.sotr_fio
		ORDER BY dt_ch, dt DESC
	) res2
from @t1 t1
GROUP BY sotr_fio
18 июл 12, 14:57    [12883706]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
hail_835,
+ на пример
with  zz
        as ( select distinct
                    sotr_fio
             from   @t1
           )
  select  *
  from    zz
  cross apply ( select top 1
                        t2.id as res1
                      , t2.dt as res2
                from    @t1 t2
                where   t2.sotr_fio = zz.sotr_fio
                order by dt_ch
                      , dt desc
              ) z
18 июл 12, 15:14    [12883836]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
;WITH CTE1(sotr_fio) AS
(
SELECT DISTINCT sotr_fio FROM @t1
)
SELECT
  C.sotr_fio,
  res.*
FROM
  CTE1 C
OUTER APPLY
  (	
      select TOP 1 t2.id, t2.dt
		from @t1 t2
		where t2.sotr_fio = C.sotr_fio
		ORDER BY dt_ch, dt DESC
	) AS res
18 июл 12, 15:14    [12883840]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
trew
Member

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

select sotr_fio, id as res1, dt as res2
from 
(
select ROW_NUMBER() OVER(partition by sotr_fio order by dt_ch) cn, *
from @t1
) tt
where tt.cn =1
18 июл 12, 15:18    [12883865]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
nezhadnye_my
Guest
select sotr_fio, id, dt
from (
		select *,
			   ROW_NUMBER() over (partition by sotr_fio order by dt_ch, dt DESC) as rn
		from @t1
      )t
where rn = 1      
18 июл 12, 15:21    [12883897]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
hail_835
Guest
Работает, но без прироста производительности. Не хватает FIRST_VALUE добавленной в 2012-м. Может есть еще варианты?
18 июл 12, 15:22    [12883904]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
hail_835
Работает, но без прироста производительности.


План актуальный покажите и структуру таблиц с индексами, ибо не всегда только переписыванием запроса задача решается.
18 июл 12, 15:23    [12883914]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
hail_835
Guest
trew
hail_835,

select sotr_fio, id as res1, dt as res2
from 
(
select ROW_NUMBER() OVER(partition by sotr_fio order by dt_ch) cn, *
from @t1
) tt
where tt.cn =1


То что нужно, СПАСИБО.
18 июл 12, 15:24    [12883917]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
nezhadnye_my
Guest
hail_835,

dt DESC потеряно, но дело Ваше :P
18 июл 12, 15:27    [12883931]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
hail_835
Guest
pkarklin
hail_835
Работает, но без прироста производительности.


План актуальный покажите и структуру таблиц с индексами, ибо не всегда только переписыванием запроса задача решается.


т.к. запрос на деле не такой простой, то я "оценил на глаз" возможно я ошибаюсь. подзапрос TOP 1 в cross apply выполняется для каждой строки (всего 2-е). Самый быстрый вариант подсказал nezhadnye_my похожий но с ошибкой в сортировке trew. Спасибо всем большое, задача решена.
18 июл 12, 15:32    [12883980]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
hail_835
Guest
nezhadnye_my
hail_835,

dt DESC потеряно, но дело Ваше :P

да, я вовремя не увидел ваш вариант
18 июл 12, 15:34    [12883988]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
hail_835,

DECLARE @t1 table(
	id int identity,
	sotr_fio nvarchar(255),
	dt_ch datetime,
	dt datetime
)

INSERT INTO @t1(sotr_fio, dt_ch, dt) VALUES
(N'ИвановИИ', '2012-07-18', '2012-07-18'),
(N'ИвановИИ', '2012-07-18', '2012-07-19'),
(N'ИвановИИ', '2012-07-15', '2012-07-19'),
(N'ПетровПП', '2012-07-17', '2012-07-18'),
(N'ПетровПП', '2012-07-17', '2012-07-17'),
(N'ПетровПП', '2012-07-15', '2012-07-25')

select sotr_fio, 
	(
		select TOP 1 t2.id
		from @t1 t2
		where t2.sotr_fio = t1.sotr_fio
		ORDER BY dt_ch, dt DESC
	) res1, 
	(
		select TOP 1 t2.dt
		from @t1 t2
		where t2.sotr_fio = t1.sotr_fio
		ORDER BY dt_ch, dt DESC
	) res2
from @t1 t1
GROUP BY sotr_fio;


with cteOrdered as
(
select t.*,
       row_number() 
        over (
              partition 
                     by t.sotr_fio
                  order 
                     by t.dt_ch,
                        t.dt DESC
             ) as rowNum
  from @t1 t
)

select *
  from cteOrdered c
 where c.rowNum = 1;
 
 
select c.*--, c_p.*
  from @t1 c
  left
  join @t1 c_p
    on c.sotr_fio = c_p.sotr_fio
   --Check logic because it's starnage (starting from "ORDER BY dt_ch, dt DESC" place when the 1st date doesn't have "desc" operator) 
   and
   (   c_p.dt_ch < c.dt_ch
    or c_p.dt_ch >= c.dt_ch
   and c_p.dt > c.dt
   --it's posible that there is additional case when both dates are equal
   
   )   
   --No sure here. I did it because both dates may have the same value
   and c_p.id > c.id
 where c_p.id is null


Крайний вариант скорее всего самый быстрый, особенно при наличии правильных индексов.

Ваша задача должна решаться не на уровне запроса, а на уровне структуры сущностей. База - полное г, если там есть такое.
Сами себе создаёте сложности. Я бы такую базу непропустил бы.
Не использейте ни один вариант если есть шанс исправить базу и она ваша, а не внешнего заказчика-мудака.
18 июл 12, 15:42    [12884057]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Кстати, если сможите сохранить поле подсчитанное по логике row_number() на уровне таблицы, то будет значительно быстрее (можно сделать триггером скорее) работать запрос.
Но выделить правильно сущности, атрибуты и обратные ссылки на "посдеднии экземпляры дочерних судностей" будет гораздо правильнее. Запросы долдны содеражать join без <> и т. п. Это не орграничение данных по какому-то диапазону.
18 июл 12, 15:55    [12884151]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
hail_835
Guest
NIIIK
Крайний вариант скорее всего самый быстрый, особенно при наличии правильных индексов.

Спасибо проверю, но я чисто визуально за второй вариант он читабельней, возможно скорость выполнения переопределит мой вариант.

NIIIK
База - полное г, если там есть такое.

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


NIIIK
Кстати, если сможите сохранить поле подсчитанное по логике row_number() на уровне таблицы, то будет значительно быстрее (можно сделать триггером скорее) работать запрос.
Но выделить правильно сущности, атрибуты и обратные ссылки на "посдеднии экземпляры дочерних судностей" будет гораздо правильнее. Запросы долдны содеражать join без <> и т. п. Это не орграничение данных по какому-то диапазону.

Для row_number() нужно тогда отдельное поле а это крайний для меня вариант.
18 июл 12, 16:24    [12884344]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
кхекхе2
Guest
hail_835,
а воттак?
select sotr_fio,min(id),min(dt)
from @t1 t1
where datediff(day,dt_ch,dt)=(select max(datediff(day,dt_ch,dt)) from @t1 t2 where t2.sotr_fio=t1.sotr_fio)
group by sotr_fio
18 июл 12, 17:07    [12884701]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
кхекхе2
hail_835,
а воттак?
select sotr_fio,min(id),min(dt)
from @t1 t1
where datediff(day,dt_ch,dt)=(select max(datediff(day,dt_ch,dt)) from @t1 t2 where t2.sotr_fio=t1.sotr_fio)
group by sotr_fio

А вот так не работает на вот таких данных.

INSERT INTO @t1(sotr_fio, dt_ch, dt) VALUES
(N'ИвановИИ', '2012-07-18', '2012-07-18'),
(N'ИвановИИ', '2012-07-18', '2012-07-19'),
(N'ИвановИИ', '2012-07-15', '2012-07-19'),
(N'ПетровПП', '2012-07-17', '2012-07-18'),
(N'ПетровПП', '2012-07-17', '2012-07-17'),
(N'ПетровПП', '2012-07-15', '2012-07-25'),
(N'ПетровПП', '2012-07-14', '2012-07-15')
18 июл 12, 17:22    [12884815]     Ответить | Цитировать Сообщить модератору
 Re: Задача по оптимизации  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
hail_835
Для row_number() нужно тогда отдельное поле а это крайний для меня вариант.


Ну отдельная 1-1 таблица связанная кластерным первичным ключём и т. п.... с нужной колонкой о которой никто не знает.

кхекхе2
hail_835,
а воттак?
select sotr_fio,min(id),min(dt)
from @t1 t1
where datediff(day,dt_ch,dt)=(select max(datediff(day,dt_ch,dt)) from @t1 t2 where t2.sotr_fio=t1.sotr_fio)
group by sotr_fio

Рельно думаете что это реально будет работать с подзапросом для каждой строки в where и функциями которые практически забьют на индексы?

Базу надо делать/оптимизировать а не запросы "оптимизировать".
20 июл 12, 17:02    [12894468]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить