Careful when querying on aspnet_users, aspnet_membership and aspnet_profile tables used by ASP.NET 2.0 Membership and Profile provider
Such queries will happily run on your development
environment:
Select
*
from
aspnet_users
where
UserName
=
'
blabla
'
Or you can get some user's profile without any problem
using:
Select
*
from
aspnet_profile
where
userID
=
'
…...
'
Even you can nicely update a user's email in aspnet_membership
table like this:
Update
aspnet_membership
SET
Email
=
'
newemailaddress@somewhere.com
'
Where
Email
=
'
…
'
But when you have a giant database on your production server,
running any of these will bring your server down. The reason is,
although these queries look like very obvious ones that you will be
using frequently, none of these are part of any index. So, all of
the above results in "Table Scan" (worst case for any query) on
millions of rows on respective tables.
Here's what happened to us. We used such fields like UserName,
Email, UserID, IsAnonymous etc on lots of marketing reports at
Pageflakes. These are some
reports which only marketing team use, no one else. Now, the site
runs fine but several times a day marketing team and users used
call us and scream "Site is slow", "Users are reporting extreme
slow performance", "Some pages are getting timed out" etc. Usually
when they call us, we tell them "Hold on, checking right now" and
we check the site thoroughly. We use SQL profiler to see what's
going wrong. But we cannot find any problem anywhere. Profile shows
queries running file. CPU load is within parameters. Site runs nice
and smooth. We tell them on the phone, "We can't see any problem,
what's wrong?"
So, why can't we see any slowness when we try to investigate the
problem but the site becomes really slow several times throughout
the day when we are not investigating?
Marketing team sometimes run those reports several times per
day. Whenever they run any of those queries, as the fields are not
part of any index, it makes server IO go super high and CPU also
goes super high - something like this:
We have SCSI drives which have 15000 RPM, very expensive, very
fast. CPU is Dual core Dual Xeon 64bit. Both are very powerful
hardware of their kind. Still these queries bring us down due to
huge database size.
But this never happens when marketing team calls us and we keep
them on the phone and try to find out what's wrong. Because when
they are calling us and talking to us,
they are not running any of the reports which brings the
servers down. They are working somewhere else on the site,
mostly trying to do the same things complaining users are
doing.
Let's look at the indexes:
Table: aspnet_users
Clustered Index = ApplicationID, LoweredUserName
NonClustered Index = ApplicationID, LastActivityDate
Primary Key = UserID
Table: aspnet_membership
Clustered Index = ApplicationID, LoweredEmail
NonClustered = UserID
Table: aspnet_Profile
Clustered Index = UserID
Most of the indexes have ApplicationID in it. Unless you put
Application='…' in the WHERE clause, it's not going to use
any of the indexes. As a result, all the queries will suffer from
Table Scan. Just put ApplicationID in the where clause (Find your
applicationID from aspnet_Application table) and all the queries
will become blazingly fast.
DO NOT use Email or UserName fields in WHERE clause. They
are not part of the index instead LoweredUserName and LoweredEmail
fields are in conjunction with ApplicationID field. All queries
must have ApplicationID in the WHERE clause.
Our Admin site which contains several of such reports and each
contains lots of such queries on aspnet_users, aspnet_membership
and aspnet_Profile tables. As a result, whenever marketing team
tried to generated reports, they took all the power of the CPU and
HDD and the rest of the site became very slow and sometimes
non-responsive.
Make sure you always cross check all your queries WHERE and JOIN
clauses with index configurations. Otherwise you are doomed for
sure when you go live.