v4.5 is very slow compare to v1.4 in some sql

[wolfjiang]

I upgrade JsqlParser from v1.4 to v4.5, the performance is very different when parse this sql: SELECT count(*) AS \"count\" FROM Setting AS m WHERE ((key not in ('mergeBillingRepayment', 'mergeBillingCorRepayment', 'mergeBillingRepaymentRule', 'mergeBillingCorRepaymentRule', 'mergeBillingBatchReceipt', 'mergeBillingBatchReceiptRule', 'mergeBillingBatchPayment', 'mergeBillingBatchPaymentRule', 'isEnabledMultiOrg', 'arContractRemainingDayRule', 'arContractOverdueDayRule', 'arReceiptDayRule', 'preReceiptDayRule', 'apCrossOrgExeUserIsCreateUser', 'apCrossOrgExeUserRuleObject')) AND (((((settingLevelId = 'SettingLevel.accountingBook' AND (businessModuleId = 'BusinessModule_accountingFactPlatf' OR settingCategory.businessModuleId = 'BusinessModule_accountingFactPlatf')) OR (settingLevelId = 'SettingLevel.accountingBook' AND (businessModuleId = 'BusinessModule_ledger' OR settingCategory.businessModuleId = 'BusinessModule_ledger')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_accountingFactPlatf' OR settingCategory.businessModuleId = 'BusinessModule_accountingFactPlatf')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_ap' OR settingCategory.businessModuleId = 'BusinessModule_ap')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_ar' OR settingCategory.businessModuleId = 'BusinessModule_ar')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_asset' OR settingCategory.businessModuleId = 'BusinessModule_asset')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_cashier' OR settingCategory.businessModuleId = 'BusinessModule_cashier')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_goal' OR settingCategory.businessModuleId = 'BusinessModule_goal')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_innertrans' OR settingCategory.businessModuleId = 'BusinessModule_innertrans')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_ledger' OR settingCategory.businessModuleId = 'BusinessModule_ledger')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_project' OR settingCategory.businessModuleId = 'BusinessModule_project')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_reimburse' OR settingCategory.businessModuleId = 'BusinessModule_reimburse')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_resource' OR settingCategory.businessModuleId = 'BusinessModule_resource')) OR (settingLevelId = 'SettingLevel.global' AND (businessModuleId = 'BusinessModule_systemSetting' OR settingCategory.businessModuleId = 'BusinessModule_systemSetting')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_accountingFactPlatf' OR settingCategory.businessModuleId = 'BusinessModule_accountingFactPlatf')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_ap' OR settingCategory.businessModuleId = 'BusinessModule_ap')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_ar' OR settingCategory.businessModuleId = 'BusinessModule_ar')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_asset' OR settingCategory.businessModuleId = 'BusinessModule_asset')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_cashier' OR settingCategory.businessModuleId = 'BusinessModule_cashier')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_goal' OR settingCategory.businessModuleId = 'BusinessModule_goal')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_project' OR settingCategory.businessModuleId = 'BusinessModule_project')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_reimburse' OR settingCategory.businessModuleId = 'BusinessModule_reimburse')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_resource' OR settingCategory.businessModuleId = 'BusinessModule_resource')) OR (settingLevelId = 'SettingLevel.organizational' AND (businessModuleId = 'BusinessModule_systemSetting' OR settingCategory.businessModuleId = 'BusinessModule_systemSetting'))) AND (businessModuleId in ('BusinessModule_analyticalReport', 'BusinessModule_base', 'BusinessModule_goal', 'BusinessModule_init', 'BusinessModule_rolePortal', 'BusinessModule_systemManage', 'BusinessModule_systemSetting', 'BusinessModule_virtualModule4Qbos', 'BusinessModule_ap', 'BusinessModule_innertrans', 'BusinessModule_cashier', 'BusinessModule_reimburse', 'BusinessModule_projectConfirmation', 'BusinessModule_project', 'BusinessModule_projectSchedule', 'BusinessModule_projectSchedule', 'BusinessModule_project', 'BusinessModule_projectConfirmation', 'BusinessModule_timesheet', 'BusinessModule_contract', 'BusinessModule_invoice', 'BusinessModule_accountingFactPlatf', 'BusinessModule_ledger', 'BusinessModule_report', 'BusinessModule_salary', 'BusinessModule_purchase', 'BusinessModule_budget', 'BusinessModule_invManagement', 'BusinessModule_invAccounting', 'BusinessModule_multiOrg', 'BusinessModule_asset', 'BusinessModule_resource', 'BusinessModule_ar') OR settingCategory.businessModuleId in ('BusinessModule_analyticalReport', 'BusinessModule_base', 'BusinessModule_goal', 'BusinessModule_init', 'BusinessModule_rolePortal', 'BusinessModule_systemManage', 'BusinessModule_systemSetting', 'BusinessModule_virtualModule4Qbos', 'BusinessModule_ap', 'BusinessModule_innertrans', 'BusinessModule_cashier', 'BusinessModule_reimburse', 'BusinessModule_projectConfirmation', 'BusinessModule_project', 'BusinessModule_projectSchedule', 'BusinessModule_projectSchedule', 'BusinessModule_project', 'BusinessModule_projectConfirmation', 'BusinessModule_timesheet', 'BusinessModule_contract', 'BusinessModule_invoice', 'BusinessModule_accountingFactPlatf', 'BusinessModule_ledger', 'BusinessModule_report', 'BusinessModule_salary', 'BusinessModule_purchase', 'BusinessModule_budget', 'BusinessModule_invManagement', 'BusinessModule_invAccounting', 'BusinessModule_multiOrg', 'BusinessModule_asset', 'BusinessModule_resource', 'BusinessModule_ar') OR (businessModuleId is null AND settingCategory.businessModuleId is null)))) OR key='projectBudgetExecuteAccountingBook') AND ((settingCategory.idPath like '%EVJFCK5017D0001%' OR settingCategory.parent = 'EVJFCK5017D0001') AND key not in ('mergeBillingRepayment', 'mergeBillingCorRepayment', 'mergeBillingRepaymentRule', 'mergeBillingCorRepaymentRule', 'mergeBillingBatchReceipt', 'mergeBillingBatchReceiptRule', 'mergeBillingBatchPayment', 'mergeBillingBatchPaymentRule', 'isEnabledMultiOrg', 'arContractRemainingDayRule', 'arContractOverdueDayRule', 'arReceiptDayRule', 'preReceiptDayRule', 'apCrossOrgExeUserIsCreateUser', 'apCrossOrgExeUserRuleObject')))

with this test code: String sql = "SELECT count(*) AS \"count\" FROM Setting ..."; CCJSqlParserManager pm = new CCJSqlParserManager(); long t = System.currentTimeMillis(); for(int i=0; i<10; i++) { pm.parse(new StringReader(sql)); } System.out.println((System.currentTimeMillis() - t) / 10.0);

version 1.4, output is : 11, but version 4.5 output is 1115, it 100 times. Is there anything wrong with me?

[manticore-projects]

Greetings.

Thank you for your report. It is not unexpected since JSQLParser has evolved a lot and now is able to parse very complex nested structures -- at a performance penalty. Your particular query with lots of brackets forces the parser to test, those brackets hold nested expressions or sub-queries. You could disable Complex Expression parsing using the FeatureSets, please refer to https://manticore-projects.com/JSQLParser/usage.html#define-the-parser-features

// Disable Complex Parsing (which allows nested Expressions, but is much slower)
Statement stmt2 = CCJSqlParserUtil.parse(
                sqlStr
                , parser -> parser
                        .withSquareBracketQuotation(true)
                        .withAllowComplexParsing(false)
                        .withTimeOut(6000)
);

The query in a readable form:

SELECT Count( * ) AS "count"
FROM setting AS m
WHERE ( ( key NOT IN (  'mergeBillingRepayment', 'mergeBillingCorRepayment', 'mergeBillingRepaymentRule'
                        , 'mergeBillingCorRepaymentRule', 'mergeBillingBatchReceipt', 'mergeBillingBatchReceiptRule'
                        , 'mergeBillingBatchPayment', 'mergeBillingBatchPaymentRule', 'isEnabledMultiOrg'
                        , 'arContractRemainingDayRule', 'arContractOverdueDayRule', 'arReceiptDayRule'
                        , 'preReceiptDayRule', 'apCrossOrgExeUserIsCreateUser', 'apCrossOrgExeUserRuleObject' ) )
        AND ( ( ( ( ( settinglevelid = 'SettingLevel.accountingBook'
                                AND ( businessmoduleid = 'BusinessModule_accountingFactPlatf'
                                        OR settingcategory.businessmoduleid = 'BusinessModule_accountingFactPlatf' ) )
                            OR ( settinglevelid = 'SettingLevel.accountingBook'
                                    AND ( businessmoduleid = 'BusinessModule_ledger'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ledger' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_accountingFactPlatf'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_accountingFactPlatf' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_ap'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ap' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_ar'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ar' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_asset'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_asset' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_cashier'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_cashier' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_goal'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_goal' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_innertrans'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_innertrans' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_ledger'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ledger' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_project'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_project' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_reimburse'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_reimburse' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_resource'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_resource' ) )
                            OR ( settinglevelid = 'SettingLevel.global'
                                    AND ( businessmoduleid = 'BusinessModule_systemSetting'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_systemSetting' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_accountingFactPlatf'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_accountingFactPlatf' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_ap'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ap' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_ar'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_ar' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_asset'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_asset' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_cashier'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_cashier' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_goal'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_goal' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_project'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_project' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_reimburse'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_reimburse' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_resource'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_resource' ) )
                            OR ( settinglevelid = 'SettingLevel.organizational'
                                    AND ( businessmoduleid = 'BusinessModule_systemSetting'
                                            OR settingcategory.businessmoduleid = 'BusinessModule_systemSetting' ) ) )
                        AND ( businessmoduleid IN ( 'BusinessModule_analyticalReport', 'BusinessModule_base', 'BusinessModule_goal'
                                                    , 'BusinessModule_init', 'BusinessModule_rolePortal', 'BusinessModule_systemManage'
                                                    , 'BusinessModule_systemSetting', 'BusinessModule_virtualModule4Qbos', 'BusinessModule_ap'
                                                    , 'BusinessModule_innertrans', 'BusinessModule_cashier', 'BusinessModule_reimburse'
                                                    , 'BusinessModule_projectConfirmation', 'BusinessModule_project', 'BusinessModule_projectSchedule'
                                                    , 'BusinessModule_projectSchedule', 'BusinessModule_project', 'BusinessModule_projectConfirmation'
                                                    , 'BusinessModule_timesheet', 'BusinessModule_contract', 'BusinessModule_invoice'
                                                    , 'BusinessModule_accountingFactPlatf', 'BusinessModule_ledger', 'BusinessModule_report'
                                                    , 'BusinessModule_salary', 'BusinessModule_purchase', 'BusinessModule_budget'
                                                    , 'BusinessModule_invManagement', 'BusinessModule_invAccounting', 'BusinessModule_multiOrg'
                                                    , 'BusinessModule_asset', 'BusinessModule_resource', 'BusinessModule_ar' )
                                OR settingcategory.businessmoduleid IN (    'BusinessModule_analyticalReport', 'BusinessModule_base', 'BusinessModule_goal'
                                                                            , 'BusinessModule_init', 'BusinessModule_rolePortal', 'BusinessModule_systemManage'
                                                                            , 'BusinessModule_systemSetting', 'BusinessModule_virtualModule4Qbos', 'BusinessModule_ap'
                                                                            , 'BusinessModule_innertrans', 'BusinessModule_cashier', 'BusinessModule_reimburse'
                                                                            , 'BusinessModule_projectConfirmation', 'BusinessModule_project', 'BusinessModule_projectSchedule'
                                                                            , 'BusinessModule_projectSchedule', 'BusinessModule_project', 'BusinessModule_projectConfirmation'
                                                                            , 'BusinessModule_timesheet', 'BusinessModule_contract', 'BusinessModule_invoice'
                                                                            , 'BusinessModule_accountingFactPlatf', 'BusinessModule_ledger', 'BusinessModule_report'
                                                                            , 'BusinessModule_salary', 'BusinessModule_purchase', 'BusinessModule_budget'
                                                                            , 'BusinessModule_invManagement', 'BusinessModule_invAccounting', 'BusinessModule_multiOrg'
                                                                            , 'BusinessModule_asset', 'BusinessModule_resource', 'BusinessModule_ar' )
                                OR ( businessmoduleid IS NULL
                                        AND settingcategory.businessmoduleid IS NULL ) ) ) )
                OR key = 'projectBudgetExecuteAccountingBook' )
        AND ( ( settingcategory.idpath LIKE '%EVJFCK5017D0001%'
                    OR settingcategory.parent = 'EVJFCK5017D0001' )
                AND key NOT IN (    'mergeBillingRepayment', 'mergeBillingCorRepayment', 'mergeBillingRepaymentRule'
                                    , 'mergeBillingCorRepaymentRule', 'mergeBillingBatchReceipt', 'mergeBillingBatchReceiptRule'
                                    , 'mergeBillingBatchPayment', 'mergeBillingBatchPaymentRule', 'isEnabledMultiOrg'
                                    , 'arContractRemainingDayRule', 'arContractOverdueDayRule', 'arReceiptDayRule'
                                    , 'preReceiptDayRule', 'apCrossOrgExeUserIsCreateUser', 'apCrossOrgExeUserRuleObject' ) ) )
;

[manticore-projects]

Btw, an alternative route was to lower the Timeout to maybe 200 ms while enabling complex parsing. It would try complex parsing first, then timeout and try simple parsing after. Of course the first 200 ms are still wasted, but nested expressions would still work.

[wolfjiang]

@manticore-projects Thank you very much for you quick and efficient reply. Is there any side effects when disable Complex Expression parsing?

[manticore-projects]

yes of course: you wont be able to parse complex, nested expression. Examples are Sub-Selects, CAST/IN with sub-selects, functions with sub-selects as parameters.

[manticore-projects]

Did my advise work for you? Can we close this issue?