Optimizing the ASP.NET 2.0/3.5 Profile Provider Before You Go Live ASP.NET

Did you know there are two important stored procedures in ASP.NET 2.0/3.5 Profile provider that can be significantly optimized? If you use them without doing the necessary optimization, your servers will sink and take your business down with them during a heavy load.

Optimizing the ASP.NET 2.0/3.5 Profile Provider Before You Go Live

Problem: An ASP.NET Membership stored procedure caused the server to fail.

Solution: Eliminate use of temporary tables from SP.

Pageflakes was demoed at Microsoft’s MIX06 conference when we were in early beta stage. We were featured on Microsoft’s ASP.NET AJAX, site and the number of visits per day sky rocketed.Then one day we noticed that the server was gone; we restarted it, brought it back, and it died within an hour.After doing a lot of post mortem analysis on the server’s remaining body parts, we found that it had 100 percent CPU, super-high I/O usage,and that the hard drives were over heated and had turned them selves off.So,we went through hundreds of mega bytes of logs hoping to find a web service function that was killing our server.We suspected one web service in particular—the first function that loads a user’s page setup.So, we broke it up into smaller parts to see which part was taking most of the time (see Example ).

Pageflake’s most complicated function

We also timed smaller parts that we suspected could be taking most of the resources.But we could not find a single place in our code that was taking any significant time.Mean while, the users were shouting, manage ment was screaming, the support staff was complaining, and the developers were furiously sweating.

Now, you are saying,“You could have used SQL Profiler!” However, we were using the SQL Server work group edition back then, which did not have SQL Profiler.So,we had to hack our way through to get SQL Profiler running on a server some how(don’t ask how).And after running the SQL Profiler, boy, were we surprised! The settings property that was giving us so much trouble was aspnet_Profile_Get Profiles. Let’s analyze aspnet_ Profile_ Get Profiles in detail.First,it looks up the Application ID (see Example ).

Part of aspnet_profile_GetProfiles, which looks up the application ID from the application name

Part of aspnet_profile_GetProfiles that creates a temporary table to store results

If it is frequently called,the I/O will be too high due to the temporary table creation.It also runs through two very big tables—aspnet_Users and aspnet_Profile.The settings property is written in such a way that if one user has multiple profiles,it will return all of the user’s profiles. But because we normally store one profile per user,there’s no need to create a temporary table.More over, there’s no need for doing LIKE LOWER(@User Name To Match),which was always being called with a full username that can be matched directly using the equal operator.

aspnet_profile_GetProfiles gets a bypass code for running faster

IF @UserNameToMatch IS NOT NULL


SELECT u.UserName,u.IsAnonymous,u.LastActivityDate,p.LastUpdatedDate,


+ DATALENGTH(p.PropertyValuesString)+DATALENGTH(p.


ROM dbo.aspnet_Users u

INNER JOIN dbo.aspnet_Profile p ON u.UserId = p.UserId


u.LoweredUserName = LOWER(@UserNameToMatch)



BEGIN -- Do the original bad things

It ran fine locally. Now it was time to run it on the server. If we do something wrong here, we might not be able to see the problem immediately, but later realize the users profiles are messed up and there is no way to get them back.So,a tough decision had to be made:do we run this on a live production server directly without testing? We didn’t have time for testing anyway;we were already down.So,we gathered around, said a prayer, and hit the execute button on SQL Server Manage ment Studio.

The settings property ran fine.The server decreased from 100 percent CPU usage to 30 percent.The I/O usage also came down to 40 percent. We went live again.We were saved that day!

Accessing the Use of Profile Provider

aspnet_Profile_Get Properties is another settings property that is called on every page load and web service call because we use Profile provider extensively. It is called when ever you access properties on Profile object (see Example).

asp net_Profile_Get Properties is called whenever you try to access Profile object in Context

CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]

@ApplicationName nvarchar(256),



@CurrentTimeUtc datetime



DECLARE @ApplicationId uniqueidentifier

SELECT @ApplicationId = NULL

SELECT @ApplicationId = ApplicationId

FROM dbo.aspnet_Applications

WHERE LOWER(@ApplicationName) = LoweredApplicationName

IF (@ApplicationId IS NULL)


DECLARE @UserId uniqueidentifier


SELECT @UserId = UserId

FROM dbo.aspnet_Users

WHERE ApplicationId = @ApplicationId

AND LoweredUserName =


IF (@UserId IS NULL)


