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

Откуда:
Сообщений: 43
Все привет! Не знаю правильно ли я выбрал раздел, ежели нет - перенесите пожалуйста мой топик куда следует...
Я делаю загрузка из таблицы 1 (реализация продукции) в таблицу 2.
CREATE TABLE [dbo].[NomReal](
	[NomReal_Rcd] [uniqueidentifier] NOT NULL, --первичный ключ
	[Nomenkl] [bigint] NULL, --номенклатура
	[QtReal] [decimal](18, 4) NULL,--количество
	[SumOplReal] [decimal](18, 4) NULL,--сумма
	[VesKgReal] [decimal](18, 4) NULL,--вес
	[Dat] [datetime] NULL,--дата
	[GplRcd] [bigint] NULL,--грузполучатель
PRIMARY KEY CLUSTERED 
(
	[NomReal_Rcd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[NomReal] ADD  DEFAULT (newsequentialid()) FOR [NomReal_Rcd]
GO

Уникальных значений в таблице нет, кроме PK, уникальна только связка грузополучатель + номенклатура + дата. Имеется кластерный индекс только по PK. Делаю запрос следующего вида:

select
nom.GplRcd,
sum(case when month(nom.Dat) = 1  then isnull(Nom.SumOplReal,0)end) as Jan,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 2  then isnull(Nom.SumOplReal,0)end) as Feb,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 3  then isnull(Nom.SumOplReal,0)end) as Mar,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 4  then isnull(Nom.SumOplReal,0)end) as Apr,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 5  then isnull(Nom.SumOplReal,0)end) as May,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 6  then isnull(Nom.SumOplReal,0)end) as Jun,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 7  then isnull(Nom.SumOplReal,0)end) as Jul,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 8  then isnull(Nom.SumOplReal,0)end) as Aug,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 9  then isnull(Nom.SumOplReal,0)end) as Sep,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 10 then isnull(Nom.SumOplReal,0)end) as Oct,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 11 then isnull(Nom.SumOplReal,0)end) as Nov,
------------------------------------------------------------------------------------------------
sum(case when month(nom.Dat) = 12 then isnull(Nom.SumOplReal,0)end) as Decem
-------------------------------------------------------------------------------------------------
from NomReal nom 
where nom.Dat >= '2011-01-01' and nom.Dat <= '2011-12-31'
group by nom.GplRcd 

