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

Откуда: Прага
Сообщений: 247
Добрый день!

У нас в системе есть такая таблица:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PriceMatrix](
	[SupplierID] [int] NOT NULL,
	[DateFrom] [smalldatetime] NOT NULL,
	[Currency] [tinyint] NOT NULL,
	[Stay] [tinyint] NOT NULL,
	[Cost] [money] NOT NULL,
	[OptionID] [int] NOT NULL,
	[Price] [money] NOT NULL,
 CONSTRAINT [PK_PriceMatrix] PRIMARY KEY CLUSTERED 
(
	[SupplierID] ASC,
	[DateFrom] ASC,
	[Stay] ASC,
	[OptionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]


Эта таблица содержит стоимость проживания в гостиницах с разлиными условиями. Всего таких записей получается 123,168,747 и весит эта таблица 20 Гб, что многовато на мой взгляд.

Я посчитал, в файловой системе таблица должна занимать SELECT (convert(bigint,123168747) * convert(bigint,52)) / convert(bigint,1024*1024) = 6108Mb (где 52 - сумма байт, выделяемых под каждый столбец, в зависимости от его типа). Но даже в этом случае получается многовато. С учетом того, что на сервере может одновременно быть 5-10 аналогичных таблиц, то дисковое пространство и память сервера будут расходоваться нерациаонально.

Можно ли как-либо "ужать" эту таблицу? Мне в голову приходит только транспонирование. С учетом того, что значение столбца Stay находится в диапазоне 1-31, то можно добавить 31 столбец в таблицу, сократив тем самым их общее количество и уменьшив количество повторяемых данных. При этом возникает вопрос - если значение записи в таблице NULL, выделяется ли под него место базой данных?

Еще есть идея насчет типа данных money. Под каждое значение резервируется 19 байт. Можно ли заменить money на что-то менее прожорливое? Всем спасибо!
9 янв 14, 16:17    [15391460]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
Glory
Member

Откуда:
Сообщений: 104760
cavalero
Всего таких записей получается 123,168,747 и весит эта таблица 20 Гб,

Где и как вы узнали про 20 Гб ?

cavalero
Я посчитал, в файловой системе таблица должна занимать SELECT (convert(bigint,123168747) * convert(bigint,52)) / convert(bigint,1024*1024) = 6108Mb (где 52 - сумма байт, выделяемых под каждый столбец, в зависимости от его типа). Но даже в этом случае получается многовато. С учетом того, что на сервере может одновременно быть 5-10 аналогичных таблиц, то дисковое пространство и память сервера будут расходоваться нерациаонально.

Считать надо так, как предлагант хелп Designing and Implementing Structured Storage (Database Engine) > Databases > Designing Databases >
9 янв 14, 16:21    [15391502]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
cavalero,

money можете поменять на int если уверены что максимальная сумма всегда меньше 20 миллионов (с учетом что последние 2 цифры это копейки) либо на bigint если не уверены.
А с каких это пор 20 Гб стало многовато?:)
9 янв 14, 16:31    [15391617]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
Glory
Member

Откуда:
Сообщений: 104760
Вот вы задали FILLFACTOR = 80 для кластерного индекса просто так или с каким то смыслом ?
9 янв 14, 16:34    [15391646]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Glory
Где и как вы узнали про 20 Гб ?


Reports->Disk Usage By top tables

#Records:123,030,117; Reserved(KB): 19,751,424; Data(KB): 19,625,792; Indexes(KB):117,592; Unused(KB):8,0240

Glory
Считать надо так, как предлагант хелп Designing and Implementing Structured Storage (Database Engine) > Databases > Designing Databases >


Да, если смотреть сюда, то я посчитал неверно, но всё таки даже с учетом того что я понял, разница между тем что должно быть и тем что есть не должна быть больше чем в 3 раза.
9 янв 14, 16:36    [15391672]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Glory
Вот вы задали FILLFACTOR = 80 для кластерного индекса просто так или с каким то смыслом ?


Не уверен, что приследовал этим какую-то цель. Возможно это значение по умолчанию.
9 янв 14, 16:39    [15391705]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
Glory
Member

Откуда:
Сообщений: 104760
cavalero
разница между тем что должно быть и тем что есть не должна быть больше чем в 3 раза.

Если на каждой странице данных окажется по одной записи, то разница будет во много раз больше
9 янв 14, 16:41    [15391732]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
П-Л
Guest
1000 гостниц
в каждой 20 разных опций проживания
каждая опция меняет свою цену 20 раз
400 000 записей - очень средний размер таблички.
Требуемая матрица - функция даты, вычисляется через то, что для каждой цены действия опции есть From .. To. Можно получать нужные сечения матрицы - по гостиницам, по опциям и т.п. Можно взять массив дат и сгенерировать матрицу за каждый день.
9 янв 14, 16:42    [15391735]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
Glory
Member

Откуда:
Сообщений: 104760
cavalero
Glory
Вот вы задали FILLFACTOR = 80 для кластерного индекса просто так или с каким то смыслом ?


Не уверен, что приследовал этим какую-то цель. Возможно это значение по умолчанию.

Тогда самое время прочитать в хелпе про фрагментацию индексов и про способы ее устранения
9 янв 14, 16:42    [15391740]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
o-o
Guest
cavalero,

расчет неправильный, т.к. Вы как минимум не учитываете Page Header на каждой странице
и row offset под каждую запись, + таблица кластерная, где расчет для навигационного дерева?
Вы же только листовой уровень (недо)посчитали.

если Enterprise Edition, то можно включить ROW COMPRESSION,
будет предпринята попытка из данных типа fixed length сделать variable length,
есть "оценивающая" процедура, можно посчитать, стОит ли заморачиваться:
sp_estimate_data_compression_savings
9 янв 14, 16:44    [15391765]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
WarAnt
cavalero,

money можете поменять на int если уверены что максимальная сумма всегда меньше 20 миллионов (с учетом что последние 2 цифры это копейки) либо на bigint если не уверены.
А с каких это пор 20 Гб стало многовато?:)


Система мультивалютная, 20 миллионов бел рублей это вполне достижимый результат :)

20 ГБ многовато в том случае, когда очевидно, что это излишнее требование. На 10 баз это уже 200гб, которые надо бэкапировать и тд.
9 янв 14, 16:45    [15391779]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
П-Л
1000 гостниц
в каждой 20 разных опций проживания
каждая опция меняет свою цену 20 раз
400 000 записей - очень средний размер таблички.
Требуемая матрица - функция даты, вычисляется через то, что для каждой цены действия опции есть From .. To. Можно получать нужные сечения матрицы - по гостиницам, по опциям и т.п. Можно взять массив дат и сгенерировать матрицу за каждый день.



Не понял, если честно. Я считаю так, исходя из текущей БД: 300 гостиниц * (в среднем 30 вариантов размещения в гостинице) * 31 день * 365(ценник на год) = 101835000 записей в базе. Это именно матрица цен с проживанием от-до.

Просто сделать матрицу цен за каждый день можно. Однако в этом случае вы не сможете обрабатывать всякие спец условиятипа "в таком то периоде 1 день бесплатно, а вот тут минимум 3 дня можно жить" и таких условий - миллион. За приемлемое время вы "по запросу" ответ на вопрос "сколько стоит" не выдадите. Именно поэтому мы используем матрицу цен. Занимает она много, нужно уменьшить размер.
9 янв 14, 17:00    [15391968]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
o-o
cavalero,

расчет неправильный, т.к. Вы как минимум не учитываете Page Header на каждой странице
и row offset под каждую запись, + таблица кластерная, где расчет для навигационного дерева?
Вы же только листовой уровень (недо)посчитали.

