Hi Scott, we inherited a project that used uuid's for it's unique database ids and while it makes it nice and portable, it does significantly slow down a lot of queries. With numeric primary keys the db only has to parse the numbers and has a more native understanding of basic integers, whereas with a uuid the entire 35 character string has to be processed every time a lookup is done which can add more time than you'd first imagine.
Our end solution (for a rebuild) was to use numeric primary keys but also give each record it's own "unique id" using a uuid. It makes for some creative queries sometimes but can make a big difference in portability. It comes down a great deal to what you're doing with the data - if you have a lot of joins etc then uuids can really be a pain in the backside. One good example is spectra - while it could be optimised a fair amount, it was still at least three or four times as slow to retreive a record as a normal query would have been. My personal pref is numeric ids - using a sequence object if possible (or a workaround). Toby Wednesday, January 22, 2003, 9:45:24 AM, you wrote: SB> Sorry to ask probably a question thats had a debate before, but.. i will so shutup and read. :D SB> Whats the negative sideeffects of using UUID instead of Integer (autonumber) for primary keys in a table. The reason i bring this up, as I always use a parent-child-sibling-infant type SB> relationship amongst various tables, one example off the top of my head was a awhile back where i had 3 tables that basically provide: SB> - Category (parent table) SB> - Category (parent table) SB> ---- File (child table) SB> ---- File (child table) SB> ------- file versions (sibling table) SB> ------- file versions (sibling table) SB> ------- file versions (sibling table) SB> ---- File (child table) SB> ---- File (child table) SB> ------- file versions (sibling table) SB> ------- file versions (sibling table) SB> ------- file versions (sibling table) SB> - Category (parent table) SB> ---- File (child table) SB> ---- File (child table) SB> ---- File (child table) SB> ---- File (child table) SB> - Category (parent table) SB> This situation called for the ability to not only associate 3 different tables into a heircahy based dataset, but it also called for i think 3-4 other tables that provided other pieces of SB> information per level, and in doing this i was forced to use a big meaty looking SQL SP, that made use of both UNIONs and Views, which worked great. SB> I did find that if i simply relied on the Autonumber system, the heirachy would be out of sync, as in the joining field for the child had the same value as the parent pk, but having said that, SB> the child pks also had the same value and so a child brach would mistakenly associate its children under another child? SB> ie: SB> parentID = 2 SB> childID = 1 SB> childParent = 2 SB> childID = 2 SB> childParent = 2 SB> childID = 3 SB> childParent = 2 SB> even though the childParent = 2, i did find that child referred to childID = 2 as its parent? SB> I did manage to find away around this so i didn't have to basically redesign the entire db, but i'm about to embark on a bigger project (probably the biggest i have undertaken yet) and i so want SB> this to be a finely tuned effecient running machine, and having remembering this scenario, i felt a bit nervous with simply utilising an "autonumbered" system or even a seperate table which has a SB> running pool of numbers that you take from. SB> Atleast with UUID i know that no matter what no other rowID through-out the entire database will have the same value? SB> Your thoughts would be appreciated. SB> Scott Barnes SB> eCommerce SB> Tourism Queensland / Sunlover Holidays SB> [EMAIL PROTECTED] SB> www.queenslandtravel.com SB> www.sunloverholidays.com SB> --- SB> You are currently subscribed to cfaussie as: [EMAIL PROTECTED] SB> To unsubscribe send a blank email to [EMAIL PROTECTED] SB> MX Downunder AsiaPac DevCon - http://mxdu.com/ --------------------------------------- Life is poetry - write it in your own words. --------------------------------------- Toby Tremayne Technical Team Lead Code Poet and Zen Master of the Heavy Sleep Toll Solutions 154 Moray St Sth Melbourne VIC 3205 +61 3 9697 2317 0416 048 090 ICQ: 13107913 --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