Данные нужно выбирать именно так, то есть на каждый месяц - отдельное поле...Выбираем год - получаем время 17 - 18 сек. (
Строк ~ 4,8 млн. Пробовал разные варианты делал некластерные индексы чуть ли не на все столбцы, результат не менялся, MSSQL в плане выполнения предлагал создать индекс вида
CREATE NONCLUSTERED INDEX [NomReal_Dat] ON [dbo].[NomReal] 
(
	[Dat] ASC
)
INCLUDE ( [Nomenkl],
[SumOplReal],
[GplRcd]) 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]
GO
Результат не изменился..(
Можно ли как - нибудь сократить это время? Оптимизировать запрос или структуру таблицы...
Заранее спасибо! Надеюсь на вашу поддержку!
8 дек 11, 10:06    [11726875]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Enterprise Edition
8 дек 11, 10:15    [11726943]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
Бектуров Роман
Member

Откуда: Новосибирск
Сообщений: 1606
кластреный индекс покороче сделать
8 дек 11, 10:31    [11727043]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
Бектуров Роман
кластреный индекс покороче сделать

Что значит покороче?
8 дек 11, 10:33    [11727057]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
Бектуров Роман
Member

Откуда: Новосибирск
Сообщений: 1606
ну столбец добавте автоинкрементый типа int сделайте кластерный по нему, поглядте, хотя по идее индекс предложенный средой все нужные поля содержит, в табличку запрос ходить не должен, в общем пробовать надо, pivot мож эффективнее будет
8 дек 11, 10:38    [11727092]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
Elena85
Member

Откуда:
Сообщений: 34
Попробуйте индекс по GplRcd,Dat и включите в него SumOplReal. Получите скан, но сэкономите на сортировке...
8 дек 11, 10:46    [11727156]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
Elena85
Попробуйте индекс по GplRcd,Dat и включите в него SumOplReal. Получите скан, но сэкономите на сортировке...

К сожалению результат практически не меняется( Pivot использовать нельзя...
8 дек 11, 10:52    [11727205]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrWhiteJohn
Guest
MrBlackJack,

на [Dat], а [Date], убрать из него время и сделать типом Date, кластерный сделать по нему.
первичный ключ удалять никто не заставляет. сделай некластерным и все.

каких эффектов добавляет кластерный по гуиду - поищи по форуму, последние 3-4 месяца всплывало регулярно.
сиквенчуал, не сиквенчуал - это в принципе бессмысленно. изобретателю иллюзии последовательных гуидов руки бы оторвал. смысл гуида в его бессмысленности и никогданеповторимости.
использование гуида в качестве PK чем-то обосновано? вон в других таблицах - bigint - может и здесь bigint хватит?

автор
Пробовал разные варианты делал некластерные индексы чуть ли не на все столбцы

теперь попробуй почитать что такое индексы и в каких случаях в их существовании вобще есть смысл.

просто ради интереса переделай на пивот (то как ты сделал через кейсы (что тоже имеет право на жизнь), но через PIVOT).
isnull внутри суммы что-то дает? а он работает и жрет процессор. копеешно, но жрет.

зы
лучше убери полосочки, не позорься.
8 дек 11, 10:52    [11727209]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
MrBlackJack
Строк ~ 4,8 млн. Пробовал разные варианты делал некластерные индексы чуть ли не на все столбцы, результат не менялся

Можно ли как - нибудь сократить это время? Оптимизировать запрос или структуру таблицы...


1. Дык с 4,8 млн онлайн бороться можно, тока купив охереннный сервант с охренительным хранилищем.

2. Единственный вариант - подсчет по мере ввода.

3. Следовательно, Indexed View ваш выбор.

4. Тока не нада делать Pivot в Indexed View. Тупая группировка по месяцам.

5. Pivot сделаете в выборке.
8 дек 11, 11:01    [11727274]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
Elena85
Member

Откуда:
Сообщений: 34
покажите план запроса
8 дек 11, 11:03    [11727290]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
Elena85
покажите план запроса


Никакой план не прожует 4.8млн строк за вменяемое время.

alter view [NomReal_monthly] WITH SCHEMABINDING as
select GplRcd, datediff(month, 0, Dat) MonthNumber,  sum(ISNULL(SumOplReal,0)) MonthlySumOplReal, COUNT_BIG(*) CntBig
from dbo.NomReal
group by GplRcd, datediff(month, 0, Dat)

go
create unique clustered index [NomReal_monthly_IDX] on dbo.[NomReal_monthly] (GplRcd, MonthNumber)

go

select
GplRcd,
case when month(dateadd(month, 0, MonthNumber)) = 1  then MonthlySumOplReal end as Jan,
------------------------------------------------------------------------------------------------
case when month(dateadd(month, 0, MonthNumber)) = 2  then MonthlySumOplReal end as Feb,
-------------------------------------------------------------------------------------------------
--...
-------------------------------------------------------------------------------------------------
from [NomReal_monthly] with(NOEXPAND)  -- ну with(NOEXPAND) для гарантии на Ent. Ed. не нужно.
where MonthNumber >= datediff(month, 0, '20110101') and MonthNumber <= datediff(month, 0, '20111231')
8 дек 11, 11:17    [11727413]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
Впрочем

create unique clustered index [NomReal_monthly_IDX] on dbo.[NomReal_monthly] (MonthNumber, GplRcd)


будет лучше.
8 дек 11, 11:19    [11727426]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
aleks2
Elena85
покажите план запроса


Никакой план не прожует 4.8млн строк за вменяемое время.

alter view [NomReal_monthly] WITH SCHEMABINDING as
select GplRcd, datediff(month, 0, Dat) MonthNumber,  sum(ISNULL(SumOplReal,0)) MonthlySumOplReal, COUNT_BIG(*) CntBig
from dbo.NomReal
group by GplRcd, datediff(month, 0, Dat)

go
create unique clustered index [NomReal_monthly_IDX] on dbo.[NomReal_monthly] (GplRcd, MonthNumber)

go

select
GplRcd,
case when month(dateadd(month, 0, MonthNumber)) = 1  then MonthlySumOplReal end as Jan,
------------------------------------------------------------------------------------------------
case when month(dateadd(month, 0, MonthNumber)) = 2  then MonthlySumOplReal end as Feb,
-------------------------------------------------------------------------------------------------
--...
-------------------------------------------------------------------------------------------------
from [NomReal_monthly] with(NOEXPAND)  -- ну with(NOEXPAND) для гарантии на Ent. Ed. не нужно.
where MonthNumber >= datediff(month, 0, '20110101') and MonthNumber <= datediff(month, 0, '20111231')

А что за конструкция datediff(month, 0, Dat) ? Результат выполнения - 1332, 1334 и т.д.?
8 дек 11, 11:28    [11727507]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
MrBlackJack
А что за конструкция datediff(month, 0, Dat) ? Результат выполнения - 1332, 1334 и т.д.?


А чо бы BOL не почитать?
8 дек 11, 11:49    [11727720]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
aleks2,
Данные из View
GplRcd MonthNumber MonthlySumOplReal CntBig
5141332447386.600092
5141333420288.1000105
5141334412360.700095
5141335416326.8000102
5141336399322.500088
5141337391714.500098
5141338406296.900097
5141339410274.4000104
5141340353030.600070


А результат запроса

select
GplRcd,
case when month(dateadd(month, 0, MonthNumber)) = 1  then MonthlySumOplReal end as Jan,
------------------------------------------------------------------------------------------------
case when month(dateadd(month, 0, MonthNumber)) = 2  then MonthlySumOplReal end as Feb
-------------------------------------------------------------------------------------------------
--...
-------------------------------------------------------------------------------------------------
from [NomReal_monthly] with(NOEXPAND)  -- ну with(NOEXPAND) для гарантии на Ent. Ed. не нужно.
where MonthNumber >= datediff(month, 0, '20110101') and MonthNumber <= datediff(month, 0, '20111231')


GplRcd Jan Feb
514nullnull
514nullnull
514nullnull
514nullnull
514nullnull
514nullnull
514nullnull
514nullnull
514nullnull

Если я чего-то не понял - объясните пожалуйста...
8 дек 11, 12:05    [11727934]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
select
nom.GplRcd,
sum(case when month(dateadd(month, 0, MonthNumber)) = 1  then Nom.MonthlySumOplReal end) as Jan,
------------------------------------------------------------------------------------------------
[NomReal_monthly] with(NOEXPAND) nom  
where MonthNumber >= datediff(month, 0, '20110101') and MonthNumber <= datediff(month, 0, '20111231')
group by nom.GplRcd 



Низзя же быть таким несообразительным?
8 дек 11, 12:09    [11727992]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
1
Guest
И на сколько велико различие во времени между запросами?
9 дек 11, 14:18    [11736793]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры таблицы или запроса  [new]
aleks2
Guest
1
И на сколько велико различие во времени между запросами?

Если ты о разнице запроса к индексированному View и исходным запросом к 4.8 млн. строк - то бесконечность.

Indexed View - суть таблица. И выбрать из нее 12 строчек занимает нисколько времени.
9 дек 11, 14:35    [11736975]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить