If you are really worried about the performance of your SQL Compact Edition database, switch all your primary keys for unique indexes and always let the engine generate the UNIQUEINDENTIFIER's GUID value through the ROWGUIDCOL property - never ever use the Guid.NewGuid() function.
Looking for more performance material to publish about SQL Compact Edition, I found myself testing the performance of GUIDs when inserting on an indexed table. Performance will be marginally slower when you insert a GUID on a PRIMARY KEY column than when inserting it on a UNIQUE INDEX. In a 10,000 row insert sample it can be as low as half a second, but nevertheless this is an interesting result: PKs are slower.
Now, if you remove the ROWGUIDCOL property from the UNIQUEIDENTIFIER column and start generating the GUID values yourself, be prepared for a big surprise: the insertion time tripled (10.5 to 30.7 seconds on my battle-scarred JasJar). Is the Guid.NewGuid() function that slow?
Subscribe to:
Post Comments (Atom)
2 comments:
I think this behaviour is becouse the PRIMARY KEY is always clustered, don't you think?
Found a very interesting article. Although it is not specific to SQL Compact Edition it discusses the matter of using GUIDs as Primary Keys.
Post a Comment