Multiple updates's or a single large one?

Enfernikus · 03-28-2010, 04:26 PM

I'm building a system employing Data Access Objects to abstract the database completely, now, I'm not a newbie in the matters of DBMS behaviors but I don't know of is update behaviors.

So, the system is something like this..

There is a base object which is called an "entity" this possess basic attributes like the entities id, the owners id, time created, etc.

The base object is then extend by, let's say, a user entity, which possesses other basic attributes like username, password, email, etc.

This user entity is then extend by the Agent entity which in turn possesses attributes like, office address, office phone, etc.

A single large update will look something like this,

sql Code:

UPDATE 
    entities 07214c6750d983a32e0a33da225c4efd 
JOIN users_entity 1575156248f1014d6bd95216907b7c57 ON 07214c6750d983a32e0a33da225c4efd.guid = 1575156248f1014d6bd95216907b7c57.guid 
SET 
    07214c6750d983a32e0a33da225c4efd.type = 'object',
    07214c6750d983a32e0a33da225c4efd.subtype = '5',
    07214c6750d983a32e0a33da225c4efd.owner_guid = '3',
    07214c6750d983a32e0a33da225c4efd.container_guid = '0',
    07214c6750d983a32e0a33da225c4efd.time_created = '1269789791',
    07214c6750d983a32e0a33da225c4efd.enabled = 'yes',
    1575156248f1014d6bd95216907b7c57.name = 'FOOO',
    1575156248f1014d6bd95216907b7c57.last_name = 'Galindo',
    1575156248f1014d6bd95216907b7c57.username = 'agalindo',
    1575156248f1014d6bd95216907b7c57.password = 'askjdbfasb',
    1575156248f1014d6bd95216907b7c57.salt = 'akjsdbak',
    1575156248f1014d6bd95216907b7c57.email = 'me@andresgalindo.us' 
WHERE 07214c6750d983a32e0a33da225c4efd.guid = 4

The update may join up to 5,6 tables each having perhaps 6-10 attributes.

This system coupled with Zend's ORM insures maximum code portability, the reason this is so important is because we're employing both Microsoft SQL servers, MySQL servers and a Key Value database modeled after Tokyo Tyrant ( I'll be adding custom support to the ORM for this ). The reason for this is PHP it to slow handle some of the data handling we'll be doing so part of the company independently developed and deployed the scraping server in C# & MSSQL which is archetypal to our application. So, some users, like agents may be on the server sharing a connection with the MSSQL server while others may be on the MySQL servers ( cost efficiency, hardware is cheap, software is not ). So, it really makes no sense to fork the source in such a way that the individual copies have queries which are molded around the particularities of the DBMS they're interacting with, thus this system was born.