SELECT TOP 1 PropertyNames,PropertyValuesString,PropertyValuesBinary

FROM dbo.aspnet_Profile

aspnet_Profile_GetProperties is called whenever you try to access Profile object in Context(continued)

WHERE UserId = @UserId
UPDATE dbo.aspnet_Users
SET LastActivityDate=@CurrentTimeUtc
WHERE UserId = @UserId
ENaspnet_Profile_GetProperties’s 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
(1 row(s) affected)

First it does a SELECT on aspnet_application to find the application ID from the application name.You can easily replace this with a hardcoded application ID inside the settings property and save one SELECT that happens on every call.Usually we run only one application on our production server, so there’s no need to look up the application ID on every single call. ASP.NET Member ship provider is built to supportmultiple applications on the same data base,and as a result, all the tables and settings properties try to first identify the application and then do their job. It’s a real waste of processing power and space when you have only one application on your database.

The I/O statistics may not look that bad, but from client statistics you can see how expensive it is (see Figure).

Get Properties’s client statistics taken from SQL Server Manage ment Studio—you can turn on Include Client Statistics from the Query menu

Get Properties’s client statistics taken from SQL Server Manage ment Studio—you can turn on Include Client Statistics from the Query menu

Now look at the last block where the aspnet_users table is updated with Last Activity Date.This is the most expensive block. Figure shows the cost of that line is 82 percent compared to the cost of the whole settings property.

The cost of a single UPDATE statement is 82 percent of the whole setting property’s cost.The query plan is generated from SQL Server Management Studio by turning on Include Actual Execution Plan from Query menu

The cost of a single UPDATE statement is 82 percent of the whole setting property’s cost.

The update is done to ensure Profile provider remembers when 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, perhaps just when a user first logs in or logs out.In our case, many web services are called while user is still on the page (the only page—the Start page). So,we can easily remove the UPDATE statement to save a costly update on the giant aspnet_users table on every single web service call that needs the Profile object.

Using Email for a Username

The Member ship class has a method—Create User—that can be used to create user accounts.You can specify anything in the user name and pass word fields as long as it satisfies the password policy defined in web.config.This function creates an entry in both aspnet_users and aspnet_membership tables.

Using Email for a Username

Problem: Using email as a username broke the password recovery option.

Solution: Include the email address during account creation.

In Dropthings,we use an email address as the username in the ASP.NET 2.0/3.5 Member ship provider.During signup, a user account is created using the Member ship.CreateUser function (see Example ).

Creating user using Membership class

Membership.CreateUser( email, password );

However, users started complaining:


I got the email invitation.I went to your site and tried login, and it said the username or password is wrong. So,I tried Signup. Signup said my username was already taken.Then I went to forgot password to retrieve the password.It shows something is wrong and the password email cannot be sent.I am stuck.Please help!

Here’s the problem.When we use the code in Example 8-16,it creates a row in aspnet_users table using the email address as the username. But in the aspnet_ member ship table, the row it creates contains null in the email column.Therefore,the user cannot use the “forgot password” option to request the password because the email address is null. So,we had to run the SQL shown in Example to fix it.This code sets the user’s email address in the aspnet_member ship table from the user name field in aspne _ users table.

Cleaning up users’ invalid email addresses

Cleaning up users’invalid email addresses (continued)

where loweredemail is null and
applicationID = '...'

However, the applicationID is something that you need to specify for your own application.You can find the ID from aspnet_application table.To fix this problem we then added the email address as the third parameter to the Create User function. See Example

The Proper way of creating user account using Member ship class

Membership.CreateUser(email, password, email);

We had not noticed that this overloaded function had created users accounts in the aspnet_member ship table,which had the email address set to null.Unless you specify the email address while creating new user accounts, a user cannot use the “for got pass word” option to get his password emailed to him.

Changing a User name in the ASP.NET 2.0/3.5 Member ship Provider

Profile.User Name is a read-only field.So, how do you change a user name? This is an important capability when a user wants to change his email address, which, in turn,changes his user name. Although there is no way with Membership provider to change the user name of a user, there is a work around(see Example)

  1. Create a new user using the new email address.
  2. Get the password of the old account and set it to the new account. If you can’t get the old pass word via Member ship provider (the pass word is hashed),then ask the user for the password.
  3. Create a new profile for the new user account.
  4. Copy all the properties from the old profile to the new profile object.
  5. Log out the user from the old account.
  6. Auto log in to the new account.

Changing a username from code

if (Profile.UserName != newUserName)

Changing a username from code (continued)

// Create profile for the new user and copy all values from current profile

You can also go directly to the aspnet_member ship and aspnet_users tables and change the Lowered User Name, User Name, Email, and Lowered Email fields if you want.But that’s an un supported way of doing it. If the table schema changes in a later version of Member ship provider, your code will break.The best way to do it is to use Member ship provider’s own functions.

Rendering Page Parts As JavaScript

A giant page full of HTML works best if the whole page can be cached on the browser.You can do this by using HTTP response caching headers, either by injecting them manually or by using the @OutputCache tag directive on ASPX pages:

But this caches the entire page on the browser for one day. If you have a page with static and dynamic parts, you cannot use this output caching at page level.Generally, the header, logo,left - side navigation menu, and footer are static parts. Sometimes there are many static sections in the body part that do not change frequently. All these, when combined, take up a significant amount of download time.Users have to down load the entire page again and again when a significant part never changes. If you could cache those static parts on the browser, you could save a lot of bytes every time the page downloads.

If the whole page size is 50 KB, at least 20 KB is static and 30 KB might be dynamic.If you can use the page fragment’s client-side caching (not ASP.NET’s server side page output cache),you can save 40 percent in download time easily.Moreover, no request is sent to the server for those static parts because they are already cached on the browser. Thus, the server doesn’t have to process the giant page at every load.

ASP.NET offers page fragment caching using @Outputcache, which is good, but that caching is on the server side.It caches the output of user controls and serves them from the server-side cache. But you cannot eliminate the download of those costly bytes. It just saves some CPU power on the server, which doesn’t have much benefit for users.

The only way to cache part of the page is to allow the browser to download those parts separately and make those parts cacheable just like images,CSS, or Java Script. So, we need to down load page frag ments separately and cache them on the browser’s cache.

I Frame is an easy way to do this, but it makes the page heavy and does not follow the parent’s page CSS. Inside I Frame, you need to down load Ajax frame works again along with any other Java Script that you might need. Although the down load can be fast because files are coming from the cache, down loading the whole frame work and lots of Java Script again will put significant stress on the browser.

There is a better way:use JavaScript to render the content of the page;that Java-Script will get cached on the browser’s cache. Here’s the idea:

  1. Split the whole page into multiple parts.
  2. Generate page content using JavaScript. Each cacheable part is Java Script,which is then rendered to HTML.
  3. Cache the cacheable parts with the browser so they aren’t downloaded again (until the user does a hard refresh or clear cache).The parts that are non-cachable and change frequently do not get cached by the browser.Consider the page layout
  4. shown in Figure

Typical homepage layout where the body section is dynamic and the header,footer,left menu,and logo are static

Typical homepage layout where the body section is dynamic and the header,footer,left menu,and logo are static

Because only the body section is dynamic, the rest of the page is fully cacheable.So,the Default.aspx that renders this whole page looks like Example.

Default.aspx with cacheable parts

<%@ Page Language="VB" AutoEventWireup="false" %><%@ OutputCache NoStore="true"Location="None" %>

Default.aspx with cacheable parts(continued)

Caching parts of a page on a browser eliminates downloading static blocks

Caching parts of a page on a browser eliminates downloading static blocks

The cached parts are 30 minutes older because the browser has not down loaded them at all and saved a significant amount of data transfer. Only the body part was down loaded from the server.

On the first visit, the page parts are downloaded one after another, as you see on Figure

On first visit, all the parts are downloaded from the server.The date in each block shows the same date time, which means it was just delivered from the server

On first visit, all the parts are downloaded from the server

But on second visit,only the Default.aspx down loads and the parts are instantly loaded from cache.Figure shows the instant loading of different cached parts of the page.

The download time for the parts is between 5 and 7 ms the second time, compared to the first time where each of them took more than 1 second to download.Thi shows you how fast the second visit is with cached page parts.

On second visit, the cached parts are served from browser cache instantly. So, the total down loaded bytes are only for the Default.aspx, not for the smaller parts of the page

On second visit, the cached parts are served from browser cache instantly

The cached Header.aspx; notice the ContentType is the only change compared to a standard ASPX page

The cached Header.aspx; notice the Content Type is the only change compared to a standard ASPX page (continued)

<form id="form1" runat="server"><div><h1>This is the big fat header. Lots of HTML</h1>

