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

Откуда: Москва
Сообщений: 4894
SQL 2014

Для тех, кто идёт вперед. Протестировал COLUMNSTORE_ARCHIVE

Исходные таблицы -- партиционированный по дням колумнстор. Данные за 5 дней -- 5 партиций. (СКРИПТ ПРИВЕДЕН ДЛЯ ИНФОРМАЦИИ, НЕ ИСПОЛНЯЕТСЯ САМ ПО СЕБЕ)


+
/****** Object: Table [dbo].[Stock_Full] Script Date: 17.06.2015 13:17:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Stock_Full_testColumnStoreArchive](
[id] [int] NULL,
[id_doc] [int] NOT NULL,
[dt] [datetime] NULL,
[pk_date] [int] NOT NULL,
[chrt_id] [int] NOT NULL,
[TS_id] [smallint] NULL,
[NM_id] [int] NULL,
[IMT_id] [int] NULL,
[brand_cod] [int] NULL,
[subject_id] [int] NULL,
[quantity] [int] NULL,
[days_on_site] [int] NULL,
[days_from_Accept] [int] NULL,
[id_stock_age_days] [int] NULL,
[supplier_id] [int] NULL,
[cost_price] [numeric](15, 3) NULL,
[price] [numeric](15, 3) NULL,
[Price_Range_Id] [int] NULL,
[WhPrice] [numeric](18, 3) NULL,
[predorder_id] [int] NULL,
[preorder_collection_id] [int] NULL
) ON [Daily2015PartitioningSchema]([pk_date])

GO

/****** Object: Index [ICS_Stock_Full] Script Date: 17.06.2015 13:17:43 ******/
CREATE CLUSTERED COLUMNSTORE INDEX [ICS_Stock_Full_testColumnStoreArchive] ON [dbo].[Stock_Full_testColumnStoreArchive] WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ON [Daily2015PartitioningSchema]([pk_date])
GO



/****** Object: Table [dbo].[Stock_Full] Script Date: 17.06.2015 13:17:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Stock_Full_testColumnStore](
[id] [int] NULL,
[id_doc] [int] NOT NULL,
[dt] [datetime] NULL,
[pk_date] [int] NOT NULL,
[chrt_id] [int] NOT NULL,
[TS_id] [smallint] NULL,
[NM_id] [int] NULL,
[IMT_id] [int] NULL,
[brand_cod] [int] NULL,
[subject_id] [int] NULL,
[quantity] [int] NULL,
[days_on_site] [int] NULL,
[days_from_Accept] [int] NULL,
[id_stock_age_days] [int] NULL,
[supplier_id] [int] NULL,
[cost_price] [numeric](15, 3) NULL,
[price] [numeric](15, 3) NULL,
[Price_Range_Id] [int] NULL,
[WhPrice] [numeric](18, 3) NULL,
[predorder_id] [int] NULL,
[preorder_collection_id] [int] NULL
) ON [Daily2015PartitioningSchema]([pk_date])

GO

/****** Object: Index [ICS_Stock_Full] Script Date: 17.06.2015 13:17:43 ******/
CREATE CLUSTERED COLUMNSTORE INDEX [ICS_Stock_Full_testColumnStore] ON [dbo].[Stock_Full_testColumnStore] WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [Daily2015PartitioningSchema]([pk_date])
GO




Заполнение данными

+

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

INSERT INTO [dbo].[Stock_Full_testColumnStoreArchive]
SELECT * FROM [dbo].[Stock_Full]
WHERE pk_date BETWEEN 20150501 AND 20150505

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

INSERT INTO [dbo].[Stock_Full_testColumnStore]
SELECT * FROM [dbo].[Stock_Full]
WHERE pk_date BETWEEN 20150501 AND 20150505

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO



Результат

SQL Server parse and compile time:
CPU time = 83 ms, elapsed time = 83 ms.
Table 'Stock_Full'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 805820, lob physical reads 1, lob read-ahead reads 1668769.
Table 'Stock_Full_testColumnStoreArchive'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 13074011)

(строк обработано: 1)

SQL Server Execution Times:
CPU time = 125345 ms, elapsed time = 127316 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 63 ms, elapsed time = 70 ms.
Table 'Stock_Full'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 854388, lob physical reads 0, lob read-ahead reads 1524806.
Table 'Stock_Full_testColumnStore'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 13074011)

(строк обработано: 1)

SQL Server Execution Times:
CPU time = 126580 ms, elapsed time = 206479 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


Статистика наполнения

EXEC sp_spaceused '[dbo].[Stock_Full_testColumnStoreArchive]'
EXEC sp_spaceused '[dbo].[Stock_Full_testColumnStore]'


name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
Stock_Full_testColumnStoreArchive 13074011 247712 KB 243072 KB 0 KB 4640 KB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
Stock_Full_testColumnStore 13074011 396192 KB 392008 KB 0 KB 4184 KB


Выборка


SET STATISTICS IO	ON 
SET STATISTICS TIME ON 
GO 

SELECT * INTO #t3 FROM [dbo].[Stock_Full_testColumnStoreArchive] 
WHERE pk_date BETWEEN 20150501 AND 20150505

SET STATISTICS IO	OFF 
SET STATISTICS TIME OFF 
GO 

SET STATISTICS IO	ON 
SET STATISTICS TIME ON 
GO 

SELECT * INTO #t4 FROM [dbo].[Stock_Full_testColumnStore]  
WHERE pk_date BETWEEN 20150501 AND 20150505

SET STATISTICS IO	OFF 
SET STATISTICS TIME OFF 
GO 


Результат

SQL Server parse and compile time:
CPU time = 234 ms, elapsed time = 291 ms.
Table 'Stock_Full_testColumnStoreArchive'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 597217, lob physical reads 1, lob read-ahead reads 672967.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 13074011)

(строк обработано: 1)

SQL Server Execution Times:
CPU time = 33212 ms, elapsed time = 6573 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 219 ms, elapsed time = 525 ms.
Table 'Stock_Full_testColumnStore'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 784921, lob physical reads 0, lob read-ahead reads 1753101.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 13074011)

(строк обработано: 1)

SQL Server Execution Times:
CPU time = 24632 ms, elapsed time = 3844 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
17 июн 15, 14:01    [17782157]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить