0

We are writing a MMORPG and suppose we have the following tables. The location_dynamic_objects is the table going to be queried and updated HEAVILY. As you can see position_x, position_y, location_id columns are duplicated as well as the object type. But if we normalize and use joins we are to apply additional filters for the selected data. We plan to send all the location_static_objects ONCE to client, so there is no real point in keeping them together with location_dynamic_objects. Static objects represent unmovable data to be rendered and is send once to client on location load. Dynamic objects represent data being updated frequently like players, rockets, asteroids, etc and is constantly sent to the client, selecting depends on client position and location. Our question is should we give up normalization to achieve performance?

create table location_static_object_types (
  location_static_object_type_id integer auto_increment primary key,
  object_type_name                varchar(16) not null
);
create table location_static_objects (
  location_static_object_id      integer auto_increment primary key,
  location_static_object_type_id integer not null,
  location_id                    integer not null,
  position_x                     integer not null,
  position_y                     integer not null
);
create table location_dynamic_object_types (
  location_dynamic_object_type_id integer auto_increment primary key,
  object_type_name                varchar(16) not null 
);
create table location_dynamic_objects (
  location_dynamic_object_id      integer auto_increment primary key,
  location_dynamic_object_type_id integer not null,
  object_native_id                integer not null,
  location_id                     integer not null,
  position_x                      integer not null,
  position_y                      integer not null
);
OneMoreVladimir
  • 1,653
  • 3
  • 21
  • 32
  • 1
    I am not sure just because there could be a join or two your performance will be ruined. With proper indexing, tuning, maybe some app caching as well, things might be as quick as you need. – mconlin Apr 06 '13 at 23:27
  • 1
    You should only normalize when you have thoroughly tested and have evidence that says you need to go back a normal form. – Kermit Apr 06 '13 at 23:30
  • Even if we add indexes we need to filter on object type and the point is to send unfiltered data to client. – OneMoreVladimir Apr 06 '13 at 23:39
  • 1
    What do you mean if you add an index you have to filter the object type? How will your query be constructed? What if anything will be in the WHERE clause? – mconlin Apr 06 '13 at 23:43
  • We want to send all the static objects on location loading ONCE. In the other case it would be smth like where object_type = and many types or to invent one more field like object_behavior_type = 'dynamic'. – OneMoreVladimir Apr 06 '13 at 23:45
  • 1
    What are the differences between location_dynamic_objects and location_static_objects? – Dan Bracuk Apr 07 '13 at 03:06
  • Static objects represent unmovable data to be rendered and is send once to client on location load. Dynamic objects represent data being updated frequently like players, rockets, asteroids, etc and is constantly sent to the client, selecting depends on client position and location. – OneMoreVladimir Apr 07 '13 at 10:05

1 Answers1

2

Because denormalization increases the redundancy of your data, it increases your total data volume. For this reason it is most rare for a denormalization to improve performance of write accesses (creates and updates) to your data; the reverse is typically true. Further, even for read queries, denormalization trades off increased performance of a small set of queries, often just one, for decreased performance of all others acccessing the denormalized data. If you have properly employed artificial primary keys for your foreign key constraints, supplementd by corresponding uniqueness constraints on your natural (primary) keys, I would be amazed if you saw an iota of performance gain through denormalization.

Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
  • I do agree with you in general. But I don't like the implication that "proper normalization" involves using artificial primary keys. Those two things have nothing to do with each other. –  Apr 07 '13 at 10:41
  • My phrase in regards to artificial primary keys is actually "properly employed", which does bear on performance. – Pieter Geerkens Apr 07 '13 at 11:51