Generated on server at: <%= DateTime.Now %></div></form></body></html>

The content type has been set to text/html/javascript, which is something that must be done by hand

When you put an ASPX inside a Script tag,it doesn’t work because <script id=" Script1" src="Header.aspx"type=" text/java script"> expects Java Script output, not HTML output.If HTML output is provided, the browser simply ignores it.So,first of all,the Header. aspx must emit Java Script instead of HTML in order to work on a< script> tag. Second, the Java Script needs to render the Header. aspx’s HTML output using document.writelnAn HTTP Module intercepts all the.aspx calls. When a page is ready to be sent to the browser,check to see if the content type is text/html/javascript. If it is,then convert the page output to a similar Java Script representation.

Details about HTTP module

Create a response filter named Html2JS Page Filter.cs to override the response stream’s Write method and convert the page’s HTML to a Java Script representation. So, ASP.NET gives you generated HTML, and you convert it to a JavaScript representation that renders the original HTML on the browser.

Using HttpModule

You might wonder if you can use an HTTP handler to do this. For example, you need to intercept calls going to an *.aspx extension that is handled by ASP. NET’s default page handler, but you can’t register another handler to the same extension.In this situation, you need to use Http Module, which intercepts any in coming request to the ASP.NET pipeline. To do this, you:

  1. Get the entire page output as HTML.
  2. Filter out what is inside the <form> tag. ASP.NET always generates a <form> tag, and the content of the page is available inside of that (see Example ).

    Getting the generated HTML from the ASPX page and parsing out the content inside the <form> tag

  3. Remove the ViewState hidden field,other wise it will conflict with the View State on the Default.aspx (the default page already has its own View State).So, the View State <input> tag cannot be sent again to the browser.This means you can not use Control,which uses View State and is one short coming of this approach.Generally, cached parts are static content, so there should not be much need for View State anyway (see Example ).

    Removing the ViewState <input> field so that it does not conflict with Default.aspx page’s View State

    Regex re = new Regex("(<input.*?_ _VIEWSTATE.*?/>)",RegexOptions.IgnoreCase); pageContentInsideFormTag = re.Replace(pageContentInsideFormTag, string.Empty);
  4. Convert the entire HTML output to a JavaScript string format.The string contains an escaped HTML that can be set as inner HTML or can be used inside thedocument.write('') statement (see Example ).

    Example Convert the HTML out put to a Java Script string representation and eliminate new lines, spaces, apostrophes, etc.The resulting string can be set to an element’s inner HTML or it can be passed to document.write.

    Example .Convert the HTML output to a JavaScript string representation and eliminate new lines, spaces, apostrophes, etc.The resulting string can be set to an element’s inner HTML or it can be passed to document.write. (continued)

  5. Emit document.write, which writes the JavaScript string to the browser.The HTML is added to the page content (see Example ).

Example.Generate a document.write statement that will write the HTML on the browser

That’s pretty much the trick.Use a response filter to get the aspx output, then convert it to a Java Script representation. Use document.write to render the HTML on the browser DOM and get that Java Script cached.For convenience, an Http Module is used here to hook into the ASP.NET pipe line and wait for .aspx files to emit text/html/java script content. Then hook the response filter into the ASP.NET request pipeline.

The HttpModule in detail

The Http Module is very simple.It hooks the context’s Release Request State event, which is fired when the page output is ready to be sent to the browser.Inside the event handler, the response filter is called to convert the HTML to a Java Script representation (see Example ).

HttpModule hooks the response filter and intercepts the page render

Finally, the module is registered in web.config by adding an entry in the <httpModules> section (see Example).

Example .Registering the web.config entry

<httpModules><add name="Html2JSModule" type="Html2JavascriptModule" /></httpModules>

You can use this approach in your .aspx files and save a significant amount of down load time on the user’s end. Although it slightly increases the first-time visit download—it takes an average of 200 ms for each script tag on net work round trip—it makes the second-time visit a breeze. See the performance difference yourself: visit www.pageflakes.com and let the site load fully. Then close your browser, open it,and enter the URL again.See how fast it loads second time.If you use a HTTP debugger to monitor how much data is trans ferred, you will see that it takes only 10 to 12 KBs the second time, compared to about 400 KB on first time. All the page frag ments are cached on the browser’s cache and require no down load time on sub sequent visits as long as the cache doesn’t expire.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

ASP.NET Topics