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

Откуда:
Сообщений: 35
Здравствуйте. Помогите, пожалуйста, оптимизировать таблицу и произвести необходимые настройки для ускорения обработки запроса. Имеется таблица, всего строк в таблице порядка 800 млн.
Столбцы в таблице



Symbol принимает около 20-30 значений.
TYPE принимает два значения
MOMENT – это дата/время формата = 20110531190016223. Принимает 70 млн значений
ID принимает 80 млн значений
ACTION принимаем 3 значения
PRICE не считал сколько значений может принимать. Наверное, несколько десятков тысяч.
VOLUME принимает порядка 1000 значений
ID_DEAL принимает несколько млн значений

Я пробовал создавать индексы по MOMENT, VOLUME, ID. Никакого существенного прироста скорости это не дало, вместо 5.40, запрос обрабатывается 5.10 . Подскажите, пожалуйста, как можно ускорить работу?
Работаю в 2008 MS SQL. После создания таблицы никаких добавлений в неё не планируется.
Индекс делал не уникальный, некластеризованный.


Запрос такой.


Select
d, dtime, max(sum_b) sum_b, max(sum_s) sum_s, max(sum_b) - max(sum_s) diff
from (select
d, dtime, type,
case when type = 'B' then sum(vol) else null end sum_b,
case when type = 'S' then sum(vol) else null end sum_s
from (select first.TYPE, (cast(left(second.MOMENT, 8) as bigint))D,(cast(left(second.MOMENT, 12) as bigint)/5*5)dtime, sum(cast(second.VOLUME as int))vol
from (select TYPE, ID, ACTION, MOMENT, VOLUME from
[база where VOLUME < 20 and ACTION = 1)first,
(select TYPE, ID, ACTION, MOMENT, VOLUME,ID_DEAL from
[база] where VOLUME < 20 and ACTION = 2)second
where first.ID = second.ID
group by (cast(left(second.MOMENT, 8)as bigint)), cast(left(second.MOMENT, 12) as bigint)/5*5, first.TYPE) a
group by d, dtime, type) b
group by d, dtime
order by dtime

К сообщению приложен файл. Размер - 10Kb
28 янв 12, 19:09    [11989560]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
evus
Member

Откуда: Москва
Сообщений: 208
AlexeyGor, попробуйте bitmap-индекс по полю ACTION, возможно станет чуть.
Индекс по MOMENT же не поможет с данным запросом, поскольку в запросе нигде это поле не участвует операциях фильтрации данных.
В целом объем данных нешуточный и нужно каким-то образом поработать над идеями выборки данных, возможно анализировать не весь набор данных, а какими-то частями, зависящами от времени - день, месяц... В таком случае Вам поможет партиционирование по полю MOMENT, но не в чистом виде, а в преобразованном к необходимой единице анализа - день, месяц...
28 янв 12, 21:57    [11989874]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
AlexeyGor
Подскажите, пожалуйста, как можно ускорить работу?
Условие "VOLUME < 20" всегда такое?

Сочетание ACTION и ID уникальное?
28 янв 12, 22:41    [11990009]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
Коляныч
Member

Откуда:
Сообщений: 279
AlexeyGor,

Запрос шибко кудрявый, без поллитры не разобрать... вообще начать бы неплохо вот с чего - выкинуть индекс по MOMENT т.к. абсолютно бесполезны. Ни в одном условии нет чистого сравнения по MOMENT, значит индекс не подхватится. Можно сделать три вычисляемых поля по таблице: D=cast(left(second.MOMENT, 8)as bigint)), dtime=(cast(left(second.MOMENT, 12) as bigint)/5*5), vint=cast(second.VOLUME as int) просто чтобы сделать запрос более читабельным.
Select 
	d,
	dtime,
	max(sum_b) sum_b,
	max(sum_s) sum_s,
	max(sum_b) - max(sum_s) diff
from
(
	select
		d,
		dtime,
		type,
		case when type = 'B' then sum(vol) else null end sum_b,
		case when type = 'S' then sum(vol) else null end sum_s
	from
	(
		select
			first.TYPE,
			second.D,
			second.dtime,
			sum(second.vint) vol
		from
		(
			select
				TYPE,
				ID
			from [база]
			where
				VOLUME < 20 and ACTION = 1
		) first,
		(
			select
				ID,
				D,
				dtime,
				vint
			from [база]
			where
				VOLUME < 20 and ACTION = 2
		) second
		where
			first.ID = second.ID
		group by D, dtime, first.TYPE
	) a
	group by d, dtime, type
) b
group by d, dtime
order by dtime


Дальше видно, что всё начинается с джойна, поэтому для пущей читабельности нужно переписать запрос:

Select 
	d,
	dtime,
	max(sum_b) sum_b,
	max(sum_s) sum_s,
	max(sum_b) - max(sum_s) diff
from
(
	select
		d,
		dtime,
		type,
		case when type = 'B' then sum(vol) else null end sum_b,
		case when type = 'S' then sum(vol) else null end sum_s
	from
	(
		select
			first.TYPE,
			second.D,
			second.dtime,
			sum(second.vint) vol

		from
			[база] first
		inner join
			[база] second
		on
			first.ID = second.ID
		where
			first.VOLUME < 20 and fist.ACTION = 1 and
			second.VOLUME < 20 and second.ACTION = 2
		group by second.D, second.dtime, first.TYPE
	) a
	group by d, dtime, type
) b
group by d, dtime
order by dtime


Сразу напрашиваются два индекса: оба по ID, первый с фильтром VOLUME < 20 and ACTION = 1 и прицепленной колонкой TYPE , второй с фильтром VOLUME < 20 and ACTION = 2 и прицепленными колонками D, dtime, vint.

Прицепленные колонки это Included Columns, фильтр это Filter - всё свойства индексов 2008.

Дальше по простому уже никак не ускорить, только если к материализованным вьюхам переходить
28 янв 12, 22:55    [11990049]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
AlexeyGor
Member

Откуда:
Сообщений: 35
alexeyvg
Условие "VOLUME < 20" всегда такое?

Сочетание ACTION и ID уникальное?


VOL < 20 - нет, может меняться. 30-40-50 и тд.

