Очистка больших таблиц DAX - DAX: Программирование

gkochkin · 11.04.2017, 11:24

Добрый день, коллеги.
Прошу поделиться опытом по очистке больших таблиц DAX в SQL Server - InventTrans, InventDim, InventsumLogTTS и т.д.
InventTrans у нас очень большая, такая, что с ней практически невозможно проводиться какие-то операции (реиндексация, секционирование и т.д.).
Есть еще журналы базы данных, которые растут просто стремительно, но старые данные в них никто очищать не хочет под предлогом того, что "могут пригодиться" и точка.
Буду благодарен за рекомендации.

potential · 11.04.2017, 11:36

Чистил большую таблицу sql скриптом итерациями по 100к строк. Но это был не InventTrans а оборотка по складу, которую интегратор сделал в виде регулярной таблицы.
Насчет "могут пригодиться" и точка можно ответственно заявить что в таком случае бэкап не будет разворачиваться (в приемлемое время) и точка

EVGL · 11.04.2017, 12:05

Удалял InventTrans там, где InventSum была "Closed". Проблематично в том, что InventSum не сообщает, когда именно данный серийный номер был "закрыт" и сопоставлен. В тех же случаях, когда серийные номера или batches не используются, а есть только склады, ситуация еще сложнее, и удаляли только те InventTrans, где ItemId "устарели". При удалении очень рекомендую отключить индексы.

Вот для вдохновения:

X++:

delete InventDim
	where exists (select 1 from INVENTSERIAL 
					where INVENTSERIAL.DATAAREAID = 'XXX' AND INVENTDIM.INVENTSERIALID = INVENTSERIAL.INVENTSERIALID) AND
	not exists  (select 1 from inventTrans where InventTrans.InventDimId = InventDim.InventDimId AND InventTrans.DataAreaId = 'XXX')
	AND INVENTDIM.INVENTSERIALID <> '' AND INVENTDIM.DATAAREAID = 'XXX'

X++:

insert into INVENTSETTLEMENT 
		(QTYSETTLED, COSTAMOUNTSETTLED, SETTLETRANSID, COSTAMOUNTADJUSTMENT, RECVERSION, RECID,
		TRANSRECID, INVENTTRANSID, ITEMID, TRANSDATE, VOUCHER, BALANCESHEETACCOUNT, OPERATIONSACCOUNT, CANCELLED, SETTLEMODEL,
		DIMENSION, DIMENSION2_, DIMENSION3_, DIMENSION4_, DIMENSION5_, DIMENSION6_, DIMENSION7_, 
		BALANCESHEETPOSTING, OPERATIONSPOSTING, ITEMGROUPID, POSTED, SETTLETYPE, DATAAREAID)
select 
SUM(is1.QtySettled), SUM(is1.CostAmountSettled), is1.INVENTTRANSID, SUM(is1.COSTAMOUNTADJUSTMENT), 777, max(is1.RecId),
is1.TRANSRECID, is1.INVENTTRANSID, is1.ItemId, is1.TransDate, is1.VOUCHER,
is1.BalanceSheetAccount, is1.OPERATIONSACCOUNT, is1.Cancelled, is1.SettleModel, 
is1.Dimension, is1.Dimension2_, is1.Dimension3_, is1.Dimension4_, is1.Dimension5_, is1.Dimension6_, is1.Dimension7_, 
is1.BalanceSheetPosting, is1.OperationsPosting, is1.ItemGroupId, is1.Posted, 4, is1.DataAreaId

from iSettlement as is1

where exists (select 1 from INVENTTRANS
				where INVENTTRANS.DATAAREAID = is1.DATAAREAID AND INVENTTRANS.RECID = is1.TRANSRECID AND INVENTTRANS.VALUEOPEN = 0)
group by 
is1.TRANSRECID, is1.INVENTTRANSID, is1.ItemId, is1.TransDate, is1.VOUCHER,
is1.BalanceSheetAccount, is1.OPERATIONSACCOUNT, is1.Cancelled, is1.SettleModel, 
is1.Dimension, is1.Dimension2_, is1.Dimension3_, is1.Dimension4_, is1.Dimension5_, is1.Dimension6_, is1.Dimension7_, 
is1.BalanceSheetPosting, is1.OperationsPosting, is1.ItemGroupId, is1.Posted, is1.SettleType, is1.DataAreaId

GO

