Hi:
I wrote a simple clover code to replace an existing function using SQL-Loader. Using an XLS with 3 sheets, the execution time for sql-loader is 2,485 millisecond, for clover it is 5,124 milliseconds. Some XLS file may contain more than 30 sheets, the executime time using clover was very slow.
The graph has an XLS reader and inserts all records of all sheets into a DB table. for each sheet on a Excel file, change the sheetname, init graph object and execute. I need your help optimizing my clover code.
Below is my graph file, the transformation component is a java class MyReformat.java which copies the xls row into DB table.
----------------------------------------------------------------------------------
//if properties file has not been initialized, set it up now
if (propertiesFile == null) {
propertiesFile = cloverDir + File.separator + “defaultProperties”;
EngineInitializer.initEngine(null, propertiesFile, null);
initDBConnection();
}
//in a windows environment, clover needs this
copiedFile = copiedFile.replace(“\\”, “\\\\”);
cloverDir = cloverDir.replace(“\\”, “\\\\”);
String graphFile = cloverDir + File.separator + graphName.replace(“\\”, “\\\\”);
Properties p = new Properties();
p.setProperty(“origFileName”, origFileName);
p.setProperty(“copiedFile”, copiedFile);
p.setProperty(“sessionid”, sessionID);
p.setProperty(“cloverDir”, cloverDir);
p.setProperty(“fileSeparator”, File.separator.replace(“\\”, “\\\\”));
p.setProperty(“fileid”, String.valueOf(fileID));
TsExcelFile f = new TsExcelFile(copiedFile);
TransformationGraph graph = null;
TransformationGraphXMLReaderWriter graphReader = new TransformationGraphXMLReaderWriter(p);
GraphRuntimeContext runtimeContext = new GraphRuntimeContext();
runtimeContext.setUseJMX(false);
runtimeContext.setVerboseMode(false);
GraphExecutor executor = new GraphExecutor();
for (int i = 0; i < f.getNumsheets(); ++i) {
String sheetName = f.sheetNames[i];
p.setProperty(“sheetname”, sheetName);
try {
graph = graphReader.read(new FileInputStream(graphFile));
} catch (Exception e) {
logger.error(“Failed to read graph !\n” + e.getMessage());
return;
}
graph.addConnection(dbconn);
try {
GraphExecutor.initGraph(graph);
} catch (ComponentNotReadyException e) {
logger.error("Exception occurred: ", e);
return;
}
Future result;
try {
result = executor.runGraph(graph, runtimeContext);
while (result.isDone()) {
;
}
if (!result.get().equals(Result.FINISHED_OK)) {
logger.error(“Failed graph execution!”);
return;
}
} catch (Exception e) {
logger.error(“Failed graph execution!\n” + e.getMessage());
return;
}
} // end of for
}
Note:
The above code sets the properties of the graph file from Properties object. for each FOR-loop, the sheetname was changed in the Properties object, initialize a new Graph object, execute the Graph.
I also tried to dynamically modify the XLSReader object and set the sheetname. But the graph object did not detect the new sheetname.
Map <String, Node> nodes = graph.getNodes();
XLSReader xls = (XLSReader) nodes.get(“XLS_READER0”);
xls.setSheetName(sheetName);
xls.init(); <---- graph object did not detect the new sheetname.