Sign in to follow this  

[DB] Storing geometry

This topic is 3590 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi all i'm developing a level editor which uses a database (postgres) to store the data. the db runs on separate server, so every level-designer works on the same database. i'll also use th db for my game. now, i have a lot of geometry data. the speed for the editor is not "too" important, but the speed for the game server will be important. for example if i have a 3d vector, i thought that i could store the X,Y,Z components in a string (varchar, comma separated) rather than in three float fields. or a better example is a 4x4 matrix, which would have 16 separated float fields.
db->sql("SELECT matrix FROM GEOMETRY");
String matrix = db->FieldByName("matrix").AsString;
Matrix4 matrix4 = CreateMatrixFromString(matrix);
or
db->sql("SELECT mat_11,mat_12,mat_13,... FROM GEOMETRY");
Matrix4 matrix4;
matrix4._11 = db->FieldByName("mat_11").AsFloat;
matrix4._12 = db->FieldByName("mat_12").AsFloat;
matrix4._13 = db->FieldByName("mat_13").AsFloat;
.
.
The second approach needs access to 16 fields while the first only needs access to one string. My question is would there be a speed difference or are the methods equal in speed. If so, i would tend to implement the first method, to avoid too mutch colums in my table.

Share this post


Link to post
Share on other sites
Is there a particular reason why you want to use text rather than floating point data?

Converting text to floating point is an extra time-consuming step. Also, in general, I have to believe that floating-point has to be fewer bytes. When transferring server-to-client, the fastest bytes are the ones not sent.

If game speed is important, designing the records to match the need of the game would seem more efficient.

Fixed size records would also decrease access times, would it not?

Share this post


Link to post
Share on other sites
Well, the only reason to use a string for a vector or a matrix is a more readable SQL statement. like i wrote above, with a string i only have to write:

"SELECT matrix FROM GEOMETRY"

rather than

"SELECT matrix_11, matrix_12, matrix_13,..,matrix_44 FROM GEOMETRY"

Of course, i could use the "SELECT *" method, but this is a bad idea, since i might have other fields in the same table.

I think your right, that the string variant is less performant because a float value as string uses more bytes than a simple float.

But for my editor, where absolute performance is not necessary, i'll stay with the string variant to avoid too much columns. Or any one knows a other solution with SQL?

thx for your suggestions

Share this post


Link to post
Share on other sites
ok, as binary i think it will be faster as a string and the casting wouldn't be a problem. but when the data is in binary mode, it is not stored in a readable format. so, if want to analyse or modify the data in a db-admin tool, the string variant would be more "readable" to debug.

Share this post


Link to post
Share on other sites
Sounds like ease of data maintenance in db_admin is your real intent. If so, you pretty much have to make data-loading during gameplay the lowest priority in development. Your editor will interpret the data, in any case, so the db structure needn't be a consideration.

The method you choose (your original question) should then be to ease programming and data maintenance whatever the cost in speed.

Loading via "SELECT matrix FROM GEOMETRY" is then the choice. Otherwise the data won't be easily interpreted in db_admin - it will be just a list of strings.

If you use the "SELECT mat_11, mat_12.." method, you may as well store the data as floating-point. The data fields will display the same in db_admin, whether it's string or floating-point.

Remember, however, that, if you make the game player your lowest priority, the game-player will make playing your game his lowest priority.

Share this post


Link to post
Share on other sites

This topic is 3590 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this