# 2020/6/11 V4B data housekeeping

###### tags: `physical` `V4B` `v4b` `Problem` `Docs` `doc` `document` `house keep`

## 1. find how many rows in BOO_TREATMENT_LOG table

select count(bookingid) from BOO_TREATMENT_LOG
-- return 7623944

## 2. find target bookingid

select bookingid, date 
from BOO_TREATMENT_LOG 
where date > '2019-01-01' 
and bookingid < 11356038
-- return rows 1
select bookingid, date 
from BOO_TREATMENT_LOG 
where date > '2019-01-01' 
and bookingid < 11356037
--return rows 0

set cutoff id = 11356037

select count(bookingid) 
from BOO_TREATMENT_LOG 
where bookingid < 11356037
-- return 6728577

predict delete 6728577/7623944 = ~88%

## 3. create SQL query

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
     DELETE TOP (50000)  BOO_TREATMENT_LOG
     WHERE bookingid < 11356037

  SET @Deleted_Rows = @@ROWCOUNT;
END

## 4. exec

## 5. check result

select count(bookingid) 
from BOO_TREATMENT_LOG 
where bookingid < 11356037
-- result 0

select count(bookingid) from BOO_TREATMENT_LOG

-- result 895367

predict result : (7623944 - 6728577 = 895367) === return result :( 895367 ) success !~

## 6. Finish ~

---

# About Cashier
#### :
1. relative table had been added index by carol so much.

2. I think is the transaction include too many unnecessary execution.

3. [SAL_INVOICE] , [SAL_INVOICE_DETAIL] exec a long time(~40s, ~50s).

### before commit
~~ConfirmValidated()~~
> ~~dbHandler.get_invoice("", invoiceid, "", "", "1900-01-01")
> dbHandler.get_invoice2("", mtxtVoidedInvno.Text, "", "")
> dbHandler.get_promotion(SalesGrid.Rows(i).Cells("promotioncodeid").Value, 2)~~

### after commit:
CreateInvoice()
> * get_invoice_detail_org
> * insert_update_invoice_detail_org
> * insert_update_invoice_deposit
> * insert_update_member_beauty_unit_detail
> * insert_update_member_course
> * insert_update_invoice_detail_sales
> * insert_update_invoice_sales
> * insert_update_invoice_discount
> * insert_update_invoice_payment
> * insert_update_invoice_detail
> * insert_update_invoice
> * get_paymentmethod_advance

CreateBackOrder(invoicereprint, cboCenter.Text, mtxtMemberID.Text)
>