delete iSettlement
	where exists 
		(select 1 from INVENTTRANS
				where INVENTTRANS.DATAAREAID = iSettlement.DATAAREAID AND INVENTTRANS.RECID = iSettlement.TRANSRECID AND INVENTTRANS.VALUEOPEN = 0)
	AND iSettlement.RecVersion <> 777

X++:

IF @TurnOffIndexes = 1
BEGIN
	ALTER INDEX I_155VoucherIdx ON INVENTJOURNALTRANS DISABLE
	ALTER INDEX I_155ToTransIdIdx ON INVENTJOURNALTRANS DISABLE
	ALTER INDEX I_155TransIDIdx ON INVENTJOURNALTRANS DISABLE
	ALTER INDEX I_155ExciseTariffCodes_INIdx ON INVENTJOURNALTRANS DISABLE
	ALTER INDEX I_155RecID ON INVENTJOURNALTRANS DISABLE

	ALTER INDEX I_174ClosedItemDimIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174DimIdIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174ClosedQtyIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174ReservationIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174ClosedAvailPhysIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174ItemCloseAvailIdx ON INVENTSUM DISABLE
	ALTER INDEX I_174RecID ON INVENTSUM DISABLE

	ALTER INDEX I_698PalletIdIdx ON INVENTDIM DISABLE
	ALTER INDEX I_698LocationIdIdx ON INVENTDIM DISABLE
	ALTER INDEX I_698BatchIdIdx ON INVENTDIM DISABLE
	ALTER INDEX I_698DimIdx ON INVENTDIM DISABLE
	ALTER INDEX I_698SiteIdx ON INVENTDIM DISABLE
	ALTER INDEX I_698InventProfileIdx_RU ON INVENTDIM DISABLE
	ALTER INDEX I_698InventOwnerIdx_RU ON INVENTDIM DISABLE
	ALTER INDEX I_698GTDIdIdx_RU ON INVENTDIM DISABLE

	ALTER INDEX I_177StatusItemIdx ON INVENTTRANS DISABLE
	ALTER INDEX I_177DimIdIdx ON INVENTTRANS DISABLE
	ALTER INDEX I_177OpenItemIdx ON INVENTTRANS DISABLE
	ALTER INDEX I_177GoodsInRouteSalesIdx_RU ON INVENTTRANS DISABLE
	ALTER INDEX I_177GroupRefIdx_RU ON INVENTTRANS DISABLE
	ALTER INDEX I_177OpenSecCurItemIdx_RU ON INVENTTRANS DISABLE
	ALTER INDEX I_177GoodsInRouteTransitIdx_RU ON INVENTTRANS DISABLE
	ALTER INDEX I_177GoodsInRouteDeliveryIdx_RU ON INVENTTRANS DISABLE
	ALTER INDEX I_177RecId ON INVENTTRANS DISABLE

	ALTER INDEX I_2938InventTransIdIdx ON INVENTTRANSORIGIN DISABLE
	ALTER INDEX I_2938ItemIdx ON INVENTTRANSORIGIN DISABLE

	--ALTER INDEX I_553ItemTypeDateIdx ON INVENTTRANSPOSTING DISABLE
	ALTER INDEX I_553InventTransOriginIdx ON INVENTTRANSPOSTING DISABLE

	ALTER INDEX I_173DateVoucherIdx ON INVENTSETTLEMENT DISABLE
	ALTER INDEX I_173ItemDateIdx ON INVENTSETTLEMENT DISABLE
	ALTER INDEX I_173ItemVoucherDateIdx ON INVENTSETTLEMENT DISABLE

	ALTER INDEX I_752BatchIdx ON INVENTBATCH DISABLE
	ALTER INDEX I_752ItemVendBatchIdx ON INVENTBATCH DISABLE

	ALTER INDEX I_1204SerialIdx ON INVENTSERIAL DISABLE
	ALTER INDEX I_1204RFIDTagIdx ON INVENTSERIAL DISABLE
END

X++:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventSum, InventDim ' + @ItemId

	delete INVENTSUM output deleted.INVENTDIMID into @DeletedIds where INVENTSUM.ITEMID = @ItemId and INVENTSUM.DATAAREAID = @DataAreaID	  
	delete INVENTDIM from INVENTDIM join @DeletedIds IDs on INVENTDIM.INVENTDIMID = IDs.ID 
		where INVENTDIM.INVENTSERIALID <> '' and INVENTDIM.DATAAREAID = @DataAreaID --and INVENTDIM.INVENTBATCHID <> ''
	delete @DeletedIds	
	
	PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTrans ' + @ItemId
	delete INVENTTRANS
		output deleted.RECID into @DeletedRecIds
		where INVENTTRANS.ITEMID = @ItemId and INVENTTRANS.DATAAREAID = @DataAreaID

	PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransOrigin ' + @ItemId
	delete INVENTTRANSORIGIN
		output deleted.RECID into @DeletedOriginIds
		--join @DeletedRecIds IDs on INVENTTRANSORIGIN.RECID = IDs.OriginRecId
		where INVENTTRANSORIGIN.DATAAREAID = @DataAreaID and INVENTTRANSORIGIN.ITEMID = @ItemId

	delete INVENTTRANSORIGINPURCHLINE from INVENTTRANSORIGINPURCHLINE join @DeletedOriginIds IDs on INVENTTRANSORIGINPURCHLINE.INVENTTRANSORIGIN = IDs.OriginRecId
	delete INVENTTRANSORIGINSALESLINE from INVENTTRANSORIGINSALESLINE join @DeletedOriginIds IDs on INVENTTRANSORIGINSALESLINE.INVENTTRANSORIGIN = IDs.OriginRecId
	delete INVENTTRANSORIGINJOURNALTRANS from INVENTTRANSORIGINJOURNALTRANS join @DeletedOriginIds IDs on INVENTTRANSORIGINJOURNALTRANS.INVENTTRANSORIGIN = IDs.OriginRecId
	delete INVENTTRANSORIGINJOURNALTRANSRECEIPT from INVENTTRANSORIGINJOURNALTRANSRECEIPT join @DeletedOriginIds IDs on INVENTTRANSORIGINJOURNALTRANSRECEIPT.INVENTTRANSORIGIN = IDs.OriginRecId

	delete INVENTTRANSORIGINTRANSFER from INVENTTRANSORIGINTRANSFER join @DeletedOriginIds IDs on INVENTTRANSORIGINTRANSFER.ISSUEINVENTTRANSORIGIN = IDs.OriginRecId
	delete INVENTTRANSORIGINTRANSFER from INVENTTRANSORIGINTRANSFER join @DeletedOriginIds IDs on INVENTTRANSORIGINTRANSFER.RECEIPTINVENTTRANSORIGIN = IDs.OriginRecId

	-- the most expensive query
	PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransPosting ' + @ItemId
	delete INVENTTRANSPOSTING from INVENTTRANSPOSTING 
	--join @DeletedOriginIds IDs on INVENTTRANSPOSTING.INVENTTRANSORIGIN = IDs.OriginRecId 
		where INVENTTRANSPOSTING.DATAAREAID = @DataAreaID and INVENTTRANSPOSTING.ITEMID = @ItemId

	PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransSettlement ' + @ItemId
	-- the itemID alone is just too slow
	delete INVENTSETTLEMENT from INVENTSETTLEMENT 
	join @DeletedRecIds IDs on INVENTSETTLEMENT.TRANSRECID = IDs.RecID 
		where INVENTSETTLEMENT.DATAAREAID = @DataAreaID and INVENTSETTLEMENT.ITEMID = @ItemId

dn · 11.04.2017, 12:18

Журнал базы данных режем регулярно в пакете - оставляем данные за последние полгода.

Если без сводного, то InventsumLogTTS чистить регулярно через deleteCommittedItemId()

gkochkin · 11.04.2017, 12:24

Цитата:

Сообщение от dn

Журнал базы данных режем регулярно в пакете - оставляем данные за последние полгода.

Если без сводного, то InventsumLogTTS чистить регулярно через deleteCommittedItemId()

Что значит "Если без сводного"?

dn · 11.04.2017, 12:31

Цитата:

Сообщение от gkochkin

Что значит "Если без сводного"?

При запуске сводного планирования эта логика по очистке и так выполняется.

Alexius · 11.04.2017, 15:24

Цитата:

Сообщение от EVGL

Удалял InventTrans там, где InventSum была "Closed". Проблематично в том, что InventSum не сообщает, когда именно данный серийный номер был "закрыт" и сопоставлен.[/XPP]

Можно вычислить максимум DateClosed из всех InventTrans, относящихся к конкретной записи InventSum.

trud · 11.04.2017, 12:50

А кстати не пробовали подойти с другой стороны. в последнее время на ixbt и подобных ресурсах много обзоров носителей способных обрабатывать чуть ли не полмиллиона IOPS. причем объемы тоже довольно большие. т.е. такому хранилищу большие таблицы должны быть по зубам или это все еще слишком дорого?

gkochkin · 11.04.2017, 13:44

Цитата:

Сообщение от trud

А кстати не пробовали подойти с другой стороны. в последнее время на ixbt и подобных ресурсах много обзоров носителей способных обрабатывать чуть ли не полмиллиона IOPS. причем объемы тоже довольно большие. т.е. такому хранилищу большие таблицы должны быть по зубам или это все еще слишком дорого?

Это, конечно, очень дорого.
Как вы можете такие вещи предлагать??

Logger · 11.04.2017, 13:46

А не рассматривали вариант
1. Пересесть в новую базу с теми же справочниками.
или
2. Завести новую компанию. Большие таблички секционировать по коду компании.

P.S. Сколько объем InventTrans ?

gkochkin · 11.04.2017, 13:51

Цитата:

Сообщение от Logger

А не рассматривали вариант
1. Пересесть в новую базу с теми же справочниками.
или
2. Завести новую компанию. Большие таблички секционировать по коду компании.

P.S. Сколько объем InventTrans ?

Объем - порядка 330 ГБ.
Если честно, не совсем понимаю - пересесть в новую базу и что?
Над новой компанией точно не думали..

ivas · 13.04.2017, 21:29

Цитата:

Сообщение от gkochkin

Объем - порядка 330 ГБ.
Если честно, не совсем понимаю - пересесть в новую базу и что?
Над новой компанией точно не думали..

330гб это не много, объем таблицы InventTrans на производительность влияет косвенно (если конечно вы не суммируете проводки например в дисплей методах

).
Интересно было бы посмотреть объем других таблиц например InventDim, InventSum, Cust/Vend/Trans/TransOpen. А в целом нужно конечно видеть хотя бы топ 25 тяжелых запросов и знать конфигурацию SQL серверов.

Ах, да, чистить быстрее всего с помощью TRUNCATE TABLE

https://msdn.microsoft.com/ru-ru/library/ms177570.aspx

eugene egorov · 13.04.2017, 22:58

Цитата:

Сообщение от ivas

Ах, да, чистить быстрее всего с помощью TRUNCATE TABLE

https://msdn.microsoft.com/ru-ru/library/ms177570.aspx

DROP DATABASE [IF EXISTS] db_name гораздо гораздее

ivas · 14.04.2017, 00:43

Цитата:

Сообщение от eugene egorov

DROP DATABASE [IF EXISTS] db_name гораздо гораздее

Тоже неплохой вариант по производительности но после команды DROP TABLE/DATABASE теряется структура таблицы, а отчистка подразумевает удаление только данных

Logger · 11.04.2017, 14:05

Ну как что.
Полегчает резко. База то практически пустая будет. Из транзакционных данных только исходные остатки.

gkochkin · 11.04.2017, 14:16

Цитата:

Сообщение от Logger

Ну как что.
Полегчает резко. База то практически пустая будет. Из транзакционных данных только исходные остатки.

а со старой базой что делать?
Эта процедура где-то описана?

Alexius · 11.04.2017, 15:33

Цитата:

Сообщение от gkochkin

а со старой базой что делать?

Отложить в сторонку.

Цитата:

Сообщение от gkochkin

Эта процедура где-то описана?

Штатной процедуры очистки исторических данных в АХ нет, каждый выкручивается как может.

**Vadik** · 11.04.2017, 15:41

Цитата:

Сообщение от Alexius

Отложить в сторонку.
Штатной процедуры очистки исторических данных в АХ нет, каждый выкручивается как может

IDMF ?

Ivanhoe · 11.04.2017, 16:08

Цитата:

Сообщение от Vadik

IDMF ?

По моему опыту, сам MS на тех нескольких кейсах, где клиент задавал серьезные вопросы съехал с этой темы, не желая ничего гарантировать, при этом поиск хоть какого-то специалиста по этому инструменту занимал недели.

Ivanhoe · 11.04.2017, 14:57

330 ГБ не так уж и много. Если вернуться к начальной постановке вопроса - что будет, если ничего не делать?

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
Описание таблиц DAX 2009	Segador	DAX: Программирование	3	07.10.2013 08:13
Генерация таблиц runtime DAX 2012	Idler	DAX: Программирование	6	30.10.2012 16:47
Переход на DAX 2009. Проблема с повторяющимися id таблиц. Как исправить?	Murlin	DAX: Программирование	18	02.11.2009 15:42
Пустые названия системных таблиц в report data range (DAX 4.0)	Qaz Qwerty	DAX: Функционал	3	06.08.2008 00:05
удаление больших таблиц	Nikolaich	DAX: Программирование	9	31.01.2006 13:35