если Enterprise Edition, то можно включить ROW COMPRESSION,
будет предпринята попытка из данных типа fixed length сделать variable length,
есть "оценивающая" процедура, можно посчитать, стОит ли заморачиваться:
sp_estimate_data_compression_savings


Да, расчет неправильный, согласен. Версия SQL - Web Edition, компрессия отпадает.

Судя по всему, если я сокращу количество записей, то это хорошо должно помочь.
9 янв 14, 17:03    [15392015]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Еще есть идея насчет типа данных money. Под каждое значение резервируется 19 байт.

это вы откуда взяли? 8 байт всю жизнь было.
9 янв 14, 17:05    [15392042]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Glory
Тогда самое время прочитать в хелпе про фрагментацию индексов и про способы ее устранения


Total fragmentation = 2.53%, есть мнение, что фрагментировать нечего. Кстати, я привел пример создания таблицы с development сервера, на продакшне всё таки 90 стоит.
9 янв 14, 17:08    [15392072]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
daw
> Еще есть идея насчет типа данных money. Под каждое значение резервируется 19 байт.

это вы откуда взяли? 8 байт всю жизнь было.


Ваша правда! Я смотрел на значение в byte при создании индекса для этого поля, в этом случае пишется 19.
9 янв 14, 17:10    [15392099]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Total fragmentation = 2.53%, есть мнение, что фрагментировать нечего.

гм. есть мнение, что это значение - совсем не то, что вы думаете.
Page fullness сколько показывает?
9 янв 14, 17:20    [15392184]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
daw, 89.64 %
9 янв 14, 17:22    [15392198]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
А если сжатие включить? Или версия сервера не позволяет?

З.Ы. 60 Gb для таблицы - это нынче смешно, если честно.

Сообщение было отредактировано: 9 янв 14, 17:33
9 янв 14, 17:26    [15392230]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Гавриленко Сергей Алексеевич, не позволяет, версия Web Edition. Размер то небольшой, но и его можно избежать, есть мнение.
9 янв 14, 17:29    [15392242]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
cavalero,

а Maximum row size/Minimum row size ?
9 янв 14, 17:32    [15392265]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
хмхмхм
Guest
cavalero
При этом возникает вопрос - если значение записи в таблице NULL, выделяется ли под него место базой данных?


Поля фиксированной длины всегда занимают место согласно своей декларации для любых значений
9 янв 14, 17:38    [15392304]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
daw, 143/143
9 янв 14, 17:43    [15392359]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> daw, 143/143

кто-то на заполненной таблице баловался добавлением/удалением not null столбцов?
отребилдите кластерный индекс - и будет счастье вам.
9 янв 14, 17:45    [15392391]     Ответить | Цитировать Сообщить модератору
 Re: Сократить количество записей в таблице  [new]
П-Л
Guest
cavalero
Не понял, если честно. Я считаю так, исходя из текущей БД: 300 гостиниц * (в среднем 30 вариантов размещения в гостинице) * 31 день * 365(ценник на год) = 101835000 записей в базе. Это именно матрица цен с проживанием от-до.

Просто сделать матрицу цен за каждый день можно. Однако в этом случае вы не сможете обрабатывать всякие спец условиятипа "в таком то периоде 1 день бесплатно, а вот тут минимум 3 дня можно жить" и таких условий - миллион. За приемлемое время вы "по запросу" ответ на вопрос "сколько стоит" не выдадите. Именно поэтому мы используем матрицу цен. Занимает она много, нужно уменьшить размер.

Вот именно с таким подходом я и не согласен. Как мне подсказывает опыт покупки всяческих там туров цена опций проживания меняется всего несколько раз в год. На таком количестве данных получить требуемую информацию на любой конкретный день запросм, табличной функцией - простой BETWEEN при том что вариант (опция) фиксирован. Т.е. по моему убеждению вы решаете не ту задачу, что надо.
9 янв 14, 17:50    [15392434]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить