Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Jun 25 13:42:49 CDT 2009
<<< String comparisons are always functions in libraries, vs a simple register to register compare >>> Hi John, Have a look this is an Intel Pentium machine command: <<< CMPS/CMPSB/CMPSW/CMPSD-Compare String Operands Compares the byte, word, or double word specified with the first source operand with the byte, word, or double word specified with the second source operand and sets the status flags in the EFLAGS register according to the results. >>> Just from this description one can find that comparing two strings, which differ in the first four bytes (double word) could be performed quicker than comparing two values in processor's registers as those values have to be loaded into registers first of all... ...furthermore if the hypothesis that MS SQL Server natural keys/indexes are kept very well compacted (and surrogate keys indexes can't be compacted that well - especially for large databases) is true then such indexes will occupy considerably less space than surrogate int Identity columns based indexes... Recap: ====== - it's technically possible that natural keys will provide considerably better performance than surrogate keys, and will use less resources? Thank you. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, June 25, 2009 9:49 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] SPAM-LOW: Re: Primary keys and entry points (was: Learning .Net -- PHP Instead?) >> A reason to not use a Guid is ressources. It takes a little more space It takes a LOT more space. An autonumber takes 4 bytes, a guid takes 16 bytes. That is 4 times the storage for each PKID. Furthermore it is much slower as it is a string of CHARACTERS and thus comparisons require string manipulations instead of just comparing two longs in a register. String comparisons are always functions in libraries, vs a simple register to register compare (a single X86 instruction). John W. Colby www.ColbyConsulting.com Gustav Brock wrote: > Hi Shamil > > As Jim tells, if you can't guarantee that your object will live at one location only and can/may be mixed with similar objects from elsewhere, use a Guid to identify it. As I'm sure you know, in Access this has been available since A97 (or A95?) where replication was introduced. > > A reason to not use a Guid is ressources. It takes a little more space and - random as it is by nature - you will most often need other keys for sorting, a timestamp for example. > For one project I mixed this. Simplified, a Guid was used for the Company table, while all other tables in some way related to the Company table and could use a normal Autonumber. > In another project I (again simplified) used a compound key for the Company table (or the the object identity) with one field to identify to location and one field with an Autonumber. If this is what you have in mind, you are of course right, that the single Id autonumber must be given up. > > /gustav -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4188 (20090625) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru __________ Information from ESET NOD32 Antivirus, version of virus signature database 4189 (20090625) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru