0

I have a notes app where a user creates notes and they get synced to a database with column noteId, which is an auto_incrementing field. The problem is that it gets a little complicated when the user creates a not when not connected to the internet, so I have to assign a temporary noteId, and do a bunch of other stuff related to the hierarchy of the note system structure.

So I'm thinking of dropping the auto_increment option all together and having the local device (iPhone/iPad - Objective C) create a unique ID (possibly a timestamp) that could be used in the database as the noteId. This way if the user is offline and reconnects at a later time, syncing would be as easy as sending the unique ID.

Two questions:

  1. How does this affect performance? Let's say I have 500,000 notes in the database, and the user makes a change to one of his notes. With the auto_incrementing field I imagine it would be easier for the system to locate a noteId of 256,000, but with a general noteId system, would finding a noteId of 88689034 be a much lengthier process?
  2. What are good possibilities for generating a unique ID for a note locally on the user's device? Time stamp?
Snowman
  • 31,411
  • 46
  • 180
  • 303

4 Answers4

1

One of the most common ways of generating unique IDs is to use UUID/GUIDs. MySQL has a UUID function, .NET has GUID functions, and it's easy to write a function to create one in PHP or JavaScript... you don't specify what language the client app is in. ;) But yes, it can definitely affect performance. The best way to increase performance of UUIDs would be to convert the hex string into a binary number and store it across multiple INT columns.

EDIT: Just thought of a better idea... why do you even need to generate an ID at the device? Couldn't you just create the note, then when the device has connection, send the note to the server and let the server generate an ID at that point? Maybe on the device you would generate a temp ID for the device to keep track of it, but the real ID would still be generated by the server.

kitti
  • 14,663
  • 31
  • 49
  • I'm guessing I'd use the iPhone's UDID then, and append to it local auto_increment field? This would be a large number though. The performance decrease wouldn't be that bad? – Snowman Feb 29 '12 at 15:31
  • The UDID is just an identifier for the phone. You want to use UUIDs (universally unique identifiers). Try looking at this question: http://stackoverflow.com/questions/427180/how-to-create-a-guid-uuid-using-the-iphone-sdk – kitti Feb 29 '12 at 15:34
  • Ya that's exactly what I'm doing now. The problem is that the note heiarachy is a bit complicated. Each note can have children notes, and so on, so it forms a tree. And each note stores its path to the root note. If I have 10 levels, and assign temp Id's to each one, then I have to do so much when I get it back from the server. So I'm thinking it might be easier just to create local note Id's. Which way do you think might be worth it in the end? – Snowman Feb 29 '12 at 15:41
  • Yeah, you could do that too. The server just has one id (and one parent_id field), while the client has a local_id and remote_id (and local_parent_id, remote_parent_id). Use only the local ids as foreign keys on the client, when the client creates a note it leaves the remote fields blank, and when it sends the note to the server it updates those remote_id fields. But UUIDs can be handled fairly efficiently, and real-world use shows that it's not always that bad (Windows makes extensive use of GUIDs). If you can test, that's best, cause with performance it's hard to tell. – kitti Feb 29 '12 at 15:50
  • Ok so I'm now using UUIDs, and putting noteId's like: `2F1107571E8B4C168BB3230B2BA9DAB8` in my database. This won't cause any drastic performance issues? I went from noteId's of 154 to something that long. If I have a million entries in my DB, it won't be difficult for the system to search for a specific noteId? – Snowman Feb 29 '12 at 16:39
  • It will probably cause drastic performance issues if you store it that way (as a `CHAR` field). The UUID is a hexadecimal number, and can be converted to a series of 4 `INT` columns. Use those columns as your PK in the DB. Not as efficient as a single `INT` column, but better than a long `CHAR` field. – kitti Feb 29 '12 at 17:24
  • I don't get it..4 columns? Why would I do that? – Snowman Feb 29 '12 at 17:52
  • Because 4 `INT` columns take less space and are quicker to look up than a 32-character `CHAR`. Since the UUID is a string, each character uses 8 bits (size of a character in ASCII, might be larger depending on your charset), but each character is a hexadecimal number and has a total of only 16 possible values, or 4 bits. – kitti Feb 29 '12 at 19:25
1

The performance of the back end shouldn't be affected by the App performance, especially if the table has been designed properly the NoteID field in the DB has been indexed correctly. And indexing 500,000 records in a back end database system is not unreasonable. However that does not mean the DB will not require regular maintenance to update indexes etc.

Given the ability of users to create notes offline, then creating uuids locally as the key for the DB NoteID field does make sense, and there are plenty of SO questions that answer how to crete a UUID, such as how-to-create-a-guid-uuid-using-the-iphone-sdk

As for performance, the UUID is in the 32 character size range, which is probably less than a single line of text in one of your notes. So there would be minimal performance issues storing a uuid locally with the note and with sending it along with the note to the DB.

Community
  • 1
  • 1
Peter M
  • 7,309
  • 3
  • 50
  • 91
  • Ok so I'm now using UUIDs, and putting noteId's like: 2F1107571E8B4C168BB3230B2BA9DAB8 in my database. This won't cause any drastic performance issues? I went from noteId's of 154 to something that long. If I have a million entries in my DB, it won't be difficult for the system to search for a specific noteId? – Snowman Feb 29 '12 at 16:40
  • @mohabitar, the length of the key field affects performance, but I doubt it will be a noticeable difference. – Marcus Adams Feb 29 '12 at 17:03
1

I think that UUIDs are fine, and not a bad solution.

However, there is the security consideration of never trusting client information, which comes into play here with client generated IDs.

If the user can only affect their own notes, tied to their authenticated session, and not affect notes from other people simply by guessing their UUIDs, then you're good.

So, for example, if the note record also has a user_id field, and you only allow a user to read, insert, or update their own notes, you're good.

As far as performance goes, the length of the key field does affect performance, and it is a big jump from a an INT (4 bytes) and a CHAR or VARCHAR(32) field. Your indexes will also use a lot more memory/storage. You'd have to test to see if the change is noticeable.

Personally, with scalability in mind, I'd rather use the client's CPU cycles over my own, and the method that I use is identical to your current method.

If the notes can be updated from more than one place (eg., on the web via a browser or through the application), and you want the ultimate solution to this type of problem, at the cost of complexity, investigate version vectors.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
0

By notes do you mean Lotus Notes? Or something else? If something else, then what?

This is what uuids are intended to address - a timestamp is from adequate even on a very small clientbase. Or use a client identifier as well as a locally generated auto-increment value as the primary key.

Yes, the bigger the key the slower it is to query it - but the difference is relatively small.

symcbean
  • 47,736
  • 6
  • 59
  • 94