We are considering using an In-Memory database (such as Apache Ignite) to deal with performance intense BI-like operations. So as a (very primitive) example, I filled Apache Ignite with 250.000 records from a csv-file (14 columns) and did some group-by operations. Previously, I also used the same data to do some performance-tests with MS SQL-Server.
Interestingly and unexpected, MS SQL-Server need about 0.25 seconds to perform this operations, while it takes 1-2 seconds with Apache Ignite.
1, I always was under the impression that Apache Ignite is not only a good option for distributed computing, but also leads to a performance gain compared to a conventional relational database due to its memory oriented architecture. Is that true? Why is it that slow in my example?
2, Did I use Apache Ignite in a wrong way or are there some additional tuning options that I should use?
Here is the source-code I used in my example:
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs = null;
private static void initialize() throws ClassNotFoundException, SQLException
{
// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Create database tables.
stmt = conn.createStatement();
// Create table
stmt.executeUpdate("CREATE TABLE PIVOT_TEST (" +
" REGION VARCHAR, COUNTRY VARCHAR, ITEM_TYPE VARCHAR, SALES_CHANNEL VARCHAR, ORDER_PRIORITY VARCHAR, ORDER_DATE VARCHAR, ORDER_ID VARCHAR PRIMARY KEY, "
+ "SHIP_DATE VARCHAR, UNITS_SOLD NUMERIC, UNIT_PRICE NUMERIC, UNIT_COST NUMERIC, TOTAL_REVENUE NUMERIC, TOTAL_COST NUMERIC, TOTAL_PROFIT NUMERIC )");
}
private static void fill() throws ClassNotFoundException, SQLException
{
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Populate table
PreparedStatement stmt =
conn.prepareStatement("COPY FROM 'LINK_TO_CSV_FILE'" +
"INTO PIVOT_TEST (REGION , COUNTRY , ITEM_TYPE , SALES_CHANNEL , ORDER_PRIORITY , ORDER_DATE , ORDER_ID , SHIP_DATE , UNITS_SOLD , UNIT_PRICE , UNIT_COST , TOTAL_REVENUE , TOTAL_COST , TOTAL_PROFIT ) FORMAT CSV");
stmt.executeUpdate();
stmt = conn.prepareStatement("CREATE INDEX index_name ON PIVOT_TEST(COUNTRY)");
stmt.executeUpdate();
}
private static void getResult() throws ClassNotFoundException, SQLException
{
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Get data
stmt = conn.createStatement();
rs =
stmt.executeQuery("SELECT AVG(UNIT_PRICE) AS AVG_UNIT_PRICE, MAX(UNITS_SOLD) AS MAX_UNITS_SOLD, SUM(UNIT_COST) AS SUM_UNIT_COST, AVG(TOTAL_REVENUE) AS AVG_TOTAL_REVENUE , AVG(TOTAL_COST) AS AVG_TOTAL_COST, AVG(TOTAL_PROFIT) as AVG_TOTAL_PROFIT FROM PIVOT_TEST GROUP BY COUNTRY;");
retrieveResultSet();
}
private static void retrieveResultSet() throws SQLException
{
while (rs.next())
{
for(int i=0; i<rs.getMetaData().getColumnCount(); i++)
{
rs.getObject(i+1);
}
}
}
public static void main(String[] args) throws SQLException, ClassNotFoundException
{
Ignite ignite = null;
try
{
//--------------------------------CONNECTION-------------------//
IgniteConfiguration configuration = new IgniteConfiguration();
ignite = Ignition.start(configuration);
conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/");
initialize();
fill();
long endPrepTable = System.currentTimeMillis();
getResult();
long endGetResult = System.currentTimeMillis();
System.out.println("Get Result (s)" + " " + (endGetResult - endPrepTable)*1.0/1000);
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
ignite.close();
conn.close();
rs.close();
}
}
Thank you for your help!
