Hello,
I have a few large DB tables with 1-2 millions of data records.
I am trying to implement kind of the following idea(double buffering):
Read X values(e.g. 1000) from the database, copy them to memory, plot the array(or whatever data structure) in the JME update method.
However, after the values have been copied from memory, the future variable should be done and I can start reading the next 1000 values - but the thread should only start drawing after the previous X values have already been displayed/used.
Can anyone suggest how to implement this?
Up till now I have done the following:
objectArray - contains all objects that need their position set(this is what I desire to update)
GlobalTools - contains global data
updatePosition - retrieves data from DB and copies it into memory
PositionData - data structure which stores position data from DB
[java]for(VizObject obj : GlobalTools.objectArray)
{
try{
if(posData == null && future == null)
{
TempPosData tpd = new TempPosData(obj.getId(), startIndex, endIndex);
tempPosDataArr.add(objIndex, tpd);
future = GlobalTools.executor.submit(updatePosition);
startIndex += 1000;
endIndex += 1000;
}
else if (future != null)
{
if(future.isDone())
{
posData = future.get();
future = null;
}
}
else if(future.isCancelled())
{
future = null;
}
objIndex++;
}
catch(Exception e){
e.printStackTrace();
}
}[/java]
UpdatePosition method:
[java]private Callable <PositionData> updatePosition = new Callable<PositionData>()
{
@Override
public PositionData call() throws Exception {
int objId = 0;
long sI = 0, eI = 0;
TempPosData tpd = tempPosDataArr.get(genIndex);
objId = tpd.getObjId();
sI = tpd.getStartIndex();
eI = tpd.getEndIndex();
// Data data = myWorld.getData();
// PositionData pd = myWorld.getData();
// myWorld?
genIndex++;
posData = new PositionData(objId);
try {
PreparedStatement statement = ConnectToDatabase.con.prepareStatement("SELECT " +
"pos_x, pos_y, pos_z, pos_time FROM xyz_pos " +
"WHERE vo_id = "+ objId +" LIMIT " + sI + ", " + eI);
ResultSet result = statement.executeQuery();
while(result.next())
{
posData.add(new Vector3f(Float.parseFloat(result.getString(1)),
Float.parseFloat(result.getString(2)), Float.parseFloat(result.getString(3))), result.getTimestamp(4));
System.out.println("A Vector: " + new Vector3f(Float.parseFloat(result.getString(1)),
Float.parseFloat(result.getString(2)), Float.parseFloat(result.getString(3))).toString());
}
//startIndex += GlobalTools.readLimit;
//endIndex += GlobalTools.readLimit;
} catch (SQLException e) {
JOptionPane.showMessageDialog(null,
"Could not retrieve desired data.",
"Database query error",
JOptionPane.ERROR_MESSAGE);
e.printStackTrace();
}
return posData;
}
};[/java]
I need 1 PositionData instance for every object, is this correct?
Moreover, how will I go about plotting the data(I have implemented an obj.setPosition(Vector3f x) method) ??
Thank you.
Ok… so why the update method?
You can do the sql stuff in another thread, and as soon as it’s ready(ResultSet.next()) do an enqueue method for the specific object.
Also nodes have UserData configs and names… so u don’t really need PositionData class
I did not know about UserData. I will look into it, but for now I will try to get it working with my own class.
Regarding the update method, I am using it to update the position and rotation every frame. Is there a better way to do this?. The UpdatePosition method is not inside the update method - only the part where I iterate through all of the objects and create the new threads in order to read from the DB.
Should I create a new class for the SQL? Similar to
[java]// This class extends Thread
class ReadPosFromDB extends Thread {
// This method is called when the thread runs
public void run() {
…
}
}
[/java]
Or should I use callable in the above so that I can return my complete posData object?
Should I place an enqueue method after I have finished copying all my results to the PosData or immediately after I have the ResultSet(after the while?)?
I guess I would do something like:
[java]application.enqueue(new Ruunable() {
for(int i = 0; i < posData.getPositions().size(); i++)
obj.setPosition(posData.getPositions().get(i));
});[/java]
@avdan said:I am trying to implement kind of the following idea(double buffering):
Read X values(e.g. 1000) from the database, copy them to memory, plot the array(or whatever data structure) in the JME update method.
However, after the values have been copied from memory, the future variable should be done and I can start reading the next 1000 values - but the thread should only start drawing after the previous X values have already been displayed/used.
Can anyone suggest how to implement this?
I started writing a rather lengthy, and probably confusing, reply. The answer is "Get this book" http://www.amazon.com/Java-Concurrency-Practice-Brian-Goetz/dp/0321349601
Are you planning on drawing 1-2 million objects in the scene? You're going to see issues with large numbers (thousands) of geometries.
Regarding your actual SQL code, you're losing all of the benefits of prepared statements by doing this:
[java]
PreparedStatement statement = ConnectToDatabase.con.prepareStatement("SELECT " +
"pos_x, pos_y, pos_z, pos_time FROM xyz_pos " +
"WHERE vo_id = "+ objId +" LIMIT " + sI + ", " + eI);
ResultSet result = statement.executeQuery();
[/java]
Not only is the statement not pre-compiled but it is also not parameterized. You should see some speed bump in running massive amounts of queries just by tweaking this.
[java]
// In constructor (the statement is now a filed)
statement = ConnectToDatabase.con.prepareStatement("SELECT pos_x, pos_y, pos_z, pos_time FROM xyz_pos " +
"WHERE vo_id = ? LIMIT ?, ?");
[/java]
And when you're ready to execute the query:
[java]
statement.setInt(1, objId);
statement.setInt(2, sI);
statement.setInt(3, eI);
ResultSet rs = statement.executeQuery();
[/java]
@sbook said:
I started writing a rather lengthy, and probably confusing, reply. The answer is "Get this book" http://www.amazon.com/Java-Concurrency-Practice-Brian-Goetz/dp/0321349601
Are you planning on drawing 1-2 million objects in the scene? You're going to see issues with large numbers (thousands) of geometries.
Thanks for the book recommendation and SQL tips. However, I need to solve the issue for this project in the near future, while digesting the book would probably take up a lot of time(though it is the way to go in the long run).
No, I do not have millions of objects, but millions of positions. I have to simulate the real behavior without using interpolation and there is a lot of data.
Ah ok, that makes things more simple… Does this have to run on super low-end hardware or something? Storing a few million Vec3f’s isn’t a big deal on most current hardware.
@sbook said:
Ah ok, that makes things more simple... Does this have to run on super low-end hardware or something? Storing a few million Vec3f's isn't a big deal on most current hardware.
Nope, lets say medium-end machines. However, the initial way I was doing it was taking a long time(minutes) on my machine:
Intel Core 2 Duo 2Ghz, 4GB DDRAM, nVidia 9600GT.
That is probably because I was storing a lot of values in a hashmap.
I have one row of data for every second of time. The way that I was doing it(just so I know it works) was to read all values into a position map and plot it in the update method. It was working, but the time to start the app was ranging from lets say 10s(with ~3 hours of data) to 1-2 minutes for 4 days of data.
I think that using this buffering technique with threads is one of the most efficient solutions available for my problem. But if you have any suggestions, feel free to share them:).
Using a list stored in order should be much faster than dealing with a hash map. If the data isn’t changing and you can fit it all in RAM, then that is the route I would go.
If the data is dynamic, then you would need to do updates from the database.
Efficiency only counts as long as you can meet your goals 
Also does rendering needs to be in realtime? or do you only need a video of it (for presentation ect)?
If so there is a screenshot filter somewhere here, that renders it frame by frame and stores it. With that you can make a video and thus bypass realtime constraints maybee.