Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос по структуре табиц  [new]
__ctrl-c__
Guest
Есть главная таблица, записи которой - это накая сушьность, и есть связаная с ней табличка хранящаяя в себе некие свойства этой сущности с датами начала действия этих значений. Задача состоит в том что постоянно нужно запрашивать последние значения из таблицы свойств для сущности, но картина ухудшается тем что сущностей превеликое множество, а записей в свойствах у каждой так же довольно много. Что бы упростить выборку напрашивается индексированная вьюха с топ 1 и ранжированием по дате, но вот фигня - нельзя создать такую вьюху с операторами ранжирования (MAX, MIN, order by).

Может кто то сталкивался с подобными трудностями и находил альтернативное решение оптимизации?

Единственно что пока что приходит в голову - денормализовать схему, вынеся последние значения свойств в саму сущнось.
11 янв 12, 11:52    [11883690]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
__ctrl-c__,

Если свойства сущности, которые стали не актуальными, интересуют только в плане истории, то имхо, можно и вынести их в отдельную таблицу, а все исторические значения соотв.хранить в другой таблице.
А вообще, о чем мы тут говорим, какой порядок кол-ва записей в таблицах, что за таблицы, какой запрос, сколько выполняется и какой скорости надо добиться?
11 янв 12, 12:46    [11884166]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
может так
create table #entity
	(
		id int identity(1,1) primary key,
		entity_name VARCHAR(128)
	)

create table #property
	(
		id int identity(1,1) primary key,
		entity_id int,
		prop_name VARCHAR(128),
		prop_value VARCHAR(128),
		prop_date datetime
	)

insert into #entity (entity_name) values ('Дорога')
insert into #entity (entity_name) values ('Дом')


insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'длина','100км','2011-12-01')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'длина','101км','2011-12-15')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'длина','102км','2011-12-31')

insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'ширина','10м','2011-12-01')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'ширина','11м','2011-12-18')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'ширина','12м','2011-12-31')

insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'толщина покрытия','20см','2011-12-01')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'толщина покрытия','21см','2011-12-11')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (1,'толщина покрытия','22см','2011-12-31')



insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'кол-во жильцов','1','2011-12-01')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'кол-во жильцов','2','2011-12-15')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'кол-во жильцов','4','2011-12-31')

insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'стоимость аренды','10т.р.','2011-12-01')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'стоимость аренды','11т.р.','2011-12-18')
insert into #property (entity_id, prop_name, prop_value, prop_date) values (2,'стоимость аренды','12т.р.','2011-12-31')



--SELECT * FROM #entity
--SELECT * FROM #property

;WITH rp AS 
(
	SELECT 
			entity_id, 
			prop_name, 
			prop_value, 
			prop_date,
			row_number() over (partition by entity_id, prop_name order by prop_date desc) r
	FROM 
			#property
	where 
			prop_date<'2011-12-16'  -- если надо на конкретную дату
)
select 
		* 
from 
		#entity e 
		join 
		rp on entity_id = e.id and r = 1

drop table #entity
drop table #property
11 янв 12, 12:51    [11884231]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Я возможно повторю SomewhereSomehow, но лично я делал табличку с текущими значениями свойств, а на её изменения вешал триггкр который прописывал в историческую таблицу изменения. Вообще из истории бралось всё достаточно быстро, если индекс по объекту и дате начала действия свойств висел.
11 янв 12, 12:52    [11884236]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
jsmirnoww
может так

Прости а зачем свойства в транспонированном виде хранить? - Почему не в виде полей - свойство = поле? Так ведь неудобно искать потомучто все значения в тексте (да и в sql_variant тож мало радости будет), да и чтоб вывести свойство объектов прийдётся снова транспонировать, да и места много больше будет тратится чем с полями.
11 янв 12, 13:00    [11884311]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
-EAV-
Guest
ZOOKABAKODER
jsmirnoww
может так

Прости а зачем свойства в транспонированном виде хранить? - Почему не в виде полей - свойство = поле? Так ведь неудобно искать потомучто все значения в тексте (да и в sql_variant тож мало радости будет), да и чтоб вывести свойство объектов прийдётся снова транспонировать, да и места много больше будет тратится чем с полями.

хотя бы потому, что каждое из значений каждого из свойств может иметь свои даты начала и окончания действия
11 янв 12, 13:14    [11884451]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по структуре табиц  [new]
__ctrl-c__
Guest
всем спасибо за советы, вроде по производительности все решилось. Да, запрос на подобие того что привел jsmirnoww у меня и был, но он отрабатывал ужасно медленно. Я всеже набрался терпения подождал часок пока он отработает и построит план. По итого я перестроил кластерный индекс с варианта (entity_id asc, prop_date asc) на (entity_id asc, prop_date desc), после чего запрос стал укладыватся в 20 секунд.

Еще раз всем спасибо.
12 янв 12, 11:40    [11890860]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить