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

Откуда:
Сообщений: 76
Переписываю за кем то запросы давних времен.. которые после заполнения таблицы информцией по 20-30 млн записей стали работать медленно.
Sql2012 Standart и sql2014 standart

Задача такая.
Есть таблица справочник.
Есть таблица событий.
Нужно подтянуть из таблицы событий пару полей 1к1.
Для примера возьмем что критерий нужной записи из таблицы событий это последняя запись по времени.

предыдущим разработчиком использовался такой вот принцип с подзапросами и TOP 1
как раз он и приводит на реальных данных сейчас к тормозам

SELECT * FROM tMain tm
INNER JOIN
(
	SELECT tf.main_id, 
			(SELECT TOP 1 child.fgk_id
				FROM tFgk child 
			WHERE child.main_id=tf.main_id 
			ORDER BY child.date_Added desc, child.fgk_id desc) AS fgk_id
	    FROM tFgk AS tf
	GROUP BY tf.main_id) AS Inf ON tm.main_id=Inf.main_id
	INNER JOIN tFgk AS tf ON tf.fgk_id=Inf.fgk_id


Заменил на оконные функции, чтобы уменьшить вложенность подзапросов

SELECT * FROM tMain tm
	INNER JOIN (
           SELECT tf.main_id,
                  tf.add_info,
                  tf.date_Added,
                  tf.fgk_id,
                  MAX(tf.fgk_id) OVER(PARTITION BY main_id, date_Added) AS max_fgk_id,
                  MAX(tf.date_Added) OVER(PARTITION BY main_id) AS max_date_Added
           FROM   tFgk AS tf
       ) AS Inf ON tm.main_id=Inf.main_id
WHERE  
	fgk_id = max_fgk_id AND 
	date_Added=max_date_Added


Правда потом остудил чуток мозг и решил обойтись not exists
SELECT * FROM tMain tm
	INNER JOIN tFgk AS tf ON tf.main_id=tm.main_id
WHERE NOT EXISTS 
(SELECT null 
	FROM tFgk child 
     WHERE child.main_id=tf.main_id AND (child.date_Added>tf.date_Added or child.fgk_id>tf.fgk_id))
ORDER BY tf.main_id


В тестовом примере он оказался эффективнее.
однако в реальной задаче еще есть куча ограничивающих условий, что делает итоговый запрос слишком громоздким.

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

+ Генерация тестовых данных.


DROP TABLE tFgk
DROP TABLE tMain
GO
CREATE TABLE tMain
(
	main_id INT identity (1,1) PRIMARY KEY,
	NAME VARCHAR(100) NOT null
)

CREATE TABLE tFgk
(
	fgk_id INT IDENTITY(1,1) PRIMARY KEY,
	main_id INT NOT NULL FOREIGN KEY REFERENCES tMain (main_id),
	date_Added datetime not null default GETDATE(),
	add_info VARCHAR(MAX)	
)
GO

INSERT tMain (NAME)
SELECT top 10 cast(OBJECT_ID AS VARCHAR(MAX))+type FROM sys.all_objects AS o
ORDER BY NEWID()


declare @i int=7
WHILE @i>0
BEGIN
	INSERT INTO tFgk
	(
		main_id,
		add_info
	)
	SELECT top 15
		(SELECT TOP 1 main_id FROM tMain where main_id<>o.[object_id] ORDER BY NEWID()) 
		,cast(OBJECT_ID AS VARCHAR(MAX))+TYPE
		 FROM sys.all_objects AS o
	ORDER BY NEWID()
	
	SET @i=@i-1
END
7 май 16, 00:34    [19144701]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите еще идей, как обеспечить выборку 1к1 записи из таблицы событий...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
apply + top 1 + нужные индексы
7 май 16, 00:56    [19144766]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите еще идей, как обеспечить выборку 1к1 записи из таблицы событий...  [new]
Добрый Э - Эх
Guest
Dzianis,

Простая задача
7 май 16, 04:26    [19145001]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите еще идей, как обеспечить выборку 1к1 записи из таблицы событий...  [new]
aleks2
Guest
with tf as ( select *, row_number() over( partition by main_id order by date_Added DESC, fgk_id DESC) as n from tFgk )
select * 
  from tMain tm INNER JOIN tf ON tf.main_id=tm.main_id
  where tf.n = 1


Только ЗАЧЕМ?
7 май 16, 06:14    [19145014]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите еще идей, как обеспечить выборку 1к1 записи из таблицы событий...  [new]
Dzianis
Member

Откуда:
Сообщений: 76
aleks2
Только ЗАЧЕМ?


Как зачем? ;)) Надо )
По банальному допустим поиск описания последних заказов для каждого клиента.

Спасибо.
Вариант с row_number выпал из головы.
10 май 16, 16:51    [19154346]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить