sql server 2005 - Are there more efficient alternatives to ASP.NET 2.0 anonymous Profiles? -

i discovered asp.net profiles few years , found quick , easy implement powerful functionality in web site. ended exclusively using "anonymous" profile properties because not using asp.net membership , wanted track information , provide personalization site's users though weren't logged in.

fast forward 3 years , struggling keeping size of aspnetdb database within 3 gb limit of hosting provider. decided year ago keep 6 months of profile data , every month have run aspnet_profile_deleteinactiveprofiles procedure followed support request host truncate database file.

the real downside have functionality implemented, web site relies on database , there downtime every time need truncate. today took cake - site down more 12 hours while doing maintenance on aspnetdb , ended creating failover copy of aspnetdb database web site online again (the original aspnetdb database still down write this).

last year rewrote of functionality because storing small collection of binary objects , converted store comma delimeted string of ids, making smaller storage.

i have looked getting more disk space, reality isn't worth have database grows 4 gb every year simple things keep list of each user's viewed products, keep track of category in "continue shopping" button take them back, , store total amount (currency only) in shopping cart. realistically, less 5% of data reused repeat visitors site, there no way tell in advance of these users make access profile information.

so question whether there more efficient way store profile data doesn't take space or if there alternatives creating profile database records every single user supporting functionality?

i took @ table profile provider, can vouch whether using store data using less disk space default provider?

update:

i did research , seems nvarchar(max) field direct replacement ntext fields used default implementation of aspnetdb, require half of disk space according this article , this one. means should able create new copy of aspnetdb, modify datatype throughout schema , code, , transfer data new database. should fix both way data stored , fragmentation occurred when shrink operation performed.

i have completed testing , moved solution production. @ first thought there problem because microsoft hard-coded ntext datatype stored procedure call inside of sqlprofileprovider. however, turns out sql server implicitly convert ntext parameter nvarchar(max). in short, didn't have change other types on propertynames , propertyvaluesstring columns of aspnet_profiles table.

the data had rewritten disk free space, ended saving 30% overall changing datatype.

another update:

i discovered although getting around 700 unique visitors every day, average number of daily "users" in aspnet_users table averages 4000-5000. theororized due fact users browsing without cookies. did testing , discovered user won't created if profile not updated, if write profile user (and profile) created on every request if cookies not enabled.

i working on workaround this. attempting write javascript ajax call webmethod. in theory, second request (the ajax call) should have .aspxanonymous cookie present if cookies enabled, , therefore can safely write profile. javascript injected page if beginning of session (determined session.isnewsession property). user should never aware of ajax call - there tell page whether cookies enabled can update profile.

of course, every subsquent request can check cookies collection ensure .aspxanonymous cookie present. create shared function can called both ajax called webmethod , page_load event directly, , use isnewsession property determine request allow run cookie check , subsequent profile update.

according microsoft, using session keep track of whether cookies enabled defeats purpose (because session depends on cookies). suggest persist arecookiesenabled value database, don't make mention of how supposed keep track of kept value - if cookies not enabled, how can link request database record?

i implemented workaround came in original post, turned out bit different described. fix can broken 2 parts - 1 fix problem database being updated when cookies disabled, , second detect when cookies disabled without doing redirect.

i have posted solution anonymous profiles creating records when cookies disabled.

so focus on second part - getting information profile first page requested. needs done if doing analytics tracking or similar - first part take care of protecting database filling totally useless data when 1) cookies disabled , 2) anonymous profile properties used , works second request (or first postback) onwards.

when researched issue of checking see if cookies enabled, solutions used redirect either same page or different page , again. interestingly, msdn 1 came 2-redirect solution.

while in circumstances redirect acceptable, didn't want performance impact affect majority of our users. instead, opted approach - use ajax run code on server after first request has completed. while has advantage of not causing redirect, has disadvantage of not functioning when javascript disabled. however, opted approach because percentage of data being lost on initial request insignificant , application not depend on data.

so, walking through beginning of process end...

protected sub page_load(byval sender object, byval e system.eventargs) handles me.load      if not me.ispostback          if session.isnewsession             me.injectprofilejavascript()         elseif anonymousprofile.isanonymouscookiestored             'if cookies supported, , isn't first request, update             'profile using current page data.             updateprofile(request.rawurl, request.urlreferrer.originalstring, currentproductid.tostring)         end if      end if  end sub 

this page_load method placed in custom pagebase class, of pages in project inherit from. first thing check whether new session checking session.isnewsession property. property true if cookies disabled or if first request. in both cases, don't want write database.

the "else if" section runs if client accepted session cookie , not first request server. thing note code snippet both sections cannot run in same request, meaning profile can updated 1 (or 0) times per request.

the anonymousprofile class included in other post.

