467,146 Members | 1,277 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

Membership User ID is uniqueidentifier, could I use INT and map theto fields?

Hi,

I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.

I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):

UserID INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.

Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?

Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?

***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?
Jan 17 '08 #1
  • viewed: 6449
Share:
5 Replies
Technically, no, there isn't a way that you can do this. The number of
possible values for a GUID is 2^128, while the number of possible values for
a 32-bit integer is 2^32. You will run out of integers before you run out
of GUIDs.

Granted, you can say that you will never have more than 2^32 records in
the table. In that case, feel free to use a secondary id which is an
integer (it should have a unique index on it, of course).

The issue you have now is the mapping between the GUID and the integer.
Since GUIDs are not generated sequentially, you have to have some sort of
lookup which is going to be performed. Also, there is the issue of
generating the id. You have to generate the id at the end of any add
operation, and you will have to serialize access to the generation of the id
(or you will have gaps), and this can lead to locking issues.

Generally speaking, I don't know that going to an integer is the best
solution here. Have you done some performance testing to verify that the
difference between comparing GUIDs and ints is that much of a difference?
I'm just guessing here, but if you are running SQL Server on a 64 bit
machine (which anyone who is serious about running SQL server ^should^ be
doing) it might not be as drastic of a difference than compared to doing the
same comparison on a 32 bit machine.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DotNetNewbie" <sn***********@yahoo.comwrote in message
news:8f**********************************@c4g2000h sg.googlegroups.com...
Hi,

I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.

I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):

UserID INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.

Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?

Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?

***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?
Jan 17 '08 #2
On Jan 17, 2:44*pm, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guard.caspershouse.comwrote:
* * Technically, no, there isn't a way that you can do this. *The number of
possible values for a GUID is 2^128, while the number of possible values for
a 32-bit integer is 2^32. *You will run out of integers before you run out
of GUIDs.

* * Granted, you can say that you will never have more than 2^32 records in
the table. *In that case, feel free to use a secondary id which is an
integer (it should have a unique index on it, of course).

* * The issue you have now is the mapping between the GUID and the integer.
Since GUIDs are not generated sequentially, you have to have some sort of
lookup which is going to be performed. *Also, there is the issue of
generating the id. *You have to generate the id at the end of any add
operation, and you will have to serialize access to the generation of the id
(or you will have gaps), and this can lead to locking issues.

* * Generally speaking, I don't know that going to an integer is the best
solution here. *Have you done some performance testing to verify that the
difference between comparing GUIDs and ints is that much of a difference?
I'm just guessing here, but if you are running SQL Server on a 64 bit
machine (which anyone who is serious about running SQL server ^should^ be
doing) it might not be as drastic of a difference than compared to doing the
same comparison on a 32 bit machine.

--
* * * * * - Nicholas Paldino [.NET/C# MVP]
* * * * * - m...@spam.guard.caspershouse.com

"DotNetNewbie" <snowman908...@yahoo.comwrote in message

news:8f**********************************@c4g2000h sg.googlegroups.com...
Hi,
I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.
I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):
UserID * *INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.
Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?
Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?
***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?- Hide quoted text -

- Show quoted text -
This is what I planned on doing:

1. whenever someone registers to my application using asp.net
memership (writing to aspnet_users, aspnet_members), if the insert
passes, I will do another insert into my own Users table and pass
along the uniqueidentifier that asp.net-membership created.

This seems to work just fine, the only thing I'm worried about is, say
someone wants to integrate another web application with mine, and they
also are using asp.net membership. Will my 'hack' ruin the membership
sharing capability that asp.net membership has??
Jan 17 '08 #3
The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to be
dependent on that primary key, which is nonclustered in the default scenario.
As long as you leave this arrangement alone, and add your int column at the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first,
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com
"DotNetNewbie" wrote:
Hi,

I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.

I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):

UserID INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.

Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?

Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?

***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?
Jan 17 '08 #4
On Jan 17, 3:20*pm, Peter Bromberg [C# MVP]
<pbromb...@yahoo.NoSpamMaam.comwrote:
The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to be
dependent on that primary key, which is nonclustered in the default scenario.
As long as you leave this arrangement alone, and add your int column at the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first, *
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site:http://www.eggheadcafe.com
UnBlog:http://petesbloggerama.blogspot.com
MetaFinder:http://www.blogmetafinder.com

"DotNetNewbie" wrote:
Hi,
I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.
I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):
UserID * *INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.
Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?
Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?
***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?- Hide quoted text -

- Show quoted text -
Peter, say I am designing an application like 'Google groups' that has
to inner join with the User's table to show the username, I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.

By the way the UserID INT will be a PK since I have another Users
table that has extra meta data on each user along with the
uniqueidentifier - I didn't want to use the aspnet_userprofile table
as its not normalized etc).
Jan 17 '08 #5
//
I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.
//
That's the issue on the table.
quote //"I think" //end quote
quote //"I have heard"// end quote

No one can do the work for you, you need to test it.

My suggestion was to not fear the uniqueidentifier, and give it a try.

But for 1,000,000 rows, I don't think there will be a big difference.

I use guid's as PK and FK's all the time.
Good luck.

"DotNetNewbie" <sn***********@yahoo.comwrote in message
news:51**********************************@21g2000h sj.googlegroups.com...
On Jan 17, 3:20 pm, Peter Bromberg [C# MVP]
<pbromb...@yahoo.NoSpamMaam.comwrote:
The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to
be
dependent on that primary key, which is nonclustered in the default
scenario.
As long as you leave this arrangement alone, and add your int column at
the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first,
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site:http://www.eggheadcafe.com
UnBlog:http://petesbloggerama.blogspot.com
MetaFinder:http://www.blogmetafinder.com

"DotNetNewbie" wrote:
Hi,
I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.
I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):
UserID INT
Membershipuserid uniqueidentifier
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.
Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?
Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?
***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?- Hide quoted text -

- Show quoted text -
Peter, say I am designing an application like 'Google groups' that has
to inner join with the User's table to show the username, I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.

By the way the UserID INT will be a PK since I have another Users
table that has extra meta data on each user along with the
uniqueidentifier - I didn't want to use the aspnet_userprofile table
as its not normalized etc).
Jan 18 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by matt@mailinator.com | last post: by
10 posts views Thread by perplexed | last post: by
5 posts views Thread by Jonathan Wood | last post: by
1 post views Thread by =?Utf-8?B?ZVByaW50?= | last post: by
6 posts views Thread by Jonathan Wood | last post: by
6 posts views Thread by Tem | last post: by
4 posts views Thread by John | last post: by
3 posts views Thread by =?Utf-8?B?U2F2dm91bGlkaXMgSW9yZGFuaXM=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.