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

Откуда:
Сообщений: 539
Добрый день!
Наконец то есть повод заюзать Секционированные таблицы и индексы!
Ситуация:
Имеется таблица в которой необходимо хранить 1млрд. записей. А так же обрабатывать их.
почитав MSDN, сделал вот такой прототип:

--Функция ранжирования
CREATE PARTITION FUNCTION myRangePF1 (INT)
AS 
RANGE LEFT FOR VALUES (3,4,5,6,7,8)

--Схема которая использует функцию ранжирования
CREATE PARTITION SCHEME myRangePS1 
AS 
PARTITION myRangePF1  
ALL TO ([PRIMARY])

--Таблица привязанная к схеме
--где кластерный индекс ссылается как я понял на функцию ранжирования
CREATE TABLE [dbo].[GoodsBalance]
( 
	[Id]             INT IDENTITY(1,1) NOT NULL,		
	[IdStore]       INT NOT NULL,
	[Date]          DATETIME NOT NULL,
	[IdGoods]       INT NOT NULL,
	[Quantity]      MONEY NOT NULL,
	[RowVersion]  TIMESTAMP NOT NULL,
	--CONSTRAINT [PK_GoodsBalance] PRIMARY KEY NONCLUSTERED ([Id]), --что то надо придумать с PK
	--Если откомментить получаю ошибку: Column 'IdStore' is partitioning column of the index 'PK_GoodsBalance'. Partition columns for a unique index must be a subset of the index key.
	CONSTRAINT [UQ_GoodsBalance$IdStore$Date$IdGoods] UNIQUE CLUSTERED (IdStore,Date,IdGoods),
) ON  myRangePS1 (IdStore)

--Таблица без партиционирования, но с таким же кластерным индексом	
CREATE TABLE [dbo].[GoodsBalance1]
( 
	[Id]             INT IDENTITY(1,1) NOT NULL,		
	[IdStore]       INT NOT NULL,
	[Date]          DATETIME NOT NULL,
	[IdGoods]       INT NOT NULL,
	[Quantity]      MONEY NOT NULL,
	[RowVersion]  TIMESTAMP NOT NULL,
	CONSTRAINT [PK_GoodsBalance] PRIMARY KEY NONCLUSTERED ([Id]),		
	CONSTRAINT [UQ_GoodsBalance1$IdStore$Date$IdGoods] UNIQUE CLUSTERED (IdStore,Date,IdGoods),	
) 


Хочу сравнить быстродействие этих запросов:
В основном запросы будут такого вида. Фильтрация по IdStore бдует всегда, ну иногда
будет фильтрация по Date и IdGoods
DECLARE @idStore INT
SET @idStore = 3
           
SELECT IdGoods, SUM(Quantity) 
FROM [GoodsBalance]
WHERE IdStore = @idStore
GROUP BY IdGoods

SELECT IdGoods, SUM(Quantity) 
FROM [GoodsBalance1]
WHERE IdStore = @idStore
GROUP BY IdGoods

Момент истины: План выполнения 71% к 29% причем не в пользу первого запроса, где обращение идет к секционированной таблице.

Поэтому у меня возникли вопросы:
1.Почему отличаются планы выполнения, хотя по времени выполнения отличие всего на несколько миллисекунд опять так же в пользу первого запроса? Ведь обращение к секционированной таблице должно происходить быстрее. И план выполнения не должен отхватывать больший процент из batch'а.
2.Это как раз тот случай когда надо использовать партиционирование? Если учесть что фильтрация по IdStore будет всегда.

Помогите пожалуйста понять, то я делаю или нет.
Спасибо!
20 июн 14, 13:34    [16195124]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanH
План выполнения 71% к 29% причем не в пользу первого запроса, где обращение идет к секционированной таблице.

Вы лучше статистику чтений сравните
20 июн 14, 13:35    [16195143]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
yaxta
Member

Откуда: азербайджан,баку
Сообщений: 518
RomanH,

хотите смотрите

http://www.cyberguru.ru/database/sqlserver/sqlserver2005-partitioned-tables-indexes-page23.html
20 июн 14, 13:49    [16195240]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
RomanH
Member

Откуда:
Сообщений: 539
Glory
RomanH
План выполнения 71% к 29% причем не в пользу первого запроса, где обращение идет к секционированной таблице.

Вы лучше статистику чтений сравните


SET STATISTICS IO ON показывает это:
(19774 row(s) affected)
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.
Table 'GoodsBalance'. Scan count 1, logical reads 2983, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(19774 row(s) affected)
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.
Table 'GoodsBalance1'. Scan count 1, logical reads 2984, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


Разницу не вижу.
20 июн 14, 13:50    [16195246]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
RomanH
Member

Откуда:
Сообщений: 539
yaxta
RomanH,

хотите смотрите

http://www.cyberguru.ru/database/sqlserver/sqlserver2005-partitioned-tables-indexes-page23.html


Читаю доку здесь:

https://www.sql.ru/articles/mssql/2005/073102partitionedtablesandindexes.shtml
и
http://msdn.microsoft.com/ru-ru/library/ms188730.aspx
Более родное чтоли :)
20 июн 14, 13:54    [16195279]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
RomanH
Member

Откуда:
Сообщений: 539
Может план выполнения поможет понять?

К сообщению приложен файл (Part.zip - 2Kb) cкачать
20 июн 14, 14:00    [16195322]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
RomanH, смотрите на фактический план выполнения и количество секций, использованных при выборке.
20 июн 14, 14:54    [16195761]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
MX_User
Member

Откуда:
Сообщений: 27
Второй план ожидает гораздо больше строк, чем первый
Update Statistics ?
20 июн 14, 14:54    [16195764]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
RomanH
Ведь обращение к секционированной таблице должно происходить быстрее.
С чего вдруг? Серверу что, в 3 раза меньше данных нужно прочитать чтобы ваш запрос вполнить?
RomanH
И план выполнения не должен отхватывать больший процент из batch'а.
Стоимость плана зависит от оценок кардинальности,а они у вас в обоих случаях кривые, так что сравнивать их бестолку.

RomanH
2.Это как раз тот случай когда надо использовать партиционирование? Если учесть что фильтрация по IdStore будет всегда
И что? Партиционирование не является решением для производительности, это в первую очередь нужно для упрощения администрирования.
21 июн 14, 00:05    [16198723]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
RomanH,

В вашем случае никакого профита не будет. Можно даже планы не смотреть.
21 июн 14, 00:52    [16198860]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
RomanH
2.Это как раз тот случай когда надо использовать партиционирование? Если учесть что фильтрация по IdStore будет всегда.
Данные хранятся упорядоченно по IdStore. При фильтрации по IdStore сервер читает некий непрерывный фрагмент поверхности диска, на котором лежат записи с данным IdStore.
Никакей секционирование не может уменьшить размер этого фрагмента, так что выгоду вы не получите. В лучшем случае, если вы всё сделаете правильно, то получите такой же результат, как и без секционирования.

В данном случае секционирование может помочь при сильной фрагментации данных, из за интенсивной вставки данных для существующих IdStore в течении длительного времени.
RomanH
--что то надо придумать с PK
Так у вас же UNIQUE CLUSTERED (IdStore,Date,IdGoods), вот и сделайте его ПК.
21 июн 14, 01:27    [16198968]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение секционированной таблицы и НЕсекционированной  [new]
RomanH
Member

Откуда:
Сообщений: 539
Mind
Партиционирование не является решением для производительности, это в первую очередь нужно для упрощения администрирования.

Спасибо, буду знать.

NickAlex66
В вашем случае никакого профита не будет. Можно даже планы не смотреть.

Не буду смотреть :)

alexeyvg
В данном случае секционирование может помочь при сильной фрагментации данных, из за интенсивной вставки данных для существующих IdStore в течении длительного времени.

Мой случай - это только тестовый пример.
Я просто заранее хочу знать нужно мне секционирование или нет, чтобы определиться с редакцией MSSQL.
На самом деле функция ранжирования будет содержать не 6 VALUES, а от 10 тыс. и больше.
где для каждого IdStore ,будет содержаться от 3 млн записей, вставка записей для каждого IdStore будет очень интенсивной.
В таком случае есть необходимость использование секционированных таблиц?
Спасибо!
23 июн 14, 15:24    [16207097]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить