what is the matter in using guids in a smart client against server scenario? RRS feed

  • Question

  • I am developing an application which consists in a smart client against a Server -with oracle db-. We use typed datasets to manage data between them. Our first aproach is using autonumeric, but we have found lot of issues: what happends when the pk comes back changed to the client? and a few more.

    I think guids were invented for this scenario, but i would want to know your opinion about them. Specially performance issues.

    Thanks in advance.

    Wednesday, August 9, 2006 5:18 PM


  • This kind of boils down to the age old question concerning what strategy you should adopt when generating primary keys. There are essentially two approaches you can take.

    You can let the RDBMS manage the generation of the primary key, using autonumeric in Oracle PL/SQL or identity in T-SQL. The main problem here is that when you're constructing a complex domain object graph or a multi-table dataset in memory, you need to know the primary key of a record so that you can specify the correct relationship in another record's foregin key field. This isn't too bad, as you can insert the record, read back its primary key value, and then set up the foreign key mapping. For more complex scenarios where you have cyclic references, you can schedule your database operations so that all insertions are performed first, followed by updates, and finally deletes. Then you simply have to schedule two inter-dependent records for insertion and an update, where the correct values for the foreign keys are set during the update. If you are using transactions during the insertion process, then the RDBMS will reserve a primary key and return its value, without actually inserting a record. This allows you to use auto-generated primary keys inside of transactions without problems.

    The second approach entails that you manage the generation of primary keys yourself. This can actually be a benefit in many situations, especially when you are implementing or utilizing an O/R mapper. The best solution here calls for creating an identity table for storing primary keys. This results in every record in the database having a unique primary key, even across different tables. You can then write an API layer in the database for generating primary keys, in the form of stored procedures and user functions. With this approach, you can generate a unique primary key as soon as you create a new domain object or record in memory in your domain logic layer, without having to first perform a full insertion.

    Most RDBMS don't support the auto-generation of GUID data columns, so you would need to write your own code to do that. The thing is, you can also use standard integers as primary key types when generating your own primary keys. In this case, the only benefit of using a GUID type instead of a 32-bit or 64-bit integer would be in the size of your primary key identifier space. A GUID is 128-bits, so it can be used to index 3.4 * 10^38 unique records, which is a lot. It's probably more than what you need in most scenarios, where a 32-bit or 64-bit integer for the primary key will suffice.

    If you need to generate your own primary keys using a common identity table for all of the records in your schema, I would stick with a 64-bit integer for the primary key. If you're using auto-generated primary keys for each table, a 32-bit integer usually provides enough indexing space, but not always. The GUID becomes useful when you need to uniquely catalog and/or identify a piece of data across different applications and/or data stores.

    Hope that answers your questions.

    Saturday, August 12, 2006 2:19 PM