Сочетание ACTION и ID уникальное? - нет.
29 янв 12, 12:48    [11991125]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
AlexeyGor
Сочетание ACTION и ID уникальное? - нет.
Да, плохо :-(

Можно попробовать индекс по ACTION, VOLUME, ID + include на TYPE, MOMENT, ID_DEAL

И нужно сделать табличку поменьше, для экспериментов, милионов на 10 строк, и пробовать разные варианты...
29 янв 12, 13:17    [11991198]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
AlexeyGor
Member

Откуда:
Сообщений: 35
alexeyvg,

Я вот что подумал. Сейчас мой запрос обрабатывается на тестовой базе в 90 млн строк за 5 минут.
На полной базе обрабатывается 30 минут. 5 минут это нормальное время для обработки запроса.
Тестовая база отличается от полной, тем, что в тестовой один #SYMBOL, а в полной порядка 50 #SYMBOL.

В запросе всегда буду обращаться только к одному #SYMBOL. Значит ли это, что можно выполнить запрос за те же 5 минут, как и когда в базе находился один #SYMBOL, если сделать партиционирование?

Сам запрос немного изменится, будет where volume .. action и #SYMBOL = 'Нужный инструмент'

Я начал делать, но видимо что-то неправильно сделал.

create partition function symbol_RI (varchar (4))
as range left
for values ('RIM1','RIU1','RIH1','RIZ1' ... указал не все инструменты, пока написал только несколько, к которым буду обращаться)

пишет

Внимание! Список значений диапазона для функции секционирования "test" не отсортирован по значению. Сопоставление секций файловым группам во время выполнения инструкции CREATE PARTITION SCHEME будет использовать эти сортированные граничные значения, если на функцию "test" есть ссылка в инструкции CREATE PARTITION SCHEME.

создание схемы

create partition scheme symbol_RTSi
as partition symbol_RI all to ([primary])

Схема секционирования "testsheme" успешно создана. "PRIMARY" помечена как следующая используемая файловая группа в схеме секционирования "testsheme".


Вопрос - поможет ли партиционирование в этом случае, и как правильно его сделать, поечему пишет такие примечания.
29 янв 12, 15:09    [11991577]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
AlexeyGor
Member

Откуда:
Сообщений: 35
Коляныч
AlexeyGor,
...

[/src]



Спасибо, я попробую!
29 янв 12, 15:11    [11991583]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
AlexeyGor
В запросе всегда буду обращаться только к одному #SYMBOL. Значит ли это, что можно выполнить запрос за те же 5 минут, как и когда в базе находился один #SYMBOL, если сделать партиционирование?
Фильтр по #SYMBOL - конечно, важная часть запроса.

Можно попробовать его включить на первое место в индексе, или действительно сделать секционирование.
AlexeyGor
Вопрос - поможет ли партиционирование в этом случае
Если план будет сканирующим, то поможет, но если будет поиск по индексу, то не сильно - поиск от размера таблицы слабо зависит, только от того, сколько данных нужно найти.
AlexeyGor
поечему пишет такие примечания.
Просто предупреждает о ошибках при перечислении значений. Лучьше перечислять значения по порядку, что бы не было предупреждений.
29 янв 12, 17:10    [11991970]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
Коляныч
Member

Откуда:
Сообщений: 279
AlexeyGor
alexeyvg
Условие "VOLUME < 20" всегда такое?

Сочетание ACTION и ID уникальное?


VOL < 20 - нет, может меняться. 30-40-50 и тд.


тогда в примере можо было так и написать VOLUME < @treshold. Значит его зашить в фильтр не можем, тогда в общем первое - это переписать запрос. Было:

declare @v int = 20
select
	first.TYPE,
	(cast(left(second.MOMENT, 8) as bigint))D,
	(cast(left(second.MOMENT, 12) as bigint)/5*5)dtime,
	(cast(second.VOLUME as int))vol
from
(select TYPE, ID, ACTION, MOMENT, VOLUME from ordlog where VOLUME < @v and ACTION = 1)first, 
(select TYPE, ID, ACTION, MOMENT, VOLUME,ID_DEAL from ordlog where VOLUME < @v and ACTION = 2)second
where first.ID = second.ID
go


Должно стать
ALTER TABLE ordlog ADD
	D  AS (cast(left(MOMENT, 8) as bigint)),
	dtime  AS (cast(left(MOMENT, 12) as bigint)/5*5)


declare @v int = 20
select
	first.TYPE,
	second.D,
	second.dtime,
	second.VOLUME
from ordlog first
inner join ordlog second on first.ID = second.ID
where
	first.VOLUME < @v and first.ACTION = 1 and second.VOLUME < @v and second.ACTION = 2


Если вообще не строить индексов, то в плане будет два раза простой табличный скан с хэшджойном. Нужно заставить скуль сразу выбирать нужные тебе данные. Можно либо два разных индекса создать, чтобы он для рызных ветвей first и second он мог соответствующий удобный выбрать и заюзать, но т.к. условия селекта очень похожие, то можно и одним индексом на двоих обойтись.

CREATE INDEX IX_ordlog_action_volume ON ordlog (ACTION, VOLUME)
INCLUDE (TYPE, D, dtime)
GO


после добавления такого индекса во что превратился план исполнения запроса? Появились в нем index seek два раза? Если не появились, нужен скриншот того что там появилось в плане и побольше подробностей. Короче пока не заставишь джойниться результат двух seek-ов по индексам не пытайся чё-то там дальше заоптимизировать. Во всякие секционирования не нужно лезть, они для оптимизации совсем других задач придуманы
29 янв 12, 18:01    [11992163]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
Коляныч
Member

Откуда:
Сообщений: 279
Коляныч,

неправильно в предыдущем постинге: про ID совсем забыл в индексе. Правильно так:

CREATE INDEX IX_ordlog_action_volume_id ON ordlog (ACTION, VOLUME, ID)
INCLUDE (TYPE, D, dtime)
GO
29 янв 12, 18:55    [11992374]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение обработки запроса  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
2 ТС:
А какой смысл в Varchar(1) и текстовой цене ?
30 янв 12, 12:53    [11995573]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить