Project:
| View Revisions: Issue #55354 | [ Back to Issue ] | ||
| Summary | 0055354: Possible seq. scan in Transaction window types | ||
| Revision | 2024-04-29 12:36 by vmromanos | ||
| Description | When a window is declared as Transaction in the Application Dictionary, there is an implicit transactional filter (only draft & modified documents in the last 1 day(s)) that adds the following where clause when opening the window: (table.Updated>? or table.Processed='N') If the table doesn't have two indexes, one for the updated and one for the processed column, a seq. scan will be performance. Note that windows declared as Transaction are usually linked to High Volume tables, so the problem is even bigger. If there are no other restrictive filter in the where clause, the seq. scan will take place for sure. See for example the where clause when opening the Sales Invoice window: from C_Invoice invoice0_ where invoice0_.IsSOTrx='Y' and invoice0_.DocStatus<>'TEMP' and (invoice0_.Updated>? or invoice0_.Processed='N') and (invoice0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (invoice0_.AD_Client_ID in (? , ?)) order by invoice0_.DocumentNo, invoice0_.C_Invoice_ID limit ? and here for the Sales Order window: where order0_.C_DocTypeTarget_ID=documentty1_.C_DocType_ID and order0_.IsSOTrx='Y' and documentty1_.IsReturn='N' and (documentty1_.DocSubTypeSO not like 'OB') and (order0_.Updated>? or order0_.Processed='N') and (order0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (order0_.AD_Client_ID in (? , ?)) order by order0_.DocumentNo, order0_.C_Order_ID limit ? In both cases updated column is indexed, but not processed, so the seq. scan is performed |
||
| Revision | 2024-04-29 12:30 by vmromanos | ||
| Description | When a window is declared as Transaction, there is an implicit transactional filter (only draft & modified documents in the last 1 day(s)) that adds the following where clause when opening the window: (table.Updated>? or table.Processed='N') If the table doesn't have two indexes, one for the updated and one for the processed column, a seq. scan will be performance. Note that windows declared as Transaction are usually linked to High Volume tables, so the problem is even bigger. If there are no other restrictive filter in the where clause, the seq. scan will take place for sure. See for example the where clause when opening the Sales Invoice window: from C_Invoice invoice0_ where invoice0_.IsSOTrx='Y' and invoice0_.DocStatus<>'TEMP' and (invoice0_.Updated>? or invoice0_.Processed='N') and (invoice0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (invoice0_.AD_Client_ID in (? , ?)) order by invoice0_.DocumentNo, invoice0_.C_Invoice_ID limit ? and here for the Sales Order window: where order0_.C_DocTypeTarget_ID=documentty1_.C_DocType_ID and order0_.IsSOTrx='Y' and documentty1_.IsReturn='N' and (documentty1_.DocSubTypeSO not like 'OB') and (order0_.Updated>? or order0_.Processed='N') and (order0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (order0_.AD_Client_ID in (? , ?)) order by order0_.DocumentNo, order0_.C_Order_ID limit ? In both cases updated column is indexed, but not processed, so the seq. scan is performed |
||
| Copyright © 2000 - 2009 MantisBT Group |
 |