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

Откуда:
Сообщений: 8
Привет всем!

Столкнулся с непонятной проблемой с хранением данных на mssql 2012:

Для простоты в базе всего 1 таблица, с единственной колонкой типа int.
Прикидываем приблизительный размер базы при инсерте 1 млрд записей
1*4 байта=4 Гбайт (+ понятно, что затраты на структуру, статистики и пр.)

файл получается 14 Гбайт, т.е. ровно в 3.5 раза больше!

тогда инсерт в колонку типа binary (4) (строго 4 байта)
файл снова 14 Гбайт

тогда инсерт в колонку типа binary (5) (дополнительный байт сел правильно)
файл 15 Гбайт

тогда инсерт в колонку типа binary (1)
файл 12 Гбайт

1 Гбайт вероятно это честные данные, а вот что занимает остальные 11 Гбайт - мне совершенно непонятно :(

exec sp_spaceused:
database_size=13962.19 MB
unallocated space=0.46 MB
reserved=14294760 KB
data=14291640 KB
index_size=1008 KB
unused=2112 KB

т.е. излишек получается около 10байт/запись
отчего это так, и можно ли как-то с этим бороться?
17 ноя 16, 19:43    [19905189]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Текст запроса на создание таблицы приведите.
17 ноя 16, 19:49    [19905206]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Каким образом вставляете данные?
17 ноя 16, 19:51    [19905211]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Верблюд,

create table testtable (binary (1))

declare @a int

set @a=0

while @a<1000000000
begin
insert into testtable
select 0x01
set @a=@a+1
end
17 ноя 16, 19:56    [19905224]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Гавриленко Сергей Алексеевич,

не зря же спросили Вы -

а от метода вставки разве что-то может зависеть?
разве тут не структура-приемник важнее?
17 ноя 16, 19:59    [19905234]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
o-o
Guest
Снэпшот на базе включен?
Это 14 лишних байт на запись
17 ноя 16, 20:08    [19905249]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
o-o,

нет, не включен
обычная свежесозданная базка, collation=Cyrillic_General_CS_AS
17 ноя 16, 20:12    [19905256]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
https://msdn.microsoft.com/en-us/library/ms175991.aspx
17 ноя 16, 20:15    [19905265]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
jellyfish
create table testtable (binary (1))
Вы не пересказывайте, а сделайте скрипт структуры.
17 ноя 16, 20:16    [19905266]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Гавриленко Сергей Алексеевич
jellyfish
create table testtable (binary (1))
Вы не пересказывайте, а сделайте скрипт структуры.


Он наверное не знает. Поэтому ссылку дал, пусть сам считает
17 ноя 16, 20:17    [19905272]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Гавриленко Сергей Алексеевич,

USE [d3]
GO

/****** Object: Table [dbo].[testtable] Script Date: 17.11.2016 20:18:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[testtable](
[col] [binary](1) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
17 ноя 16, 20:19    [19905277]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Верблюд
Гавриленко Сергей Алексеевич
пропущено...
Вы не пересказывайте, а сделайте скрипт структуры.


Он наверное не знает. Поэтому ссылку дал, пусть сам считает


Спасибо за содержательный ответ, посчитать разумеется это невозможно.
17 ноя 16, 20:22    [19905288]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
jellyfish
Верблюд
пропущено...


Он наверное не знает. Поэтому ссылку дал, пусть сам считает


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


Да ладно? Там по ссылке все подробно расписано.
17 ноя 16, 20:28    [19905305]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'd3'), OBJECT_ID(N'testtable'), NULL, NULL, 'DETAILED')


9 байт одна запись в таблице у тебя занимает
17 ноя 16, 20:32    [19905319]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
------------------------ ------------------------ ------------------------
9 9 9
17 ноя 16, 20:33    [19905324]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
o-o
Guest
Чего-то все повелись на то, что размер преувеличен, а он нормальный. У меня есть куча, 1 поле bigint, 10млн записей, это 164Мб.
У него в 100 раз больше строк, у меня бы вышло 16гиг, но ведь bigint.
Нефиг 4 байта умножать, в строке overhead где-то 10 и есть
17 ноя 16, 20:33    [19905326]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
jellyfish
Верблюд
пропущено...


Он наверное не знает. Поэтому ссылку дал, пусть сам считает


Спасибо за содержательный ответ, посчитать разумеется это невозможно.
А вы попробуйте в следующий раз.

Считаем размер по формулам из ссылки:

declare
    @null_bitmap    int = 2 + (1 + 7) / 8                           -- Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
declare
    @row_size       int = 1 + @null_bitmap + 4                      -- Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
declare
    @Rows_Per_Page  int = 8096 / (@row_size + 2)                    -- Rows_Per_Page = 8096 / (Row_Size + 2)
declare
    @Num_Pages int = 1000000 / @Rows_Per_Page                    -- Num_Pages = Num_Rows / Rows_Per_Page
declare
    @heap_size bigint = cast (8192 as bigint) * @Num_Pages          -- 8192 x Num_Pages
select
    @null_bitmap
    , @row_size
    , @Rows_Per_Page
    , @Num_Pages
    , heap_size_mb = @heap_size / 1024. /1024.

                                                heap_size_mb
----------- ----------- ----------- ----------- ---------------------------------------
3           8           809         1236        9.65625000000


Проводим натурный эксперимент:

set nocount on
go
use tempdb
go
if object_id( 'tempdb..testtable' ) is not null
    drop table testtable

create table testtable (a binary (1))

declare @a int

set @a=0

while @a<1000000
begin
insert into testtable
select 0x01

set @a=@a+1
end

exec sp_spaceused 'testtable'

go
name                                                                                                                             rows                 reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
testtable                                                                                                                        1000000              11512 KB           11432 KB           56 KB              24 KB

В итоге расчетный и реальный размер отличаются не очень сильно.
17 ноя 16, 20:38    [19905336]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Ясно, значит ничего не сделаешь.
Спасибо всем кто помог разбраться!
17 ноя 16, 20:42    [19905353]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
jellyfish
Ясно, значит ничего не сделаешь.
Если все ваши числа запихать в несколько varbinary (max), то будет меньше. К примеру.

Сообщение было отредактировано: 17 ноя 16, 20:51
17 ноя 16, 20:51    [19905381]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Гавриленко Сергей Алексеевич
jellyfish
Ясно, значит ничего не сделаешь.
Если все ваши числа запихать в несколько varbinary (max), то будет меньше. К примеру.


проще в файл сложить тогда и запаковать :)
17 ноя 16, 20:56    [19905396]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Верблюд
Гавриленко Сергей Алексеевич
пропущено...
Если все ваши числа запихать в несколько varbinary (max), то будет меньше. К примеру.


проще в файл сложить тогда и запаковать :)
Все зависит от задачи, которая не была озвучена.
17 ноя 16, 20:59    [19905412]     Ответить | Цитировать Сообщить модератору
 Re: Хранение данных в базе  [new]
jellyfish
Member

Откуда:
Сообщений: 8
Гавриленко Сергей Алексеевич
jellyfish
Ясно, значит ничего не сделаешь.
Если все ваши числа запихать в несколько varbinary (max), то будет меньше. К примеру.


Так даже одного достаточно, спасибо Вам большое, попробую!
17 ноя 16, 21:42    [19905602]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить