In this example below we show how to create a multi threaded insert client to insert 100,000 records into SQLFire table. In this example below the table is partitioned with synchronous persistence turned on.The distributed system includes one locator and 5 data members.
1. Create Table as shown below
drop diskstore store1; CREATE DISKSTORE STORE1; drop table person; create table person (id int primary key, name varchar(40)) PARTITION BY COLUMN (id) REDUNDANCY 1 PERSISTENT 'STORE1' SYNCHRONOUS;2. Multi Threaded Insert client Code.
package pivotal.au.fe.sqlfire.insert;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.logging.Level;
import java.util.logging.Logger;
public class MultiThreadInsert
{
private String url = "jdbc:sqlfire://127.0.0.1:1527/";
private final int RECORDS = 100000;
private final int COMMIT_POINT = 10000;
private static final int nThreads = 4;
public MultiThreadInsert()
{
}
private Connection getConnection() throws SQLException
{
Connection conn = null;
conn = DriverManager.getConnection(url);
return conn;
}
@SuppressWarnings("unchecked")
public void start() throws InterruptedException, SQLException
{
Connection conn = getConnection();
final ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
ArrayList list = new ArrayList();
for (int i = 0; i < nThreads; i++) {
list.add(new RunData(conn, i+1));
}
long start = System.currentTimeMillis();
List<Future<?>> tasks = executorService.invokeAll(list, 5, TimeUnit.MINUTES);
for(Future<?> f : tasks){
try {
f.get();
} catch (ExecutionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
long end = System.currentTimeMillis() - start;
float elapsedTimeSec = end/1000F;
System.out.println(String.format("Elapsed time in seconds %f", elapsedTimeSec));
conn.close();
executorService.shutdown();
System.exit(0);
}
private class RunData implements Callable
{
int counter = 0;
int increment;
Connection conn;
private RunData(Connection conn, int increment)
{
this.increment = increment;
this.conn = conn;
}
public void run()
{
PreparedStatement stmt = null;
String sql = "insert into person values (?, ?)";
int counter = 0;
int dataSize = RECORDS / nThreads;
System.out.printf("Start: %d End: %d \n",(dataSize * (increment - 1)), (dataSize * increment));
try
{
stmt = conn.prepareStatement(sql);
for (int i = (dataSize * (increment - 1)); i < (dataSize * increment); i++)
{
counter = counter + 1;
stmt.setInt(1, i);
stmt.setString(2, "Person" + i);
stmt.addBatch();
if (counter % COMMIT_POINT == 0)
{
stmt.executeBatch();
conn.commit();
}
}
/* there might be more records so call stmt.executeBatch() prior to commit here */
stmt.executeBatch();
conn.commit();
System.out.printf("Number of records submitted %d.\n", counter);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public Object call() throws Exception
{
run();
return counter;
}
}
/**
* @param args
* @throws InterruptedException
* @throws SQLException
*/
public static void main(String[] args) throws InterruptedException, SQLException
{
// TODO Auto-generated method stub
MultiThreadInsert test = new MultiThreadInsert();
test.start();
}
}
3. Output when run as follows.Note: This was run on my MAC laptop which had 5 cache servers running on it. This would perform much better if I had 5 physical machines for each of the SQLFire cache server members.
Start: 0 End: 25000
Start: 75000 End: 100000
Start: 50000 End: 75000
Start: 25000 End: 50000
Number of records submitted 25000.
Number of records submitted 25000.
Number of records submitted 25000.
Number of records submitted 25000.
Elapsed time in seconds 4.409000
