How to increase the Insertion performance?


#1

Hi,
I am using Distributed database with remote connection in orient db.I have bulk of data with many edges for example 1.5 lakhs data with 1 lakhs edges. My insertion takes hours and hours.I am using OIntentMassiveInsert to insert my data.How can i improve my insertion time?i have attached my java code for your reference

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;

import com.orientechnologies.orient.core.intent.OIntentMassiveInsert;
import com.orientechnologies.orient.core.sql.OCommandSQL;
import com.tinkerpop.blueprints.Vertex;
import com.tinkerpop.blueprints.impls.orient.OrientGraphFactory;
import com.tinkerpop.blueprints.impls.orient.OrientGraphNoTx;

import Model.GlobalProperties;

public class Insertion
{
	static boolean Var8 = false;
	static boolean Var5 = false;
	static boolean Var6 = false;
	static boolean isVar7 = false;

	static int batchCount = 0;

	public static void insertfun(String filepath, String Var1, String Var2, String logDate, String userId) throws FileNotFoundException, IOException
	{
		long startTime = System.currentTimeMillis();
		OrientGraphNoTx graph = null;
		OrientGraphFactory factory = null;
		try
		{
			factory = new OrientGraphFactory(GlobalProperties.orientDB_IP, GlobalProperties.orientDB_user, GlobalProperties.orientDB_pwd).setupPool(1, 10);
			factory.setRequireTransaction(false);
			factory.declareIntent(new OIntentMassiveInsert());
			graph = factory.getNoTx();
			File logFile = new File(filepath);
			if (logFile.exists())
			{
				JSONParser parser = new JSONParser();
				JSONObject dataFromFile = (JSONObject) parser.parse(new FileReader(logFile));
				JSONObject inputJsonData = (JSONObject) parser.parse(dataFromFile.toJSONString().replace("\\/", ""));

				if (inputJsonData != null)
				{
					String Var3 = "";
					String Var4 = "";

					if (inputJsonData.containsKey("String"))
					{
						Var3 = inputJsonData.get("String").toString();
					}
					if (inputJsonData.containsKey("String"))
					{
						Var4 = inputJsonData.get("String").toString();
					}
					
					
					
					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var5 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var5, null, "String", false, "String", Var1, Var2, logDate, userId, Var4, Var3);
						Var5 = true;
					}

					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var6 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var6, null, "String", true, "String", Var1, Var2, logDate, userId, Var4, Var3);
						Var6 = true;
					}

					
					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var7 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var7, null, "String", true, "String", Var1, Var2, logDate, userId, Var4, Var3);
						isVar7 = true;
					}
					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var8 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var8, null, "String", true, "String", Var1, Var2, logDate, userId, Var4, Var3);
						Var8 = true;
					}
	
					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var11 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var11, null, "String", false, "String", Var1, Var2, logDate, userId, Var4, Var3);
					}
					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var9 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var9, null, "String", true, "String", Var1, Var2, logDate, userId, Var4, Var3);
					}

					if (inputJsonData.containsKey("String"))
					{
						JSONArray Var10 = (JSONArray) inputJsonData.get("String");
						loadLogData(graph, Var10, null, "String", true, "String", Var1, Var2, logDate, userId, Var4, Var3);
					}
				}
				System.out.println("Time Taken to Create Vertex and Edge + " + (System.currentTimeMillis() - startTime) + " ms");
			}
		} catch (Exception ex)
		{
			System.out.println(" Exception :::: " + ex.getMessage());
		} finally
		{
			if (factory.exists())
			{
				factory.declareIntent(null);
				if (!graph.isClosed())
				{
					graph.shutdown();
				}
				factory.close();
			}
		}
	}

	@SuppressWarnings("unchecked")
	private static void loadLogData(OrientGraphNoTx graph, JSONArray jsonArr, JSONObject jsonObj, String className, boolean edgeRequired, String edgeName, String Var1, String Var2, String logDate, String userId, String Var4, String Var3)
	{
		try
		{
			String defaultQueryInfo = "and Var1 = '" + Var1 + "' and Var2 = '" + Var2 + "'  and logDate = '" + logDate + "'";
			System.out.println("Loading object : " + className);
			if (jsonArr != null)
			{
				for (int i = 0; i < jsonArr.size(); i++)
				{
					JSONObject finalJsonObject = (JSONObject) jsonArr.get(i);
					if (finalJsonObject != null && !finalJsonObject.isEmpty())
					{
						// Default Values
						finalJsonObject.put("Var1", Var1);
						finalJsonObject.put("Var2", Var2);
						finalJsonObject.put("userId", userId);
						finalJsonObject.put("logDate", logDate);
						finalJsonObject.put("String", Var3);
						if (className.equals("String"))
						{
							finalJsonObject.put("String", Var4);
						}

						// Create Vertex
						Vertex v = graph.addVertex("class:" + className, finalJsonObject);
						batchCount++;
						if (edgeRequired)
						{
							String query = null;

							if (className.equals("String"))
							{

								if (Var5)
								{
									String sName = v.getProperty("String");
									String query7 = "select  from Var5 where  = '" + sName + "' " + defaultQueryInfo;
									if (query7 != null)
									{
//										System.out.println("Mysql Query : " + query7);
										for (Vertex v1 : (Iterable<Vertex>) graph.command(new OCommandSQL(query7)).execute())
										{
											//v.addEdge("String", v1);
											batchCount++;
											graph.addEdge(null, v, v1, "String");
										}
									}

								}
							}

							if (className.equals("String"))
							{
								if (isVar7)
								{
									String Var4Loc = v.getProperty("String");
									query = "select  from Var7 where Var4 = '" + Var4Loc + "' " + defaultQueryInfo;
								}
							}
							if (className.equals("String"))
							{
								if (Var5)
								{
									String port_index = v.getProperty("String");
									String Var4Loc = v.getProperty("Var4");
									query = "select  from Var5 where index = '" + port_index + "' and Var4 = '" + Var4Loc + "' " + defaultQueryInfo;
								}
								
								
							}
							
							if (className.equals("String"))
							{
								String Var3 = v.getProperty("String").toString();
								
								String query1 = "select from Var11 where Var3 = '" + Var3 + "'";
								for (Vertex v1 : (Iterable<Vertex>) graph.command(new OCommandSQL(query1)).execute())
								{
									//v.addEdge(edgeName, v1);
									batchCount++;
									graph.addEdge(null, v, v1, "String");
								}
								JSONArray Arr = (JSONArray) finalJsonObject.get("String");
								query = "select from  where " + Arr + " contains Var12";
								
							}

							if (className.equals("String"))
							{
								String Var12 = v.getProperty("String").toString();
								{
									 query = "select from Var6 where Var12 = '" + Var12 + "'";

								}
								
							}
							if (query != null)
							{
//								System.out.println("Mysql Query : " + query);
								for (Vertex v1 : (Iterable<Vertex>) graph.command(new OCommandSQL(query)).execute())
								{
									//									v.addEdge(edgeName, v1);
									batchCount++;
									graph.addEdge(null, v, v1, edgeName);
								}
							}
						}

					}

					if (batchCount > 100)
					{
						graph.commit();
						batchCount = 0;
					}
				}
			}
			if (jsonObj != null)
			{
				if (jsonObj != null && !jsonObj.isEmpty())
				{
					// Default Values
					jsonObj.put("Var1", Var1);
					jsonObj.put("Var2", Var2);
					jsonObj.put("userId", userId);
					jsonObj.put("logDate", logDate);
					jsonObj.put("String", Var3);

					// Create Vertex
					graph.addVertex("class:" + className, jsonObj);
					//batchCount++;
				}
			}
			graph.commit();
		} catch (Exception e)
		{
			System.out.println("Exception in @Loaddata : " + e.getMessage());
		}

	}

	/*if (Var8)
	{
		//String sName = v.getProperty("Var4");
		query = null;//"select  from Var8 where Var4 = '" + sName + "' " + defaultQueryInfo;
	}*/
}

[/details]


#2

Hi @luigidellaquila
I am sincerely looking forward for your answer.

Regards,
Harish


#3

Hi @Harish

The main problem here is the latency of the replication.
When you use OrientGraphNoTx, each record operation (eg. vertex/edge creation, set property and so on) involves data replication and a quorum operation; the easiest way to reduce the number of distributed interactions is to do your insert in transaction, so that you have only one distributed interaction per transaction (that can involve hundreds of single record operations). From our experience, the optimal size of a tx is a few hundreds of vertices/edges (eg. commit every 500 created records).

I hope it helps

Thanks

Luigi


#4

Hi @luigidellaquila
Thanks for the reply.Now I am using transaction as per your suggestion and Time has been reduced but It still takes about 1hr which is too long for my customers.So any further suggestion to improve the insertion?