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

Откуда:
Сообщений: 23
Имеются 2 временные таблицы
A(FKey1 int,PidFkey1 tinyint,Skey int,Kf1 real)
,
диапазон строк таблицы 50-500 млн
диапазон FKey1 int 100k - 2 млн


B(FKey2 int,PidFkey2 tinyint,Skey int,Kf2 real)

диапазон таблицы строк 5-200 млн
диапазон FKey2 int 5k - 200k

диапазон skey 100k - 10 млн
имеются колоночные индексы на обоях таблицах

Нужно получить следующий результат, объединить данные таблиц по полю Skey
Получив С(FKey1 ,FKey2 ,PidFkey1,PidFkey2 ,Kf1,Kf2 )

Следующее получить D(FKey1 ,FKey2 ,kf) :
объединение группировок
FKey1 ,FKey2 ,PidFkey1, MAХ(Kf1)
FKey1 ,FKey2 ,PidFkey2 , MAХ(Kf2)

Последнее получить E(Fkey1,Fkey2,SumKf)
результат группировки Fkey1,Fkey2 отфильтровав по SumKf

Проблемы начинаются когда в плане hash aggregate начинает использоваться tempDB, сейчас решаю проблему используя курсор(подбирая определенные параметры эмпирически) если ли лучшее решение?

Система 12 (c HT) Ядер, 40ГБ Рам, IO маленький
A# - 60 млн
B# - 50 млн
итог: выполнение запроса 10 мин.

With C as (
SELECT   
	Fkey1, 
	Fkey2,      
	PidFkey1,
	PidFkey2, 
	Kf1,
	Kf2
FROM #A  a
	INNER JOIN #B  b
		on a.Skey=b.Skey
)
,D as (
SELECT 
	Fkey1, 
	Fkey2,      
	kf=max(kf1)
FROM C
GROUP BY Fkey1,Fkey2,PidFkey1
union all
SELECT 
	Fkey1, 
	Fkey2,      
	kf=max(kf2)
FROM C
GROUP BY Fkey1,Fkey2,PidFkey2
)
,E as (
SELECT 
	Fkey1, 
	Fkey2,      
	Sumkf=Sum(kf)
FROM C
GROUP BY Fkey1,Fkey2
having Sum(kf)>kf3
8 сен 15, 17:18    [18124367]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
ATI.HeNRy,

Чего-тов запросе недописано
8 сен 15, 17:27    [18124413]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
a_voronin
ATI.HeNRy,

Чего-тов запросе недописано


Это искомый запрос я его никогда в таком виде не запускал, предчувствуя долгую работу.
А Вы имеете виду реализацию курсора?
8 сен 15, 17:49    [18124494]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
А вижу, простите. исправил

With C as (
SELECT
Fkey1,
Fkey2,
PidFkey1,
PidFkey2,
Kf1,
Kf2
FROM #A a
INNER JOIN #B b
on a.Skey=b.Skey
)
,D as (
SELECT
Fkey1,
Fkey2,
kf=max(kf1)
FROM C
GROUP BY Fkey1,Fkey2,PidFkey1
union all
SELECT
Fkey1,
Fkey2,
kf=max(kf2)
FROM C
GROUP BY Fkey1,Fkey2,PidFkey2
)
SELECT
Fkey1,
Fkey2,
Sumkf=Sum(kf)
INTO #C
FROM D
GROUP BY Fkey1,Fkey2
having Sum(kf)>kf3
8 сен 15, 17:55    [18124521]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
В курсоре делаю ограничение на выбор записей из таблицы #B по ключу Fkey2
WHERE Fkey2  between @RangeFkey2 and @RangeFkey2+@Range

вот и здесь получается если задать большой @Range получаем Use tempDB
если маленький теряем время из-за частого обращение к таблицам.
8 сен 15, 21:51    [18125443]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ATI.HeNRy,

Эстимейты то корректные? Просто памяти не хватает?
Сколько памяти использует запрос во время работы?
9 сен 15, 00:32    [18125902]     Ответить | Цитировать Сообщить модератору
 Re: hash aggregate для больших таблиц  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
Mind
ATI.HeNRy,

Эстимейты то корректные? Просто памяти не хватает?
Сколько памяти использует запрос во время работы?


Эстимейты то корректные? Просто памяти не хватает?

ДА

Сколько памяти использует запрос во время работы?
Зависит от диапазона X по которому делаю запрос:

1X Время выполнения - 12 сек
<MemoryGrantInfo SerialRequiredMemory="7168" SerialDesiredMemory="26059536" RequiredMemory="114952" DesiredMemory="26167432" RequestedMemory="8093416" GrantWaitTime="0" GrantedMemory="8093416" MaxUsedMemory="2195456" />


2X Время выполнения - 22 сек
<MemoryGrantInfo SerialRequiredMemory="7168" SerialDesiredMemory="51819728" RequiredMemory="114952" DesiredMemory="51927560" RequestedMemory="8093416" GrantWaitTime="0" GrantedMemory="8093416" MaxUsedMemory="4038656" />


4X Время выполнения - 160 сек
<MemoryGrantInfo SerialRequiredMemory="7168" SerialDesiredMemory="103007792" RequiredMemory="114952" DesiredMemory="103115656" RequestedMemory="8093416" GrantWaitTime="0" GrantedMemory="8093416" MaxUsedMemory="6606848" />
9 сен 15, 15:22    [18128688]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить