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

Откуда: Москва
Сообщений: 902
Есть две таблицы T1 и T2.
CREATE TABLE T1
(
AgentCode VARCHAR(10),
Contrct VARCHAR(16),
StatusCode INT,
WAS_IN_GROUP VARCHAR(9),
MN VARCHAR(4)
)
CREATE TABLE T2
(
MN VARCHAR(4),
Unit VARCHAR(9),
BranchName VARCHAR(100)
)

T1 содержит информацию, о том что агент(AgentCode) продавший договор(Contrct), относится к подразделению (WAS_IN_GROUP) на протяжении месяца(MN).(StatusCode)-последний статус договора. Первичным ключом является Contrct.
T2 содержит информацию, о том что подразделение (Unit) состоит на протяжении месяца в структуре из (BranchName) на протяжении месяца(MN). То есть эта таблица определяет иерархическую структуру.
Пример данных из второй таблицы:
MN unit BranchName
2012-07 006023000 006023000 -->
2012-07 006008000 006023000 --> 006008000 -->
2012-07 003003000 006023000 --> 006008000 --> 003003000 -->
2012-07 003004000 006023000 --> 006008000 --> 003003000 --> 003004000 -->

Мне нужно посчитать распределение статусов проданных договоров в разрезе иерархии и отчетного периода.
Для этого я использую примерно такой скрипт

DECLARE @Agency VARCHAR(9) = '006008000'
SELECT	MN,
		SUM(CASE WHEN StatusCode = 1 THEN 1 ELSE 0 END) AS StatusCode1,
		SUM(CASE WHEN StatusCode = 2 THEN 1 ELSE 0 END) AS StatusCode2,
		SUM(CASE WHEN StatusCode = 3 THEN 1 ELSE 0 END) AS StatusCode3,
		SUM(CASE WHEN StatusCode = 4 THEN 1 ELSE 0 END) AS StatusCode4,
FROM T1 AS DOG
INNER JOIN	T1 AS N
ON	WAS_IN_GROUP = N.unit AND
	DOG.MN = N.MN AND
	N.BranchName LIKE '%' +  @Agency  + '%'
GROUP BY
MN

То есть в результирующий набор попадут как непосредственные продажи 003003000, так и договоры 003004000.

Время работы скрипта для одного @Agency занимает примерно 40 сек, а при переборе курсором всех подразделений переваливает за 1,5 часа.
Помогите с настройкой индекса.
27 мар 13, 17:08    [14103177]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
Yagrus2,
В первом посте описка.
Нужно так:
То есть в результирующий набор попадут как непосредственные продажи 006008000 , так и договоры 003004000, 003003000.
27 мар 13, 17:13    [14103208]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Yagrus2
	N.BranchName LIKE '%' +  @Agency  + '%'

Почему так, а не
	N.BranchName = @Agency
?
27 мар 13, 17:18    [14103240]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Гость333
Yagrus2
	N.BranchName LIKE '%' +  @Agency  + '%'


Почему так, а не
	N.BranchName = @Agency

?
Так BranchName - вон какая колбаса! С миллионом Agency, да со стрелками...
27 мар 13, 17:23    [14103276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
Гость333,
Потому что структура может быть произвольной.
К тому же для 006008000 я должен учитывать:
006023000 --> 006008000 -->
006023000 --> 006008000 --> 003003000 -->
006023000 --> 006008000 --> 003003000 --> 003004000 -->

собственные продажи
продажи по 003003000
продажи по 003003000
27 мар 13, 17:25    [14103291]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
PaulYoung
Member

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

у вас что, структура записана в одно поле со стрелками?
27 мар 13, 17:28    [14103320]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
PaulYoung
Yagrus2,

у вас что, структура записана в одно поле со стрелками?


В исходной структуре эта информация хранится в виде ID, ParID, Name
27 мар 13, 17:29    [14103337]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Гость333
Member

Откуда:
Сообщений: 3683
iap
Так BranchName - вон какая колбаса! С миллионом Agency, да со стрелками...

А, вон оно чо :-)
Я просто не осилил такое форматирование таблицы...
27 мар 13, 17:32    [14103359]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Yagrus2,
автор
Время работы скрипта для одного @Agency занимает примерно 40 сек, а при переборе курсором всех подразделений переваливает за 1,5 часа.

А нельзя реляционным способом записывать branchName ? Например как ссылку на таблицу с древовидной структурой или на табличку вроде (branchid, agancy,order) . С имеющимся способом хранения branchName про использования индекса на этом поле можно забыть.
А потом, зачем курсором перебирать все подразделения? Это же обычный джойн с таблицей подразделений. )))
А так для T1
 create index ix_T1_wasingroup_mn on T1(WAS_IN_GROUP,MN) include(StatusCode)

для T2
create index ix_T2_mn_unit on T1(MN,unit) include(BranchName)

Может быть один из них и будет использоваться
28 мар 13, 10:41    [14105772]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексом  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
Провел такой эксперимент. Создал табличную переменную
DECLARE @unitTable TABLE
(
unit VARCHAR(100)
)

А затем заполнил ее такими данными, для @Agency = '006008000'
INSERT INTO @unitTable
	SELECT	unit 
	FROM         T2 AS N
	WHERE	N.BranchName LIKE '%' +  @Agency  + '%'
	GROUP BY unit

То есть @unitTable состоит из 3 записей:
006008000
003004000
003003000

И модифицировал исходный скрипт на такой:

SELECT	MN,
		SUM(CASE WHEN StatusCode = 1 THEN 1 ELSE 0 END) AS StatusCode1,
		SUM(CASE WHEN StatusCode = 2 THEN 1 ELSE 0 END) AS StatusCode2,
		SUM(CASE WHEN StatusCode = 3 THEN 1 ELSE 0 END) AS StatusCode3,
		SUM(CASE WHEN StatusCode = 4 THEN 1 ELSE 0 END) AS StatusCode4,
FROM T1 AS DOG
INNER JOIN	T2 AS N
ON	WAS_IN_GROUP = N.unit AND
	DOG.MN = N.MN AND
	N.unit IN (SELECT unit  FROM @unitTable)
GROUP BY
MN


Уменьшить время выполнения не получилось, так как из плана запроса следует,
что при сканировании T2 не используется условие на ограничение unit (N.unit IN (SELECT unit FROM @unitTable))

Я предположил что если в соединении со стороны Т2 будет использоваться меньшее кол-во записей, то возможно получить ускорение
28 мар 13, 17:24    [14108922]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить