Performance between database with Adempiere
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
With the intention of showing to the performance of adempiere in each database, I setup some simple tests and this are the results:
Contents
Environment
Software
OS : openSUSE 10.2 (X86-64) GUI : Gnome 2.16.1 JAVA: JDK1.6.0 to X86-64
Hardware
CPU: AMD Athlon(tm) 64 Processor 3700+ Memory: 2 GB
Adempiere
Version: Adempiere 3.1.3 Lang: English Server: Stop Profile: LAN Log: Warning
Database
Oracle 10.2.0.1.0 to (X86-64) using SQLJ
Time to show Role 01-02 seconds Time to show Menus 07-08 seconds Time to show Business Partner Window 11-12 seconds Time to show Sales Order Window 07-08 seconds Time to show Product Window 06-07 seconds Time to show Preview the Sales Order 26-27 seconds Time to show Sales Detail Rep 05-06 seconds Time to show Product Info View 01-02 seconds
PostgreSQL 8.2.0 to (X86-64) using PLJAVA
Time to show Role 02-03 seconds Time to show Menus 08-09 seconds Time to show Business Partner Window 09-10 seconds Time to show Sales Order Window 07-08 seconds Time to show Product Window 06-07 seconds Time to show Preview the Sales Order 30-31 seconds Time to show Sales Detail Report 05-06 seconds Time to show Product Info View 03-04 seconds
Fyracle 0.8.10 to (i386)
I try to install fyracle but this does not exist for 64bits, I I try in 32bits without success
EnterpriseDB 8.1 to (X86-64) using PLSQL of Oracle
Time to show Role 01-02 seconds Time to show Menus 07-08 seconds Time to show Business Partner Window 10-11 seconds Time to show Sales Order Window 07-08 seconds Time to show Product Window 06-07 seconds
I obtained these errors in EDB even though is mentioned compatibility with ORACLE:
===========> MMeasure.updateCalculatedGoals: SELECT COALESCE(SUM(currencyBase(invoiceOpen(C_Invoice_ID, C_InvoicePaySchedule_ID),C_Currency_ID, DateAcct, AD_Client_ID, AD_Org_ID)),0) FROM C_Invoice_v C_Invoice WHERE IsSOTrx='Y' AND Processed='Y' AND C_Invoice.AD_Client_ID IN(0,11) AND C_Invoice.AD_Org_ID IN(0,11,12) java.sql.SQLException: ERROR: EDB-42883: function substr(character varying, numeric, integer) does not exist; State=42883; ErrorCode=0
===========> DB.executeUpdate: UPDATE C_Invoice i SET (Description,POReference)=(SELECT Description,POReference FROM C_Order o WHERE i.C_Order_ID=o.C_Order_ID) WHERE DocStatus NOT IN ('RE','CL') AND C_Order_ID=1000000 [null] [11] java.sql.SQLException: ERROR: syntax error at or near "i"; State=42601; ErrorCode=0
===========> DB.executeUpdate: UPDATE AD_PrintFormatItem_Trl trl SET PrintName = (SELECT e.PrintName FROM AD_Element_Trl e, AD_Column c WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=15900) WHERE AD_PrintFormatItem_ID = 1000122 AND EXISTS (SELECT * FROM AD_Element_Trl e, AD_Column c WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=15900 AND trl.AD_PrintFormatItem_ID = 1000122) AND EXISTS (SELECT * FROM AD_Client WHERE AD_Client_ID=trl.AD_Client_ID AND IsMultiLingualDocument='Y') [null] [11] java.sql.SQLException: ERROR: syntax error at or near "trl"; State=42601; ErrorCode=0
===========> InfoProduct.run: SELECT p.M_Product_ID, p.Discontinued, p.Value, p.Name, bomQtyAvailable(p.M_Product_ID,?,0) AS QtyAvailable, bomPriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceList, bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceStd, bomQtyOnHand(p.M_Product_ID,?,0) AS QtyOnHand, bomQtyReserved(p.M_Product_ID,?,0) AS QtyReserved, bomQtyOrdered(p.M_Product_ID,?,0) AS QtyOrdered, (SELECT SUM(c.TargetQty) FROM M_InOutLineConfirm c IN NER JOIN M_InOutLine il ON (c.M_InOutLine_ID=il.M_InOutLine_ID) INNER JOIN M_InOut i ON (il.M_InOut_ID=i.M_InOut_ID) WHERE c.Processed='N' AND i.M_Warehouse_ID=? AND il.M_Product_ID=p.M_Product_ID) AS QtyUnconfirmed, (SELECT SUM(c.TargetQty) FRO M M_MovementLineConfirm c INNER JOIN M_MovementLine ml ON (c.M_MovementLine_ID=ml.M_MovementLine_ID) INNER JOIN M_Locator l ON (ml.M_LocatorTo_ID=l.M_Locator_ID) WHERE c.Processed='N' AND l.M_Warehouse_ID=? AND ml.M_Product_ID=p.M_Product_ID) A S QtyUnconfirmedMove, bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID)-bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS Margin, bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceLimit, pa.IsInstanceAttribute FROM M_Product p LEFT OUTER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID AND pr.IsActive='Y') LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID) WHERE p.IsActive='Y' AND p.IsSummary='N' AND pr.M_PriceList_Version_ID=? AND p.AD_Client_ID IN(0,11) AND p.AD_Org_ID IN(0,11,12) ORDER BY QtyAvailable DESC, Margin DESC [14] java.sql.SQLException: ERROR: EDB-42883: function round(numeric, numeric) does not exist; State=42883; ErrorCode=0
===========> ProcessCtl.run: run [15] java.sql.SQLException: ERROR: syntax error at or near "CASE"; State=42601; ErrorCode=0
===========> MWorkflow.load: AD_Workflow_ID=106, SQL=SELECT AD_Org_ID,AD_WF_Node_ID,AccessLevel,AD_Client_ID,AD_Table_ID,AD_WF_Responsible_ID,AD_Workflow_ID,AD_WorkflowProcessor_ID,Author,Cost,Created,CreatedBy,Description,DocValueLogic,Duration,DurationUnit,EntityType,Help,IsActive,IsDefault,IsValid,Limit,Name,Priority,PublishStatus,Updated,UpdatedBy,ValidateWorkflow,ValidFrom,ValidTo,Value,Version,WaitingTime,WorkflowType,WorkingTime FROM AD_Workflow WHERE AD_Workflow_ID=? [16] java.sql.SQLException: ERROR: syntax error at or near "Limit"; State=42601; ErrorCode=0
===========> MAttachment.get_LOB: Unknown: [B@25a6cc45 [11]
Conclusion
finally my deception for EnterpriseDB is great, even that they say that she supports of form is transparent ORACLE without changing code, is not the case for Adempiere.
Then I now remain with PostgreSQL,I think that PostgreSQL is the sufficiently stable to leave in production
Kinds regards
Victor Perez
CEO