private sub injectprofilejavascript()      dim sb new stringbuilder      sb.appendline("$(document).ready(function() {")     sb.appendline("  if (arecookiessupported() == true) {")     sb.appendline("    $.ajax({")     sb.appendline("      type: 'post',")     sb.appendline("      url: 'httphandlers/updateprofile.ashx',")     sb.appendline("      contenttype: 'application/json; charset=utf-8',")     sb.appendformat("      data: ""{3}'rawurl':'{0}', 'referralurl':'{1}', 'productid':{2}{4}"",", request.rawurl, request.urlreferrer, currentproductid.tostring, "{", "}")     sb.appendline()     sb.appendline("      datatype: 'json'")     sb.appendline("    });")     sb.appendline("  }")     sb.appendline("});")      page.clientscript.registerclientscriptblock(gettype(page), "updateprofile", sb.tostring, true)  end sub  public shared sub updateprofile(byval rawurl string, byval referralurl string, byval productid integer)     dim context httpcontext = httpcontext.current     dim profile profilecommon = ctype(context.profile, profilecommon)      dim currenturl new system.uri("http://www.test.com" & rawurl)     dim query namevaluecollection = httputility.parsequerystring(currenturl.query)     dim source string = query.item("source")     dim search string = query.item("search")     dim ovkey string = query.item("ovkey")      'update profile     profile.testvalue1 = source     profile.testvalue2 = search  end sub 

next, have our method inject ajax call page. keep in mind, still base class regardless of page user lands on code run on first page request.

inside javascript, first test see if cookies enabled and, if so, call custom handler on server using ajax , jquery. passing parameters server code (although 2 of them have been supplied client, bytes aren't significant).

the second method updates profile , contain custom logic so. included snippet on how parse querystring values partial url. thing needs known here shared method updates profile.

important: ajax call function, following handler must added system.web section of web.config file:

<httpmodules>     <add name="scriptmodule" type="system.web.handlers.scriptmodule, system.web.extensions, version=3.5.0.0, culture=neutral, publickeytoken=31bf3856ad364e35"/> </httpmodules> 

i decided best test cookies on client , not make ajax call if cookies disabled. test cookies, use code:

function arecookiessupported() {     var c='c';var ret = false;     document.cookie = 'c=2;';     if (document.cookie.indexof(c,0) > -1) {         ret = true;     } else {         ret = false;     }     deletecookie(c);     return ret } function deletecookie(name) {     var d = new date();     document.cookie = name + '=1;expires=' + d.togmtstring() + ';' + ';'; } 

these 2 javascript functions (in custom .js file) write cookie , read determine if cookies can read. cleans cookie setting expiration date in past.

<%@ webhandler language="vb" class="handlers.updateprofile" %>  imports system imports system.web imports system.web.sessionstate imports newtonsoft.json imports system.collections.generic imports system.io  namespace handlers      public class updateprofile : implements ihttphandler : implements irequiressessionstate          public sub processrequest(byval context httpcontext) implements ihttphandler.processrequest              if anonymousprofile.isanonymouscookiestored                  if context.session.isnewsession                     'writing session state reset isnewsession flag on                     'next request. fix problem if there no session_start                     'defined in global.asax , no other session variables written.                     context.session("activatesession") = ""                 end if                  dim reader new streamreader(context.request.inputstream)                 dim params dictionary(of string, string) = jsonconvert.deserializeobject(of dictionary(of string, string))(reader.readtoend())                  dim rawurl string = params("rawurl")                 dim referralurl string = params("referralurl")                 dim productid integer = params("productid")                  pagebase.updateprofile(rawurl, referralurl, productid)             end if         end sub          public readonly property isreusable() boolean implements ihttphandler.isreusable                             return false             end         end property      end class  end namespace 

this our custom httphandler class receives ajax request. request processed if .aspxanonymous cookie passed in (checked once again utilizing anonymousprofile class other post), prevent robots , other scripts executing it.

next run code update session object if required. strange reason, isnewsession value stay true until session updated, if handler session_start doesn't exist in global.asax. make code work both , without global.asax file , without other code updates session object, run update here.

the next bit of code grabbed this post , contains dependency json.net serializer. torn using approach because of dependency, decided json serializer valuable in future continue adding ajax , jquery site.

then parameters , pass them our shared updateprofile method in pagebase class defined previously.

<!-- required anonymous profiles --> <anonymousidentification enabled="true"/> <profile defaultprovider="sqlprovider" inherits="anonymousprofile">     <providers>         <clear/>         <add name="sqlprovider" type="system.web.profile.sqlprofileprovider" connectionstringname="sqlservices" applicationname="myapp" description="sqlprofileprovider profile test web site"/>     </providers>     <properties>         <add name="testvalue1" allowanonymous="true"/>         <add name="testvalue2" allowanonymous="true"/>     </properties> </profile> 

lastly, have our configuration section profile properties, set used anonymously (i purposely ommitted connection string section, corresponding connection string , database required). main thing note here inclusion of inherits attribute on profile. once again anonymousprofile class defined in other post.