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

Откуда: Москва
Сообщений: 31
Всем добрый день.
Возникла проблема - при большом количестве таблиц уходит слишком много времени на подсчет, что является не приемлемым.

Ниже приведен код процедуры. Пояснения
- процедура выбирает количество записей по статусам в каждом проекте. Заранее количество и перечень проектов не известны - становятся известны в момент обращения в зависимости от пользователя.
- проект = отдельная таблица в БД
- в каждой таблице надо отсечь не нужные записи, при этом нужные записываются в #Temp в рамках выполнения запроса к каждой таблице и связью inner join с #Temp мы тем самым отсекаем не нужные
- в процессе выполнения @sql идет запись данных каждого проекта в #TB и в конце мы из него получаем значения все сразу.

Код:
+

declare @sql nvarchar(max)
create table #TB (Project int, Status_id int, CountAnketa int)
create table #Temp (Id int)

	select @sql = coalesce(@sql,'') + '
		declare @Per' + cast(P.Id as varchar(10)) + ' int, @Activ' + cast(P.Id as varchar(10)) + ' int
		select @Per' + cast(P.Id as varchar(10)) + ' = max(Id) from [Application].[ProjectPeriods] where [ProjectId]=' + cast(P.Id as varchar(10))  + '
		select @Activ' + cast(P.Id as varchar(10)) + ' = [ActivityGroupId] FROM [Application].[FormsData] where [ProjectId]=' + cast(P.Id as varchar(10))  + '
			insert into #Temp select [ContainerRowId] from [Application].[ProjectActivites] where [ActivityGroupId]=@Activ' + cast(P.Id as varchar(10)) + ' and [ProjectPeriodId]=@Per' + cast(P.Id as varchar(10)) + '
		insert into #TB
		select ''' + cast(P.Id as varchar(10)) + ''' as Project, FS.Id as Status_id, count(PF.id) as CountAnketa
		from ' + EC.name  + ' PF right join
			[Application].[FormStatuses] FS on PF.Status_id=FS.Id and Period_Id=@Per' + cast(P.Id as varchar(10)) + '
		where FS.Id in (1,2,3,4,7,8) and (Deleted = 0 OR Deleted is null)
				and BaseItem_id in (select Id from #Temp)
		group by FS.Id
		delete #Temp
		'
	from	[Application].[Projects] P inner join
			[Application].[FormsData] FD on P.Id=FD.ProjectId inner join
			[DynamicObjects].[EntityContainers] EC on FD.ContainerId=EC.Id
	
	exec (@sql)

SELECT Id, Name, IsActive, Photo, AlternativeName, [1],[2],[3],[4],[7],[8]
	FROM 
	(select P.Id, P.Name, case P.IsActive when 1 then '' else 'Проект в архиве' end as IsActive, 
			P.Photo, '' as 'AlternativeName', #TB.Status_id, #TB.CountAnketa
	from [Application].Projects P inner join
		#TB on P.Id=#TB.Project
	) D
	PIVOT
	(
		AVG(CountAnketa) for Status_id in ([1],[2],[3],[4],[7],[8])
	) as PVT
	order by Name

drop table #Temp
drop table #TB



На 40 проектов тратится около 2 секунд, на 180 более 1,5 минуты.

Для примера привожу то, что формируется в @sql для пары проектов:
+
declare @Per19 int, @Activ19 int
select @Per19 = max(Id) from [Application].[ProjectPeriods] where [ProjectId]=19
select @Activ19 = [ActivityGroupId] FROM [Application].[FormsData] where [ProjectId]=19
	insert into #Temp select [ContainerRowId] from [Application].[ProjectActivites] where [ActivityGroupId]=@Activ19 and [ProjectPeriodId]=@Per19
insert into #TB
select '19' as Project, FS.Id as Status_id, count(PF.id) as CountAnketa
from [TestProject].[ProjectForms] PF right join
	[Application].[FormStatuses] FS on PF.Status_id=FS.Id and Period_Id=@Per19
where FS.Id in (1,2,3,4,7,8) and (Deleted = 0 OR Deleted is null)
		and BaseItem_id in (select Id from #Temp)
group by FS.Id
delete #Temp
		
declare @Per20 int, @Activ20 int
select @Per20 = max(Id) from [Application].[ProjectPeriods] where [ProjectId]=20
select @Activ20 = [ActivityGroupId] FROM [Application].[FormsData] where [ProjectId]=20
	insert into #Temp select [ContainerRowId] from [Application].[ProjectActivites] where [ActivityGroupId]=@Activ20 and [ProjectPeriodId]=@Per20
insert into #TB
select '20' as Project, FS.Id as Status_id, count(PF.id) as CountAnketa
from [Hortex].[ProjectForms] PF right join
	[Application].[FormStatuses] FS on PF.Status_id=FS.Id and Period_Id=@Per20
where FS.Id in (1,2,3,4,7,8) and (Deleted = 0 OR Deleted is null)
		and BaseItem_id in (select Id from #Temp)
group by FS.Id
delete #Temp


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

Отмечу сразу, что собирать данные в какую то промежуточную таблицу и обновлять ее раз в N времени не подходит, т.к. требуется всегда видеть самые свежие, актуальные данные.
28 июн 16, 17:54    [19346976]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
i-i
Guest
формирование строки select @sql = coale... за какое время происходит?
28 июн 16, 18:02    [19347010]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

Откуда: Москва
Сообщений: 31
i-i
формирование строки select @sql = coale... за какое время происходит?

При 180 проектах - 2 сек.
28 июн 16, 18:05    [19347020]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Вы не пробовали задавать ваш вопрос тому, кто придумал такую схему данных?

З.Ы. Сделать сверху вашей кучи таблиц представление, выкинуть динамику нафиг и вибирать данные одним запросом.
28 июн 16, 18:05    [19347021]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Есть идеи как это можно оптимизировать?


обновлять ее раз в N времени не подходит


Если добавляется мало, но часто, напишите асинхронные триггеры с использованием ServiceBroker. В очередь брокера выбросите XML, а аналитика потом разгребет, не торопясь, в отчётные таблицы, консолидированные по всем проектам.

Иначе вы своим получением свежих данных положите работу пользователей, работающих с проектами.
28 июн 16, 18:17    [19347071]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

Откуда: Москва
Сообщений: 31
Гавриленко Сергей Алексеевич
Вы не пробовали задавать ваш вопрос тому, кто придумал такую схему данных?

З.Ы. Сделать сверху вашей кучи таблиц представление, выкинуть динамику нафиг и вибирать данные одним запросом.


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

А каким образом представление даст возможность увеличить скорость? Ведь фактически представление будет делать те же самые запросы, только не зависимо от того сколько таблиц надо просчитать оно будет сначала считать все, а потом отдавать нужное. Разве не так?
28 июн 16, 18:17    [19347075]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

Откуда: Москва
Сообщений: 31
Сижу тут пытаюсь что то сделал, и убрать колонку Photo - это логотип проекта.
180 проектов стало отрабатывать за 18 сек.

Что то можно с этим сделать? Видимо время съедает именно перетягивание картинки?
28 июн 16, 18:18    [19347080]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
aleks2
Guest
Даже если ты напишешь
create table #Temp (Id int primary key)
и заменишь
and BaseItem_id in (select Id from #Temp)
на
inner join


будет уже быстрее.

А ужо если вытащишь ненужный join из под группировки - ваще запорхает.
28 июн 16, 18:40    [19347153]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

Откуда: Москва
Сообщений: 31
aleks2
Даже если ты напишешь
create table #Temp (Id int primary key)
и заменишь
and BaseItem_id in (select Id from #Temp)
на
inner join


будет уже быстрее.

А ужо если вытащишь ненужный join из под группировки - ваще запорхает.


Так и сделал - действительно скорость раза в 3 увеличилась.
Но не совсем понял про не нужный join - это который из них?
28 июн 16, 18:56    [19347210]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
aleks2
Guest
 PF right join
28 июн 16, 19:17    [19347299]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

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

Спасибо!
29 июн 16, 09:42    [19348515]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
BioVR
Member

Откуда: Москва
Сообщений: 31
Всем спасибо!
Удалось оптимизировать скорость до 8 сек.
29 июн 16, 11:43    [19349142]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро обработать большое количество таблиц?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
BioVR
- проект = отдельная таблица в БД


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



единственно нормальная идея -перепроектировать твою бд, все проекты надо класть в одну таблицу .

после этого жизнь станет легкой и пушистой.

да, еще нужно оторвать что-нибудь тому, кто это придумал, ну или просто уволить.
29 июн 16, 11:52    [19349199]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить