Очистка больших таблиц DAX

[EVGL]

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

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

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'

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

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

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]

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

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

[gkochkin]

Цитата:

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

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

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

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

[dn]

Цитата:

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

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

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

[Alexius]

Цитата:

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

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

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