Have Geek Brain

ASP.NET Membership Provider, User GUID, and Disk Space

I am currently using the ASP.NET SQL Membership Provider that uses GUIDs for the user ID. My application has multiple user tables that have foreign key relationships back to the User table, and I am concerned about disk space and the performance implications of a standard provider using the GUID for the User ID.

Does anyone run into space / performance issues related to this and if there are any customized approaches people have implemented to solve this problem?

Any insight or suggestions would be most appreciated.

thanks

+1


a source to share


3 answers


I doubt you will have space issues as a result of using GUIDs rather than INT types. One thing I'm warning you about is that you might be tempted to create clustered indexes on GUID columns in the database. DO NOT DO IT. By default, GUIDs are random and inserting random data into a clustered index column causes several problems. Clustered, as you know, means "PHYSICAL STORAGE SEQUENCE". Therefore, when you insert a new random value (GUID), this row should usually be inserted in the middle of the table. This can lead to massively fragmented indexes.



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

+5


a source


Could you have a custom table that maps the GUID to an integer value, which can then be used as an integer in custom tables?



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


a source


If you are using SQL Server 2005 you can take a look at the method NewSequentialId()

. Eric Swann provides a good overview of its use with a membership provider. There is also a good article on the benefits of using sequential GUIDs over the default random ones. Here's an excerpt from the performance comparison 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
+1


a source






More articles:


All Articles