ASP.NET Membership Provider, User ID GUID, and disk space

Question

I'm currently using the SQL Membership provider for ASP.NET, which uses GUIDs for the User ID. My application has several custom tables that have foreign key relations back to the User table and I'm concerned about the disk space and performance implications of the standard provider's use of GUIDs for user ID.

Has anyone run into space / performance issues related to this and if so are there custom approaches that people have implemented to address this?

Any insight or suggestions would be most appreciated.

Thanks

Answer 1

Could you not have a custom table which maps the GUID to an integer value which you can then use the integer in custom tables?

UserId guid
FriendlyUserId int //use this as FK in other tables?

Answer 2

I doubt you'll have any space issues as a result of using GUIDs rather than INT types for example. One thing I will warn you about is that you might be tempted to create clustered indexes on the GUID columns in the database. DO NOT DO THIS. By default, GUIDs are random, and inserting random random data into a column that has a clustered index causes a few issues. Clustered, as you might know, means IN PHYSICAL STORAGE SEQUENCE. So when you insert a new random value (GUID) that row usually has to be inserted into the middle of the table. This can lead to massively fragmented indexes.

My advice would be to create a table that links the GUIDs to INT values (BIGINT if you expect that many users) and then use the INT everywhere else. Like Fermin just said.

Answer 3

If you are using SQL Server 2005, you may want to look at the NewSequentialId() method. Eric Swann provides a good overview of its use with the Membership provider. There is also a nice article on benefits of using sequential GUIDs over the default random ones. Here is a performance comparison excerpt from the article...

                  [Reads] [Writes]  [Leaf Pages] [Avg Page Used] [Avg Fragmentation] [Record Count]
  IDENTITY(,)     0     1,683    1,667  98.9%           0.7%              50,000
  NEWID()           0      5,386    2,486  69.3%          99.2%              50,000
  NEWSEQUENTIALID() 0      1,746    1,725  99.9%           1.0%              50,000