Do you know there are two important stored procedures in ASP.NET
2.0 Profile Provider that you can optimize significantly? If you
use them without doing the necessary optimization, your servers
will sink taking your business down with you during heavy load.
Here’re a story:
During March, Pageflakes
was shown on MIX 2006. We were having a glamorous time back then.
We were right on Showcase
of Atlas web site. The first company, not the second or third.
Number of visits per day were rising sky high. One day we noticed,
the server was no more. We restarted the server, brought it back,
again it died within an hour. After doing a lot of postmortem
analysis on the remaining of server’s body parts, we found that it
was having 100% CPU and super high IO usage. The hard drives were
over heated and turned themselves off in order to save themselves.
This was quite surprising to us because we were very intelligent
back then and we profiled every single web service function. So, we
went through hundreds of megabytes of logs hoping to find which
webservice function was taking the time. We suspected one. It was
the first function that loads a user’s page setup. We broke it up
into smaller parts in order to see which part is taking most of the
time.
private GetPageflake( string source, string pageID, string userUniqueName )
{
if( Profile.IsAnonymous ) { using ( new TimedLog (Profile.UserName, "GetPageflake" ))
{
You see, the entire function body is timed. If you want to learn
how this timing works, wait until I make a post on it. We also
timed smaller parts which we suspected were taking the most
resource. But we could find not a single place in our code which
was taking any significant time. Our codebase is always super
optimized (after all, you know who is reviewing it, me).
Meanwhile, users were shouting, management was screaming,
support staff was complaining on the phone. Developers were
fainting one by one. Remaining ones were furiously sweating and
blood vessels on their forehead were coming out etc. Nothing
special, just a typical situation we have couple of times every
month.
Now you must be shouting, “You could have used SQL Profiler, you
idiot!” We were using SQL Server workgroup edition. It does
not have SQL Profiler. So, we had to hack our way through to get it
running on a server somehow. Don’t ask how. After running
the SQL Profiler, boy, were we surprised! The name of the
honorable SP which were giving us so much pleasure was none
but the famous and the most pretigious one, which we all know by
name due to its incredible contribution to the development
of human civilization. Please welcome, the
great stored procedure “dbo.aspnet_Profile_GetProfiles”!
We used (and still use) Profile provider extensively.
Here’s the SP:
CREATE PROCEDURE [dbo].[aspnet_Profile_GetProfiles]
@ApplicationName nvarchar (256),
@ProfileAuthOptions int ,
@PageIndex int ,
@PageSize int ,
@UserNameToMatch nvarchar (256) = NULL ,
@InactiveSinceDate datetime = NULL
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications
WHERE LOWER (@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL )
RETURN
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL ,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
WHERE ApplicationId = @ApplicationId
AND u.UserId = p.UserId
AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
AND ( (@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER (@UserNameToMatch))
ORDER BY UserName
SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH (p.PropertyNames) + DATALENGTH (p.PropertyValuesString)
+ DATALENGTH (p.PropertyValuesBinary)
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND
i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
DROP TABLE #PageIndexForUsers
END
END
First it looks up for ApplicationID.
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications
WHERE LOWER (@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL )
RETURN
Then it creates a temporary table (why not table data type?) in
order to store profiles of users.
— Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL ,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
If it gets called very frequently, there will be too high IO due
to the temporary table creation. It also runs through 2 very big
tables – aspnet_Users and aspnet_Profile. The SP is written in such
a way that if one user has multiple profiles, it will return all
profiles of the user. But normally we store one profile per user.
So, there’s no need for creating a temporary table. Moreover,
there’s no need for doing
LIKE LOWER
(@UserNameToMatch). It is always called with a full user name which
we can match directly using the equal operator.
So, we opened up the stored proc and did a open heart bypass
surgery like this:
IF @UserNameToMatch IS NOT NULL
BEGIN
SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH (p.PropertyNames) + DATALENGTH (p.PropertyValuesString) + DATALENGTH (p.PropertyValuesBinary)
FROM dbo.aspnet_Users u
INNER JOIN dbo.aspnet_Profile p ON u.UserId = p.UserId
WHERE
u.LoweredUserName = LOWER (@UserNameToMatch)
SELECT @@ROWCOUNT
END
ELSE
BEGIN -- Do the original bad things
It ran fine locally. Now it was time to run it on the server.
This is an important SP which is used by the ASP.NET 2.0 Profile
Provider, heart of ASP.NET framework. If we do something wrong
here, we might not be able to see the problem immediately, but may
be after one month we will realize users profile is mixed up and
there’s no way to get it back. So, it was a pretty hard decision to
run this on a live production server directly without doing enough
test. We did not have time to do enough test anyway. We are already
down. So, we all gathered, said our prayer and hit the “Execute”
button on SQL Server Management Studio.
The SP ran fine. On the server we noticed from 100% CPU usage it
came down to 30% CPU usage. IO usage also came down to 40%.
We went live again.
Here’s another SP which gets called on every page load and
webservice call on our site because we use Profile provider
extensively.
CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]
@ApplicationName nvarchar (256),
@UserName nvarchar (256),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER (@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL )
RETURN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER (@UserName)
IF (@UserId IS NULL )
RETURN
SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
FROM dbo.aspnet_Profile
WHERE UserId = @UserId
IF (@@ROWCOUNT > 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate=@CurrentTimeUtc
WHERE UserId = @UserId
END
END
When you run the SP, see the statistics:
Table 'aspnet_Applications' . Scan count 1, logical reads 2, physical reads 0, read -ahead reads 0, lob logical reads 0, lob physical reads 0, lob read -ahead reads 0.
(1 row(s) affected)
Table 'aspnet_Users' . Scan count 1, logical reads 4, physical reads 0, read -ahead reads 0, lob logical reads 0, lob physical reads 0, lob read -ahead reads 0.
(1 row(s) affected)
(1 row(s) affected)
Table 'aspnet_Profile' . Scan count 0, logical reads 3, physical reads 0, read -ahead reads 0, lob logical reads 0, lob physical reads 0, lob read -ahead reads 0.
(1 row(s) affected)
Table 'aspnet_Users' . Scan count 0, logical reads 27, physical reads 0, read -ahead reads 0, lob logical reads 0, lob physical reads 0, lob read -ahead reads 0.
(1 row(s) affected)
(1 row(s) affected)
This stored proc is called whenever you access some
property on Profile object.
First it does a SELECT on aspnet_application to find out the
application ID from application name. You can easily replace this
with a hard coded application ID inside the SP and save some
effort. Normally we run only one application on our production
server. So, there’s no need to lookup application ID on every
single call.
From client statistics, you can see how expensive it is:
Then look at the last block where aspnet_users table is updated
with LastActivityDate. This is the most expensive one.
This is done in order to ensure Profile provider remembers when
was the last time a user’s profile was accessed. We do not need to
do this on every single page load and web service call. May be we
can do it when user first logs in and logs out. In our case, a lot
of web service is called while user is on the page. There’s only
one page anyway. So, we can easily remove this in order to save a
costly update on the giant aspnet_users table on every single web
service call.
Some tweakings you should do on your web.config if you are using ASP.NET 2.0 Membership Provider before…
Check out this post telling you what you have to do before you go live with your ASP.NET 2.0 membership…
Some tweakings you should do on your web.config if you are using ASP.NET 2.0 Membership Provider before…
hello i think it’s a nice blog. please look at this echanger e-gold to wmz.
http://panegold-wmz.net
I avoid all MSFT membership, profile and role management code. I have extended these classes that use my own schema. I compared, I removed total 36 columns from different tables used for these! I don’t all those crap for app.
Very good article, but I am unsure exactly where are you inserting the code above starting with ‘IF @UserNameToMatch IS NOT NULL’ into the original stored procedure. Could you please clarify?
Thanks so much, Jacque