-
Type:
Feature Request
-
Status: In Development (View Workflow)
-
Priority:
Blocker
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: Core
As a developer, I want to minimize the amount of data that has to be searched through when running queries against SQL so that queries return more quickly, with less blocking. To help with this, we need a way of cleaning old, unused records out of tables efficiently and consistently.
We have found that in other tables that using Partitioning has been a successful strategy for purging these tables on a regular basis and has yielded benefits for the Forecasting tables.
With this in mind, we would like to partition tables with the most rows first, as that will have the biggest impacts on our application. Below are the table sizes in production for those tables with more than 1M rows:
| TableName | Rows |
|---|---|
| [dbo].[tbTransactionItem] | 43,364,599,196 |
| [dbo].[tbTransactionControlInfo] | 9,961,088,823 |
| [dbo].[tbTransactionSalesItem] | 7,903,899,426 |
| [dbo].[tbTransactionSalesItemCost] | 7,903,870,248 |
| [dbo].[tbTransactionSalesItemCombo] | 5,327,091,334 |
| [dbo].[tbPolledDataTransactionHeader] | 2,316,689,145 |
| [dbo].[tbTransactionSummary] | 2,316,582,569 |
| [Forecasting].[InventoryItemIntervalAggregate] | 1,881,191,106 |
| [dbo].[tbTransactionServiceTime] | 1,857,105,328 |
| [dbo].[tbTransactionPayment] | 1,466,064,397 |
| [dbo].[tbTransactionCashDepositFinancial] | 1,269,844,681 |
| [dbo].[tbEntitySalesItemCost] | 1,178,549,147 |
| [dbo].[tbFutureOrderDetail] | 1,045,686,007 |
| [Forecasting].[SalesItemIntervalAggregate] | 853,557,015 |
| [dbo].[tbAudit] | 844,824,249 |
| [dbo].[tbAuditDataImport] | 690,499,521 |
| [dbo].[tbsessionMessage] | 531,557,703 |
| [dbo].[tbExceptionUndefinedSalesItem] | 505,912,895 |
| [dbo].[tbAuditExport] | 443,705,400 |
| [dbo].[tbStockBalanceDaily] | 392,333,515 |
| [dbo].[tbTransactionSalesOrderDetail] | 369,556,274 |
| [dbo].[tbTransactionStockCount] | 311,788,962 |
| [dbo].[tbTransactionStockCountVariance] | 310,923,485 |
| [dbo].[tbTransactionCashDepositControlInfo] | 286,161,634 |
| [dbo].[tbTransactionSalesOrderDetailItemQuantity] | 241,752,630 |
| [Forecasting].[SalesItemDailyAggregate] | 233,162,920 |
| [dbo].[tbItemBalance] | 221,720,267 |
| [dbo].[tbFutureOrderHeader] | 202,485,731 |
| [dbo].[tbPolledLogTransaction] | 201,484,909 |
| [dbo].[tbTransactionSupplyOrderDetail] | 198,903,155 |
| [dbo].[tbEntityItemCostAdjust] | 198,273,350 |
| [dbo].[tbTransactionReceiveLog] | 195,571,917 |
| [dbo].[tbAuditLog] | 177,232,140 |
| [dbo].[tbTransactionPromo] | 169,566,716 |
| [Forecasting].[InventoryItemDailyAggregate] | 145,421,503 |
| [dbo].[tbpolleddatatransactionheader_xx] | 138,762,339 |
| [Forecasting].[MetricDetail] | 113,320,567 |
| [dbo].[tbSyncToken] | 93,943,285 |
| [dbo].[tbExportQueueMessage] | 92,827,541 |
| [dbo].[tbTransactionStockAdjust] | 92,452,287 |
| [dbo].[tbTransactionCashCount] | 91,628,847 |
| [dbo].[tbExportQueueMessageDestination] | 90,090,278 |
| [dbo].[tbMXShift] | 88,487,435 |
| [dbo].[tbSalesItemInventoryItemQuantity] | 78,182,782 |
| [dbo].[tbValidation] | 77,230,689 |
| [dbo].[tbTransactionCashDeposit] | 77,169,978 |
| [dbo].[tbBankingTransaction] | 76,927,556 |
| [dbo].[tbDLPSDisplayItem] | 65,356,899 |
| [dbo].[tbTransactionStockCountLocationDetail] | 64,529,945 |
| [reporting].[SalesItemDailyAggregate] | 55,518,818 |
| [dbo].[tbUserEvent] | 50,236,742 |
| [Forecasting].[MetricDetailIntervalAggregate] | 48,127,853 |
| [dbo].[tbWorkFlowTasksStatus] | 44,712,414 |
| [dbo].[tbTransactionDiscount] | 43,208,596 |
| [dbo].[tbBankingCloseFinancial] | 42,787,504 |
| [dbo].[tbCustomerOnAccountBalanceEvent] | 29,508,286 |
| [dbo].[tbAttendShift] | 28,295,998 |
| [dbo].[tbMXDayShift] | 25,090,211 |
| [dbo].[tbBankingEftposRecon] | 22,441,398 |
| [dbo].[tbEntityItemCost] | 18,969,854 |
| [dbo].[bkup_tbEntityItemCost] | 18,868,413 |
| [dbo].[tbAuditCashDeposit] | 18,210,771 |
| [dbo].[tbOnAccountSalePayment] | 14,411,309 |
| [dbo].[tbTransactionWaste] | 13,622,194 |
| [dbo].[tmpTbDLPSDisplayItem] | 12,541,106 |
| [dbo].[tbBankingFloatCount] | 12,412,589 |
| [dbo].[tbFloatMovement] | 12,386,078 |
| [dbo].[tbOnAccountSale] | 11,546,652 |
| [dbo].[tbXSLTTradingDayExportStatus] | 11,122,302 |
| [Forecasting].[EventAutoAdjustment] | 10,642,464 |
| [dbo].[tbEntitySalesItem] | 10,302,864 |
| [dbo].[tbMXDayShiftMarker] | 10,251,143 |
| [dbo].[tbItemQty] | 9,829,414 |
| [dbo].[tbEntityItem] | 9,737,728 |
| [bak].[tbEntityItem_1] | 9,724,143 |
| [bak].[tbEntityItem_2] | 9,685,410 |
| [dbo].[tbEntityItemDailyStockBalance] | 8,730,624 |
| [dbo].[tbMXDay] | 8,650,492 |
| [dbo].[bkup_tbEntitySalesItem] | 8,626,800 |
| [dbo].[tmp_SalesItemCosts20120430] | 8,333,096 |
| [dbo].[bkup_tbEntityItem] | 8,142,225 |
| [dbo].[tbWebServiceCall] | 8,033,453 |
| [dbo].[tbMXDayInfo] | 7,563,428 |
| [dbo].[tbSessionMachineCommand] | 7,365,664 |
| [dbo].[tbSessionCommand] | 7,341,215 |
| [dbo].[tbTransactionNonSales] | 6,547,511 |
| [dbo].[tbEntitySalesItemTaxCode] | 6,128,873 |
| [Forecasting].[Forecast] | 6,018,585 |
| [dbo].[tbBankingClose] | 6,013,928 |
| [dbo].[tbTransactionCashDetail] | 5,687,724 |
| [dbo].[tbEntityLocationItem] | 5,531,100 |
| [dbo].[tbClosingStock] | 5,313,652 |
| [dbo].[tbTransactionTLDStatus] | 4,796,135 |
| [dbo].[tbTransactionPettyCash] | 4,488,362 |
| [dbo].[bkup_tbEntityLocationItem] | 4,389,897 |
| [dbo].[tmp_PaperCost_20120430] | 4,163,544 |
| [dbo].[tbEntityItemYieldCorrectionFactor] | 3,966,854 |
| [dbo].[tbBanking] | 3,756,382 |
| [dbo].[tbBankingDeposit] | 3,754,448 |
| [dbo].[tbBankingChequeRecon] | 3,744,888 |
| [dbo].[tbTransactionSalesOrder] | 3,672,093 |
| [dbo].[tbTaskQueueMessage] | 3,524,385 |
| [dbo].[tbTransactionRefund] | 3,510,447 |
| [dbo].[tbCachedReport] | 3,468,047 |
| [dbo].[tbTransactionSupplyOrder] | 3,404,545 |
| [dbo].[tbWorkFlowTemplateInstance] | 3,376,518 |
| [dbo].[tbEntityDayCharacteristic] | 3,288,332 |
| [dbo].[tbBasket] | 2,901,624 |
| [dbo].[tbEntityDayCharacteristicDetail] | 2,833,355 |
| [dbo].[tbTransactionItem_Missed_2300000_A] | 2,659,952 |
| [dbo].[tbAttendShiftBreak] | 2,241,928 |
| [dbo].[tbVendorEntityItem] | 2,234,118 |
| [dbo].[bkup_tbVendorEntityItem] | 2,149,231 |
| [dbo].[TMP_PaperCosts06302012] | 2,118,200 |
| [dbo].[tbPolledLog] | 1,974,123 |
| [Forecasting].[MetricDetailDailyAggregate] | 1,950,665 |
| [dbo].[tbBankingDispatchTender] | 1,796,419 |
| [dbo].[tbBankingDispatch] | 1,634,640 |
| [dbo].[tbTransactionStockCountLocation] | 1,114,927 |
| [dbo].[tbTransactionTransferDetail] | 1,109,487 |
| [dbo].[tbDeliveryCheck] | 1,008,483 |
- is implemented by
-
CFAMX-9528 Augment/Create Load Tests
- Open
-
- Open
-
- Closed
-
- Closed
-
- Closed
-
- In Dev