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

Откуда:
Сообщений: 1197
Привет

Есть такой вот индекс

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20130912-113653] ON [dbo].[Test]
(
	[IsDeleted] ASC,
	[SaleDate] ASC,	
	[ItemQty] ASC,
	[UnitPrice] ASC,	
	[SalesAmt] ASC,
	[InsertDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO


когда делаю просто SELECT TOP 1000 из таблицы по всем столбцам из индекса, то работает всё быстро.
если делаю SELECT TOP 1000 с MAX(InsertDate) с группировкой по другим столбцам начинает дико тормозить. Данных в таблице порядка 40млн.

План показывает 96% времени идет на сортировку. Причем EstimatedOperatorCost = 3500.

Что может быть?
13 май 14, 16:59    [16011283]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
Glory
Member

Откуда:
Сообщений: 104751
relief
Что может быть?

Чтобы вычислить MAX(InsertDate) придется перебрать все записи. И отсортировать их

Сообщение было отредактировано: 13 май 14, 17:03
13 май 14, 17:03    [16011305]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
iap
Member

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

что-то сказать можно только глядя на запрос
13 май 14, 17:03    [16011307]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
Что может быть?

Чтобы вычислить MAX(InsertDate) придется перебрать все записи. И отсортировать их



А как решить проблему?
13 май 14, 17:08    [16011331]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
iap
relief,

что-то сказать можно только глядя на запрос


удалил лишние поля. если MAX(tt.InsertDate) заменить на InsertDate, то всё летает


SELECT tt.SaleDate, tt.Category, tt.ProductGroupDetail, MAX(tt.InsertDate) as InsertDate
FROM 
(
	SELECT    bds.SaleDate, 
		CASE
		 WHEN bds.SaleType IN (1, 3) THEN ISNULL(bm.ProductGroup, 'Error') 
		 WHEN bds.SaleType = 2 THEN 'A' 
		 WHEN bds.SaleType = 4 THEN 'B' 
		 END AS Category, bds.InsertDate, ISNULL(bm.ProductGroupDetail, '') as ProductGroupDetail
	FROM            dbo.RawSales AS bds WITH (NOLOCK) LEFT JOIN
							 dbo.Models AS bm WITH (NOLOCK) ON bds.ModelCd  = bm.MODEL_CD
	WHERE        (bds.IsDeleted = 0)
) as tt
GROUP BY tt.SaleDate, tt.Category, tt.ProductGroupDetail
13 май 14, 17:09    [16011345]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
Glory
Member

Откуда:
Сообщений: 104751
relief
А как решить проблему?

Наверное нужен индекс, где InsertDate будет первым полем, а не 6-ым
И еще нужен текст запроса
13 май 14, 17:10    [16011350]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
А как решить проблему?

Наверное нужен индекс, где InsertDate будет первым полем, а не 6-ым
И еще нужен текст запроса


сделал отдельный некластерный индекс только InsertDate, но проблему не решает.
Или надо сделать аналог кластерному со всеми полями, только InsertDate первое поле?

текст запроса выложил выше
13 май 14, 17:12    [16011363]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Glory
relief
А как решить проблему?

Наверное нужен индекс, где InsertDate будет первым полем, а не 6-ым
И еще нужен текст запроса
Можно и показанный индекс заставить работать,
но для этого надо в запросе использовать все поля этого индекса,
ибо InsertDate стоит в нём на последнем месте.
13 май 14, 17:13    [16011370]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
iap
Glory
пропущено...

Наверное нужен индекс, где InsertDate будет первым полем, а не 6-ым
И еще нужен текст запроса
Можно и показанный индекс заставить работать,
но для этого надо в запросе использовать все поля этого индекса,
ибо InsertDate стоит в нём на последнем месте.


а они все используются в запросе. я просто их удалил для компактного вида.
Сорри, что не учел это.
Но даже если пишу в запросе WITH(INDEX(ClusteredIndex-20130912-113653)) всё равно не помогает
13 май 14, 17:24    [16011435]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
Glory
Member

Откуда:
Сообщений: 104751
relief
сделал отдельный некластерный индекс только InsertDate, но проблему не решает.

Мда. Как бы было здорово, если сделал индекс и любой запрос стал его использовать.

relief
текст запроса выложил выше

GROUP BY tt.SaleDate, tt.Category, tt.ProductGroupDetail
Вот скажите, а как серверу то использовать индекс, если в нем нет полей из запроса ?
И где TOP 1000 ?

Сообщение было отредактировано: 13 май 14, 17:26
13 май 14, 17:25    [16011443]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
сделал отдельный некластерный индекс только InsertDate, но проблему не решает.

Мда. Как бы было здорово, если сделал индекс и любой запрос стал его использовать.

relief
текст запроса выложил выше

GROUP BY tt.SaleDate, tt.Category, tt.ProductGroupDetail
Вот скажите, а как серверу то использовать индекс, если в нем нет полей из запроса ?
И где TOP 1000 ?


сделал, чтобы все поля из индекса выбирались в селекте. не лучше стало.
в аттаче план запроса приложил

К сообщению приложен файл (1_Anonymized.sqlplan - 23Kb) cкачать
13 май 14, 17:41    [16011565]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
Glory
Member

Откуда:
Сообщений: 104751
relief
сделал, чтобы все поля из индекса выбирались в селекте. не лучше стало.

И как же вы поеметили в индекс вычислямое поле tt.Category ? А вычисляемое ProductGroupDetail ?
13 май 14, 17:43    [16011579]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
сделал, чтобы все поля из индекса выбирались в селекте. не лучше стало.

И как же вы поеметили в индекс вычислямое поле tt.Category ? А вычисляемое ProductGroupDetail ?


а они из другой таблицы.
но там тоже есть индекс, который используется в запросе по плану

REATE NONCLUSTERED INDEX [NonClusteredIndex-20140403-141324] ON [dbo].[Model] 
(
	[ProductGroup] ASC
)
INCLUDE ( [MODEL_CD],
[ProductGroupDetail]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
13 май 14, 18:02    [16011692]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
UH
Member

Откуда: Kyiv
Сообщений: 149
relief,

Попробуйте для начала заставить работать индекс (IsDeleted, SaleDate, SaleType, ProductGroupDetail, ModelCd, InsertDate) для основной выборки, ограничив необх. кол-вом записей (исходя из задачи, для получения TOP N нужно выбрать минимум 4N), а потом уже добавлять остальное. Где-то так:

SELECT   SaleDate
       , ProductGroupDetail
       , Category
       , InsertDate = MAX(InsertDate)
FROM (SELECT TOP 1000  SaleDate
             , ProductGroupDetail = ISNULL(bm.ProductGroupDetail, '')
             , Category = CASE bds.SaleType
                            WHEN 1 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 2 THEN 'A'
                            WHEN 3 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 4 THEN 'B'
                          END
             , InsertDate
      FROM (SELECT TOP 4000 SaleDate, SaleType, ProductGroupDetail, ModelCd, MAX(InsertDate) as InsertDate
            FROM RawSales bds (NOLOCK) 
            WHERE IsDeleted = 0
            GROUP BY SaleDate, SaleType, ProductGroupDetail, ModelCd
            ) q1
            LEFT JOIN dbo.Models bm (NOLOCK) ON q1.ModelCd  = bm.MODEL_CD
      ) q2
GROUP BY SaleDate, ProductGroupDetail, Category
13 май 14, 18:15    [16011761]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
relief
Member

Откуда:
Сообщений: 1197
UH
relief,

Попробуйте для начала заставить работать индекс (IsDeleted, SaleDate, SaleType, ProductGroupDetail, ModelCd, InsertDate) для основной выборки, ограничив необх. кол-вом записей (исходя из задачи, для получения TOP N нужно выбрать минимум 4N), а потом уже добавлять остальное. Где-то так:

SELECT   SaleDate
       , ProductGroupDetail
       , Category
       , InsertDate = MAX(InsertDate)
FROM (SELECT TOP 1000  SaleDate
             , ProductGroupDetail = ISNULL(bm.ProductGroupDetail, '')
             , Category = CASE bds.SaleType
                            WHEN 1 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 2 THEN 'A'
                            WHEN 3 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 4 THEN 'B'
                          END
             , InsertDate
      FROM (SELECT TOP 4000 SaleDate, SaleType, ProductGroupDetail, ModelCd, MAX(InsertDate) as InsertDate
            FROM RawSales bds (NOLOCK) 
            WHERE IsDeleted = 0
            GROUP BY SaleDate, SaleType, ProductGroupDetail, ModelCd
            ) q1
            LEFT JOIN dbo.Models bm (NOLOCK) ON q1.ModelCd  = bm.MODEL_CD
      ) q2
GROUP BY SaleDate, ProductGroupDetail, Category


так он - индекс - работает. работает даже когда джойн делаешь. падает, когда InsertDate меняю на MAX(InsertDate )
13 май 14, 18:20    [16011786]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с индексом  [new]
Glory
Member

Откуда:
Сообщений: 104751
relief
падает, когда InsertDate меняю на MAX(InsertDate )

Т.е.
SELECT   SaleDate
       , ProductGroupDetail
       , Category
FROM (SELECT TOP 1000  SaleDate
             , ProductGroupDetail = ISNULL(bm.ProductGroupDetail, '')
             , Category = CASE bds.SaleType
                            WHEN 1 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 2 THEN 'A'
                            WHEN 3 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 4 THEN 'B'
                          END
             , InsertDate
      FROM (SELECT TOP 4000 SaleDate, SaleType, ProductGroupDetail, ModelCd, MAX(InsertDate) as InsertDate
            FROM RawSales bds (NOLOCK) 
            WHERE IsDeleted = 0
            GROUP BY SaleDate, SaleType, ProductGroupDetail, ModelCd
            ) q1
            LEFT JOIN dbo.Models bm (NOLOCK) ON q1.ModelCd  = bm.MODEL_CD
      ) q2
GROUP BY SaleDate, ProductGroupDetail, Category

работает быстро
а
SELECT   SaleDate
       , ProductGroupDetail
       , Category
       , InsertDate = MAX(InsertDate)
FROM (SELECT TOP 1000  SaleDate
             , ProductGroupDetail = ISNULL(bm.ProductGroupDetail, '')
             , Category = CASE bds.SaleType
                            WHEN 1 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 2 THEN 'A'
                            WHEN 3 THEN ISNULL(bm.ProductGroup, 'Error') 
                            WHEN 4 THEN 'B'
                          END
             , InsertDate
      FROM (SELECT TOP 4000 SaleDate, SaleType, ProductGroupDetail, ModelCd, MAX(InsertDate) as InsertDate
            FROM RawSales bds (NOLOCK) 
            WHERE IsDeleted = 0
            GROUP BY SaleDate, SaleType, ProductGroupDetail, ModelCd
            ) q1
            LEFT JOIN dbo.Models bm (NOLOCK) ON q1.ModelCd  = bm.MODEL_CD
      ) q2
GROUP BY SaleDate, ProductGroupDetail, Category

сразу "падает" ?

И зачем два раза GROUP BY ?
И почему TOP без ORDER BY ?
14 май 14, 09:24    [16013683]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить