---
# System prepended metadata

title: 2020/6/11 V4B data housekeeping
tags: [house keep, V4B, Problem, physical, doc, v4b, Docs, document]

---

# 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
```sql
select count(bookingid) from BOO_TREATMENT_LOG
-- return 7623944
 ```    

## 2. find target bookingid
```sql 
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
```sql
select count(bookingid) 
from BOO_TREATMENT_LOG 
where bookingid < 11356037
-- return 6728577
```
predict delete 6728577/7623944 = ~88%

## 3. create SQL query
```sql
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
```sql 
select count(bookingid) 
from BOO_TREATMENT_LOG 
where bookingid < 11356037
-- result 0
```
```sql
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)
> 







