🔒 Locked

[DB] Storing geometry

Started by LPVOID_CH Feb 18, 2008 at 2:33 AM 4 replies 1k views

Original Post

Feb 18, 2008 at 2:33 AM

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);


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.

Buckeye

Feb 18, 2008 at 9:13 AM

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?

LPVOID_CH

Feb 18, 2008 at 9:59 AM

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

GraphicsBas

Feb 18, 2008 at 10:20 AM

I have another suggestion why don't you just store the matrix in one column as binary data? This way you can just do

SELECT matrix FROM Geometry

whitout much casting..

GBS

LPVOID_CH

Feb 18, 2008 at 10:42 AM

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.

Buckeye

Feb 18, 2008 at 11:05 AM

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.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.