R
rayherring
Guest
There is no index on 'Hide_From_GST_Report' at all, Although I said 'Customer', the table that the flag is in is not the same, I was just trying to be generic with my question. The name 'Customer' is a bad name too, I should have said 'Account', since the 'Account' table has accounts for our stores, customers, suppliers, delivery companies, etc... and then hanging off of that master table is sub-tables depending on the type (Store, Customer, Supplier, Delivery, etc...). The flag is stored in a 'AccountFlag' table which has an index on the 'Customer_ID' (Don't ask, I didn't design it this way and it won't be changing any time soon). Also, although I used 'Store_ID = 1' in my example, there are actually 17 stores in total (4 of which aren't customer facing ones). The full example would be: FOR EACH Store NO-LOCK WHERE Store.Store_Deleted = FALSE AND Store.Requires_GST_Totals = TRUE: inv-blk: FOR EACH InvHeader_DT NO-LOCK WHERE InvHeader_DT.Store_ID = Store.Store_ID AND InvHeader_DT.Invoice_Date >= 11/01/2017 AND InvHeader_DT.Invoice_Date = 11/01/2017 AND InvHeader_DT.Invoice_Date <= 11/30/2017, FIRST AccountFlag NO-LOCK WHERE AccountFlag.Customer_ID = InvHeader.Customer_ID AND AccountFlag.Hide_From_GST_Report NE FALSE BREAK BY InvHeader_DT.Customer_ID: DISPLAY InvHeader.Customer_ID. END. END. But that makes it take an extra second or 2 in total. In all timing tests I have found that the 'FOR EACH' on 'Store' is extremely fast, less than 0.02s in total, but that's because we only have like 17 stores where the 'Store_Deleted' flag is FALSE and a couple where the 'Store_Deleted' flag is TRUE. There is an index on 'InvHeader' which fits what I have opted for: Store_ID Invoice_Date Customer_ID (Obviously I don't use Customer_ID in my 'FOR EACH' on 'InvHeader' but the index is still the chosen one when I do an xref in mpro). The only index on 'AccountFlag' is for 'Customer_ID'. Obviously the problem here is that it still has to investigate every single InvHeader row before determining if it needs to skip it or not which adds time (for 1 store it took 0.2s, most stores seem to take about 0.15s). There are 84,000 InvHeader records that fall between 11/01/2017 and 11/30/2017, customers have a default store they purchase from but there is nothing stopping them from purchasing from other stores. Looking at AccountFlag, there are 2 accounts that have the flag set to 'N', one is our main warehouse (which feeds each individual store when they get low on stock), the other is our stock transfer account, neither of which are involved in GST since it is all inter-company.
Continue reading...
Continue reading...