Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Auto incr number or GUID?
Message
From
27/10/2003 07:08:42
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
 
To
27/10/2003 06:42:32
Dorin Vasilescu
ALL Trans Romania
Arad, Romania
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00842581
Message ID:
00842853
Views:
20
Dorin,
A GUID is actually a 16 bytes value. Using it as 16 bytes I think timings would get down too.
Since you have the test code, would you test with this :
Local loGUID
loGUID = Createobject('GUIDLib')

lcPKey = loGUID.GetGUID16()

Define Class GUIDLib As Relation
  Procedure Init
  Declare Integer UuidCreate In 'RPCRT4.dll' String @pguid
  Declare Integer CoCreateGuid In 'Ole32.dll' ;
    string @pguid
  Declare Integer StringFromGUID2 In 'Ole32.dll' ;
    string rguid, String @lpsz, Integer cchMax
  Declare RtlMoveMemory In WIN32API ;
    INTEGER @DestNumeric, ;
    STRING @pVoidSource, ;
    INTEGER nLength
Endproc

  Procedure GUID16ToStr
  lparameters tcGUID16
  Local rguid
  rguid=Replicate(Chr(0),80)
  StringFromGUID2(tcGUID16,@rguid,40)
  Return Strconv(Left(rGUID,76),6)
endproc  

  Procedure GetGUID16
  Local pguid
  pguid=Replicate(Chr(0),16)
  UuidCreate(@pguid)
  Return pGUID
endproc  

  Procedure GetGUID
  Local pguid,rguid,lcFail
  lcFail = ""
  pguid=Replicate(Chr(0),16)
  rguid=Replicate(Chr(0),80)

  If Inlist(UuidCreate(@pguid),0,1824) && 1824 = LOCAL_ONLY
    Return Iif(StringFromGUID2(pguid,@rguid,40) # 0, ;
       Strconv(Left(rguid,76),6), lcFail)
  Endif
  Return lcFail
Endproc
Enddefine
Cetin

>You can think also to sys(2015)+WorkstationId as a character type PK solution. Only WorkstationId must be an unique value, saved in registry or local config files. GUIDs are very random generated values and I think that there is an overhead to mantain indexes (also think about one-to-many relationships).
>
>I've done some tests, maybe results can be useful:
>
>With 100000 records, only one PK fields, 38 bytes GUID type as in sample...
>http://support.microsoft.com/support/kb/articles/Q269/3/87.asp
>and right(SYS(2015),3) + 3 chars workstation id (12 in total)
>
>
>Index size GUID            : 6.786.548 bytes
>Index size SYS(2015)+WsId  :   863.744 bytes
>
>7.85 times more for GUID index
>
>After a REINDEX command
>Index size GUID            : 4.767.744 bytes
>Index size SYS(2015)+WsId  :   850.432 bytes
>
>5.60 times more for GUID indexes
>
>Average seek times , 100000 iterations, random keys
>Index on GUID         : 1.880 seconds
>The othe index        : 0.954 seconds
>
>
>
>>>Hi,
>>>I am designing database for my new application. I think to use AutoNumber as PK, however, it will cause problem while replication. Do you think combination of AutoNumber and BrandID as PK is a good solution? Will it complicated coding? how about GUID?
>>>
>>>Thank you
>>
>>As Sylvain said already,
>>Go with GUID.
>>
>>For a long discussion and sample on it check :
>>
>>http://oldlook.experts-exchange.com/Databases/FoxPro/Q_20764442.html
>>
>>Cetin
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform