Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5 : linq

Simple way to cache objects and collections for greater performance and scalability

omar — Sun, 01 Nov 2009 16:41:00 GMT

Caching of frequently used data greatly increases the scalability of your application since you can avoid repeated queries on database, file system or to webservices. When objects are cached, it can be retrieved from the cache which is lot faster and more scalable than loading from database, file or web service. However, implementing caching is tricky and monotonous when you have to do it for many classes. Your data access layer gets a whole lot of code that deals with caching objects and collection, updating cache when objects change or get deleted, expire collections when a contained object changes or gets deleted and so on. The more code you write, the more maintenance overhead you add. Here I will show you how you can make the caching a lot easier using Linq to SQL and my library AspectF. It’s a library that helps you get rid of thousands of lines of repeated code from a medium sized project and eliminates plumbing (logging, error handling, retrying etc) type code completely.

Here’s an example how caching significantly improves the performance and scalabitlity of applications. Dropthings – my open source Web 2.0 AJAX portal, without caching can only serve about 11 request/sec with 10 concurrent users on a dual core 64 bit PC. Here data is loaded from database as well as from external sources. Avg page response time is 1.44 sec.

After implementing caching, it became significantly faster, around 32 requests/sec. Page load time decreased significantly as well to 0.41 sec only. During the load test, CPU utilization was around 60%.

It shows clearly the significant difference it can make to your application. If you are suffering from poor page load performance and high CPU or disk activity on your database and application server, then caching Top 5 most frequently used objects in your application will solve that problem right away. It’s a quick win to make your application a lot faster than doing complex re-engineering in your application.

Common approaches to caching objects and collections

Sometimes the caching can be simple, for example caching a single object which does not belong to a collection and does not have child collections that are cached separately. In such case, you write simple code like this:

Is the object being requested already in cache?
- Yes, then serve it from cache.
- No, then load it from database and then cache it.

On the other hand, when you are dealing with cached collection where each item in the collection is also cached separately, then the caching logic is not so simple. For example, say you have cached a User collection. But each User object is also cached separately because you need to load individual User objects frequently. Then the caching logic gets more complicated:

Is the collection being requested already in cache?
- Yes. Get the collection. For each object in the collection:
  - Is that object individually available in cache?
    - Yes, get the individual object from cache. Update it in the collection.
    - No, discard the whole collection from cache. Go to next step:
- No. Load the collection from source (eg database) and cache each item in the collection separately. Then cache the collection.

You might be thinking why do we need to read each individual item from cache and why do we need to cache each item in collection separarely when the whole collection is already in cache? There are two scenarios you need to address when you cache a collection and individual items in that collection are also cached separately:

An individual item has been updated and the updated item is in cache. But the collection, which contains all those individual items, has not been refreshed. So, if you get the collection from cache and return as it is, you will get stale individual items inside that collection. This is why each item needs to be retrieved from cache separately.
An item in the collection may have been force expired in cache. For ex, something changed in the object or the object has been deleted. So, you expired it in cache so that on next retrieval it comes from database. If you load the collection from cache only, then the collection will contain the stale object.

If you are doing it the conventional way, you will be writing a lot of repeated code in your data access layer. For example, say you are loading a Page collection that belongs to a user. If you want to cache the collection of Page for a user as well as cache individual Page objects so that each Page can be retrieved from Cache directly. Then you need to write code like this:

public List<Page> GetPagesOfUserOldSchool(Guid userGuid)
{
    ICache cache = Services.Get<ICache>();
    bool isCacheStale = false;
    string cacheKey = CacheSetup.CacheKeys.PagesOfUser(userGuid);
    var cachedPages = cache.Get(cacheKey) as List<Page>;
    if (cachedPages != null)
    {
        var resultantPages = new List<Page>();
        // If each item in the collection is no longer in cache, invalidate the collection
        // and load again.
        foreach (Page cachedPage in cachedPages)
        {
            var individualPageInCache = cache.Get(CacheSetup.CacheKeys.PageId(cachedPage.ID)) as Page;
            if (null == individualPageInCache)
            {
                // Some item is missing in cache. So, the collection is stale. 
                isCacheStale = true;
            }
            else
            {
                resultantPages.Add(individualPageInCache);
            }
        }

        cachedPages = resultantPages;
    }

    if (isCacheStale)
    {
        // Collection not cached. Need to load collection from database and then cache it.
        var pagesOfUser = _database.GetList<Page, Guid>(...);
        pagesOfUser.Each(page =>
        {
            page.Detach();
            cache.Add(CacheSetup.CacheKeys.PageId(page.ID), page);
        });
        cache.Add(cacheKey, pagesOfUser);
        return pagesOfUser;
    }
    else
    {
        return cachedPages;
    }
}

Imagine writing this kind of code over and over again for each and every entity that you want to cache. This becomes a maintenace nightmare as your project grows.

Here’s how you could do it using AspectF:

public List<Page> GetPagesOfUser(Guid userGuid)
{
    return AspectF.Define
        .CacheList<Page, List<Page>>(Services.Get<ICache>(), 
                     CacheSetup.CacheKeys.PagesOfUser(userGuid), 
                     page => CacheSetup.CacheKeys.PageId(page.ID))
        .Return<List<Page>>(() => 
            _database.GetList<Page, Guid>(...).Select(p => p.Detach()).ToList());
}

Instead of 42 lines of code, you can do it in 5 lines!

Read my article Simple way to cache objects and collections for greater performance and scalability on CodeProject and learn:

Caching Linq to SQL entities
Handling update and delete scenarios
Expiring dependent objects and collections in cache
Handling objects that’s cached with multiple keys
Avoid database query optimizations when you cache sets of data

Enjoy. Don’t forget to vote for me!

Web 2.0 AJAX Portal using jQuery, ASP.NET 3.5, Silverlight, Linq to SQL, WF and Unity

omar — Wed, 08 Apr 2009 18:16:22 GMT

Dropthings – my open source Web 2.0 Ajax Portal has gone through a technology overhauling. Previously it was built using ASP.NET AJAX, a little bit of Workflow Foundation and Linq to SQL. Now Dropthings boasts full jQuery front-end combined with ASP.NET AJAX UpdatePanel, Silverlight widget, full Workflow Foundation implementation on the business layer, 100% Linq to SQL Compiled Queries on the data access layer, Dependency Injection and Inversion of Control (IoC) using Microsoft Enterprise Library 4.1 and Unity. It also has a ASP.NET AJAX Web Test framework that makes it real easy to write Web Tests that simulates real user actions on AJAX web pages. This article will walk you through the challenges in getting these new technologies to work in an ASP.NET website and how performance, scalability, extensibility and maintainability has significantly improved by the new technologies. Dropthings has been licensed for commercial use by prominent companies including BT Business, Intel, Microsoft IS, Denmark Government portal for Citizens; Startups like Limead and many more. So, this is serious stuff! There’s a very cool open source implementation of Dropthings framework available at National University of Singapore portal.

Visit: http://dropthings.omaralzabir.com

I have published a new article on this on CodeProject:

http://www.codeproject.com/KB/ajax/Web20Portal.aspx

Get the source code

Latest source code is hosted at Google code:

http://code.google.com/p/dropthings

There’s a CodePlex site for documentation and issue tracking:

http://www.codeplex.com/dropthings

You will need Visual Studio 2008 Team Suite with Service Pack 1 and Silverlight 2 SDK in order to run all the projects. If you have only Visual Studio 2008 Professional, then you will have to remove the Dropthings.Test project.

New features introduced

Dropthings new release has the following features:

Template users – you can define a user who’s pages and widgets are used as a template for new users. Whatever you put in that template user’s pages, it will be copied for every new user. Thus this is an easier way to define the default pages and widgets for new users. Similarly you can do the same for a registered user. The template users can be defined in the web.config.
Widget-to-Widget communication – Widgets can send message to each other. Widgets can subscribe to an Event Broker and exchange messages using a Pub-Sub pattern.
WidgetZone – you can create any number of zones in any shape on the page. You can have widgets laid in horizontal layout, you can have zones on different places on the page and so on. With this zone model, you are no longer limited to the Page-Column model where you could only have N vertical columns.
Role based widgets – now widgets are mapped to roles so that you can allow different users to see different widget list using ManageWidgetPersmission.aspx.
Role based page setup – you can define page setup for different roles. For ex, Managers see different pages and widgets than Employees.
Widget maximize – you can maximize a widget to take full screen. Handy for widgets with lots of content.
Free form resize – you can freely resize widgets vertically.
Silverlight Widgets – You can now make widgets in Silverlight!

Why the technology overhauling

Performance, Scalability, Maintainability and Extensibility – four key reasons for the overhauling. Each new technology solved one of more of these problems.

First, jQuery was used to replace my personal hand-coded large amount of Javascript code that offered the client side drag & drop and other UI effects. jQuery already has a rich set of library for Drag & Drop, Animations, Event handling, cross browser javascript framework and so on. So, using jQuery means opening the door to thousands of jQuery plugins to be offered on Dropthings. This made Dropthings highly extensible on the client side. Moreover, jQuery is very light. Unlike AJAX Control Toolkit jumbo sized framework and heavy control extenders, jQuery is very lean. So, total javascript size decreased significantly resulting in improved page load time. In total, the jQuery framework, AJAX basic framework, all my stuffs are total 395KB, sweet! Performance is key; it makes or breaks a product.

Secondly, Linq to SQL queries are replaced with Compiled Queries. Dropthings did not survive a load test when regular lambda expressions were used to query database. I could only reach up to 12 Req/Sec using 20 concurrent users without burning up web server CPU on a Quad Core DELL server.

Thirdly, Workflow Foundation is used to build operations that require multiple Data Access Classes to perform together in a single transaction. Instead of writing large functions with many if…else conditions, for…loops, it’s better to write them in a Workflow because you can visually see the flow of execution and you can reuse Activities among different Workflows. Best of all, architects can design workflows and developers can fill-in code inside Activities. So, I could design a complex operations in a workflow without writing the real code inside Activities and then ask someone else to implement each Activity. It is like handing over a design document to developers to implement each unit module, only that here everything is strongly typed and verified by compiler. If you strictly follow Single Responsibility Principle for your Activities, which is a smart way of saying one Activity does only one and very simple task, you end up with a highly reusable and maintainable business layer and a very clean code that’s easily extensible.

Fourthly, Unity Dependency Injection (DI) framework is used to pave the path for unit testing and dependency injection. It offers Inversion of Control (IoC), which enables testing individual classes in isolation. Moreover, it has a handy feature to control lifetime of objects. Instead of creating instance of commonly used classes several times within the same request, you can make instances thread level, which means only one instance is created per thread and subsequent calls reuse the same instance. Are these going over your head? No worries, continue reading, I will explain later on.

Fifthly, enabling API for Silverlight widgets allows more interactive widgets to be built using Silverlight. HTML and Javascripts still have limitations on smooth graphics and continuous transmission of data from web server. Silverlight solves all of these problems.

Read the article for details on how all these improvements were done and how all these hot techs play together in a very useful open source project for enterprises.

http://www.codeproject.com/KB/ajax/Web20Portal.aspx

Don’t forget to vote for me if you like it.

Memory Leak with delegates and workflow foundation

omar — Sat, 14 Mar 2009 08:35:53 GMT

Recently after Load Testing my open source project Dropthings, I encountered a lot of memory leak. I found lots of Workflow Instances and Linq Entities were left in memory and never collected. After profiling the web application using .NET Memory Profiler, it showed the real picture:

It shows you that instances of the several types are being created but not being removed. You see the “New” column has positive value, but the “Remove” column has 0. That means new instances are being created, but not removed. Basically the way you do Memory Profiling is, you take two snapshots. Say you take one snapshot when you first visit your website. Then you do some action on the website that results in allocation of objects. Then you take another snapshot. When you compare both snapshots, you can see how many instances of classes were created between these two snapshots and how many were removed. If they are not equal, then you have leak. Generally in web application many objects are created on every page hit and the end of the request, all those objects are supposed to be released. If they are not released, then we have a problem. But that’s the scenario for desktop applications because in a desktop application, objects can remain in memory until app is closed. But you should know best from the code which objects were supposed to go out of scope and get released.

For beginners, leak means objects are being allocated but not being freed because someone is holding reference to the objects. When objects leak, they remain in memory forever, until the process (or app domain) is closed. So, if you have a leaky website, your website is continuously taking up memory until it runs out of memory on the web server and thus crash. So, memory leak is a bad – it prevents you from running your product for long duration and requires frequent restart of app pool.

So, the above screenshot shows Workflow and Linq related classes are not being removed, and thus leaking. This means somewhere workflow instances are not being released and thus all workflow related objects are remaining. You can see the number is same 48 for all workflow related objects. This is a good indication that, almost every instance of workflow is leaked because there were total 48 workflows created and ran. Moreover it indicates we have a leak from a top Workflow instance level, not in some specific Activity or somewhere deep in the code.

As the workflows use Linq stuff, they held reference to the Linq stuffs and thus the Linq stuffs leaked as well. Sometimes you might be looking for why A is leaking. But you actually end up finding that since B was holding reference to A and B was leaking and thus A was leaking as well. This is sometimes tricky to figure out and you spend a lot of time looking at the wrong direction.

Now let me show you the buggy code:

ManualWorkflowSchedulerService manualScheduler = 
  workflowRuntime.GetService<ManualWorkflowSchedulerService>();

WorkflowInstance instance = workflowRuntime.CreateWorkflow(workflowType, properties);
instance.Start();

EventHandler<WorkflowCompletedEventArgs> completedHandler = null;
completedHandler = delegate(object o, WorkflowCompletedEventArgs e)
{
    if (e.WorkflowInstance.InstanceId == instance.InstanceId) // 1. instance
    {
        workflowRuntime.WorkflowCompleted -= completedHandler; // 2. terminatedhandler

        // copy the output parameters in the specified properties dictionary
        Dictionary<string,object>.Enumerator enumerator = 
            e.OutputParameters.GetEnumerator();
        while( enumerator.MoveNext() )
        {
            KeyValuePair<string,object> pair = enumerator.Current;
            if( properties.ContainsKey(pair.Key) )
            {
                properties[pair.Key] = pair.Value;
            }
        }
    }
};

Exception x  = null;
EventHandler<WorkflowTerminatedEventArgs> terminatedHandler = null;
terminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e)
{
    if (e.WorkflowInstance.InstanceId == instance.InstanceId) // 3. instance
    {
        workflowRuntime.WorkflowTerminated -= terminatedHandler; // 4. completeHandler
        Debug.WriteLine( e.Exception );

        x = e.Exception;
    }
};

workflowRuntime.WorkflowCompleted += completedHandler;
workflowRuntime.WorkflowTerminated += terminatedHandler;

manualScheduler.RunWorkflow(instance.InstanceId);

Can you spot the code where it leaked?

I have numbered the lines in comment where the leak is happening. Here the delegate is acting like a closure and those who are from Javascript background know closure is evil. They leak memory unless very carefully written. Here the delegate keeps a reference to the instance object. So, if somehow delegate is not released, the instance will remain in memory forever and thus leak. Now can you find a situation when the delegate will not be released?

Say the workflow completed. It will fire the completeHandler. But the completeHandler will not release the terminateHandler. Thus the terminateHandler remains in memory and it also holds reference to the instance. So, we have a leaky delegate leaking whatever it is holding onto outside it’s scope. Here the only thing outside the scope if the instance, which it is tried to access from the parent function.

Since the workflow instance is not released, all the properties the workflow and all the activities inside it are holding onto remains in memory. Most of the workflows and activities expose public properties which are Linq Entities. Thus the Linq Entities remain in memory. Now Linq Entities keep a reference to the DataContext from where it is produced. Thus we have DataContext remaining in memory. Moreover, DataContext keeps reference to many internal objects and metadata cacahe, so they remain in memory as well.

So, the correct code is:

ManualWorkflowSchedulerService manualScheduler = 
    workflowRuntime.GetService<ManualWorkflowSchedulerService>();

WorkflowInstance instance = workflowRuntime.CreateWorkflow(workflowType, properties);
instance.Start();
var instanceId = instance.InstanceId;

EventHandler<WorkflowCompletedEventArgs> completedHandler = null;
completedHandler = delegate(object o, WorkflowCompletedEventArgs e)
{
    if (e.WorkflowInstance.InstanceId == instanceId) // 1. instanceId is a Guid
    {
        // copy the output parameters in the specified properties dictionary
        Dictionary<string,object>.Enumerator enumerator = 
                e.OutputParameters.GetEnumerator();
        while( enumerator.MoveNext() )
        {
            KeyValuePair<string,object> pair = enumerator.Current;
            if( properties.ContainsKey(pair.Key) )
            {
                properties[pair.Key] = pair.Value;
            }
        }
    }
};

Exception x  = null;
EventHandler<WorkflowTerminatedEventArgs> terminatedHandler = null;
terminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e)
{
    if (e.WorkflowInstance.InstanceId == instanceId) // 2. instanceId is a Guid
    {
        x = e.Exception;
        Debug.WriteLine(e.Exception);
    }
};

workflowRuntime.WorkflowCompleted += completedHandler;
workflowRuntime.WorkflowTerminated += terminatedHandler;

manualScheduler.RunWorkflow(instance.InstanceId);
// 3. Both delegates are now released
workflowRuntime.WorkflowTerminated -= terminatedHandler;
workflowRuntime.WorkflowCompleted -= completedHandler;

There are two changes – in both delegates, the instanceId variable is passed, instead of the instance. Since instanceId is a Guid, which is a struct type data type, not a class, there’s no issue of referencing. Structs are copied, not referenced. So, they don’t leak memory. Secondly, both delegates are released at the end of the workflow execution, thus releasing both references.

In Dropthings, I am using the famous CallWorkflow Activity by John Flanders, which is widely used to execute one Workflow from another synchronously. There’s a CallWorkflowService class which is responsible for synchronously executing another workflow and that has similar memory leak problem. The original code of the service is as following:

public class CallWorkflowService : WorkflowRuntimeService
{
    #region Methods

    public void StartWorkflow(Type workflowType,Dictionary<string,object> inparms, 
           Guid caller,IComparable qn)
    {
        WorkflowRuntime wr = this.Runtime;
        WorkflowInstance wi = wr.CreateWorkflow(workflowType,inparms);
        wi.Start();
        ManualWorkflowSchedulerService ss = 
             wr.GetService<ManualWorkflowSchedulerService>();
        if (ss != null)
            ss.RunWorkflow(wi.InstanceId);
        EventHandler<WorkflowCompletedEventArgs> d  = null;
        d = delegate(object o, WorkflowCompletedEventArgs e)
        {
            if (e.WorkflowInstance.InstanceId ==wi.InstanceId)
            {
                wr.WorkflowCompleted -= d;
                WorkflowInstance c = wr.GetWorkflow(caller);
                c.EnqueueItem(qn, e.OutputParameters, null, null);
            }
        };
        EventHandler<WorkflowTerminatedEventArgs> te = null;
        te = delegate(object o, WorkflowTerminatedEventArgs e)
        {
            if (e.WorkflowInstance.InstanceId == wi.InstanceId)
            {
                wr.WorkflowTerminated -= te;
                WorkflowInstance c = wr.GetWorkflow(caller);
                c.EnqueueItem(qn, new Exception("Called Workflow Terminated", 
                  e.Exception), null, null);
            }
        };
        wr.WorkflowCompleted += d;
        wr.WorkflowTerminated += te;
    }

    #endregion Methods
}

As you see, it has that same delegate holding reference to instance object problem. Moreover, there’s some queue stuff there, which requires the caller and qn parameter passed to the StartWorkflow function. So, not a straight forward fix.

I tried to rewrite the whole CallWorkflowService so that it does not require two delegates to be created per Workflow. Then I took the delegates out. Thus there’s no chance of closure holding reference to unwanted objects. The result looks like this:

public class CallWorkflowService : WorkflowRuntimeService
{
    #region Fields

    private EventHandler<WorkflowCompletedEventArgs> _CompletedHandler = null;
    private EventHandler<WorkflowTerminatedEventArgs> _TerminatedHandler = null;
    private Dictionary<Guid, WorkflowInfo> _WorkflowQueue = 
       new Dictionary<Guid, WorkflowInfo>();

    #endregion Fields

    #region Methods

    public void StartWorkflow(Type workflowType,Dictionary<string,object> inparms,
        Guid caller,IComparable qn)
    {
        WorkflowRuntime wr = this.Runtime;
        WorkflowInstance wi = wr.CreateWorkflow(workflowType,inparms);
        wi.Start();

        var instanceId = wi.InstanceId;
        _WorkflowQueue[instanceId] = new WorkflowInfo { Caller = caller, qn = qn };

        ManualWorkflowSchedulerService ss = 
           wr.GetService<ManualWorkflowSchedulerService>();
        if (ss != null)
            ss.RunWorkflow(wi.InstanceId);
    }

    protected override void OnStarted()
    {
        base.OnStarted();

        if (null == _CompletedHandler)
        {
            _CompletedHandler = delegate(object o, WorkflowCompletedEventArgs e)
            {
                var instanceId = e.WorkflowInstance.InstanceId;
                if (_WorkflowQueue.ContainsKey(instanceId))
                {
                    WorkflowInfo wf = _WorkflowQueue[instanceId];
                    WorkflowInstance c = this.Runtime.GetWorkflow(wf.Caller);
                    c.EnqueueItem(wf.qn, e.OutputParameters, null, null);
                    _WorkflowQueue.Remove(instanceId);
                }
            };
            this.Runtime.WorkflowCompleted += _CompletedHandler;
        }

        if (null == _TerminatedHandler)
        {
            _TerminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e)
            {
                var instanceId = e.WorkflowInstance.InstanceId;
                if (_WorkflowQueue.ContainsKey(instanceId))
                {
                    WorkflowInfo wf = _WorkflowQueue[instanceId];
                    WorkflowInstance c = this.Runtime.GetWorkflow(wf.Caller);
                    c.EnqueueItem(wf.qn, 
                      new Exception("Called Workflow Terminated", e.Exception), 
                      null, null);
                    _WorkflowQueue.Remove(instanceId);
                }
            };

            this.Runtime.WorkflowTerminated += _TerminatedHandler;
        }
    }

    protected override void OnStopped()
    {
        _WorkflowQueue.Clear();

        base.OnStopped();
    }

    #endregion Methods

    #region Nested Types

    private struct WorkflowInfo
    {
        #region Fields

        public Guid Caller;
        public IComparable qn;

        #endregion Fields
    }

    #endregion Nested Types
}

After fixing the problem, another Memory Profile result showed the leak is gone:

As you see, the numbers vary, which means there’s no consistent leak. Moreover, looking at the types that remains in memory, they look more like metadata than instances of classes. So, they are basically cached instances of metadata, not instances allocated during workflow execution which are supposed to be freed. So, we solved the memory leak!

Now you know how to write anonymous delegates without leaking memory and how to run workflow without leaking them. Basically, the principle theory is – if you are referencing some outside object from an anonymous delegate, make sure that object is not holding reference to the delegate in some way, may be directly or may be via some child objects of its own. Because then you have a circular reference. If possible, do not try to access objects e.g. instance inside an anonymous delegate that is declared outside the delegate. Try accessing instrinsic data types like int, string, DateTime, Guid etc which are not reference type variables. So, instead of referencing to an object, you should declare local variables e.g. instanceId that gets the value of properties (e.g. instance.InstanceId) from the object and then use those local variables inside the anonymous delegate.

Linq to SQL solve Transaction deadlock and Query timeout problem using uncommitted reads

omar — Sat, 07 Mar 2009 07:30:52 GMT

When your database tables start accumulating thousands of rows and many users start working on the same table concurrently, SELECT queries on the tables start producing lock contentions and transaction deadlocks. This is a common problem in any high volume website. As soon as you start getting several concurrent users hitting your website that results in SELECT queries on some large table like aspnet_users table that are also being updated very frequently, you end up having one of these errors:

Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Or,

Timeout Expired. The Timeout Period Elapsed Prior To Completion Of The Operation Or The Server Is Not Responding.

The solution to these problems are – use proper index on the table and use transaction isolation level Read Uncommitted or WITH (NOLOCK) in your SELECT queries. So, if you had a query like this:

SELECT * FORM aspnet_users 
where ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

You should end up having any of the above errors under high load. There are two ways to solve this:

SET TRANSACTION LEVEL READ UNCOMMITTED;
SELECT * FROM aspnet_Users 
WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

Or use the WITH (NOLOCK):

SELECT * FROM aspnet_Users WITH (NOLOCK) 
WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

The reason for the errors are that since aspnet_users is a high read and high write table, during read, the table is partially locked and during write, it is also locked. So, when the locks overlap on each other from several queries and especially when there’s a query that’s trying to read a large number of rows and thus locking large number of rows, some of the queries either timeout or produce deadlocks.

Linq to Sql does not produce queries with the WITH (NOLOCK) option nor does it use READ UNCOMMITTED. So, if you are using Linq to SQL queries, you are going to end up with any of these problems on production pretty soon when your site becomes highly popular.

For example, here’s a very simple query:

using (var db = new DropthingsDataContext())
{
    var user = db.aspnet_Users.First();
    var pages = user.Pages.ToList();
}

DropthingsDataContext is a DataContext built from Dropthings database.

When you attach SQL Profiler, you get this:

You see none of the queries have READ UNCOMMITTED or WITH (NOLOCK).

The fix is to do this:

using (var db = new DropthingsDataContext2())
{
    db.Connection.Open();
    db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

    var user = db.aspnet_Users.First();
    var pages = user.Pages.ToList();
}

This will result in the following profiler output

As you see, both queries execute within the same connection and the isolation level is set before the queries execute. So, both queries enjoy the isolation level.

Now there’s a catch, the connection does not close. This seems to be a bug in the DataContext that when it is disposed, it does not dispose the connection it is holding onto.

In order to solve this, I have made a child class of the DropthingsDataContext named DropthingsDataContext2 which overrides the Dispose method and closes the connection.

   class DropthingsDataContext2 : DropthingsDataContext, IDisposable
    {
        public new void Dispose()
        {
            if (base.Connection != null)
                if (base.Connection.State != System.Data.ConnectionState.Closed)
                {
                    base.Connection.Close();
                    base.Connection.Dispose();
                }

            base.Dispose();            
        }
    }

This solved the connection problem.

There you have it, no more transaction deadlock or lock contention from Linq to SQL queries. But remember, this is only to eliminate such problems when your database already has the right indexes. If you do not have the proper index, then you will end up having lock contention and query timeouts anyway.

There’s one more catch, READ UNCOMMITTED will return rows from transactions that have not completed yet. So, you might be reading rows from transactions that will rollback. Since that’s generally an exceptional scenario, you are more or less safe with uncommitted read, but not for financial applications where transaction rollback is a common scenario. In such case, go for committed read or repeatable read.

There’s another way you can achieve the same, which seems to work, that is using .NET Transactions. Here’s the code snippet:

using (var transaction = new TransactionScope(
    TransactionScopeOption.RequiresNew,
    new TransactionOptions()
    {
        IsolationLevel = IsolationLevel.ReadUncommitted,
        Timeout = TimeSpan.FromSeconds(30)
    }))
{
    using (var db = new DropthingsDataContext())
    {
        var user = db.aspnet_Users.First();
        var pages = user.Pages.ToList();

        transaction.Complete();
    }
}

Profiler shows a transaction begins and ends:

The downside is it wraps your calls in a transaction. So, you are unnecessarily creating transactions even for SELECT operations. When you do this hundred times per second on a web application, it’s a significant over head.

Some really good examples of deadlocks are given in this article:

http://www.code-magazine.com/article.aspx?quickid=0309101&page=2

I highly recommend it.

Linq to SQL: Delete an entity using Primary Key only

omar — Thu, 30 Oct 2008 00:27:00 GMT

Linq to Sql does not come with a function like .Delete(ID) which allows you to delete an entity using it’s primary key. You have to first get the object that you want to delete and then call .DeleteOnSubmit(obj) to queue it for delete. Then you have to call DataContext.SubmitChanges() to play the delete queries on database. So, how to delete object without getting them from database and avoid database roundtrip?

You can call this function using DeleteByPK<Employee, int>(10, dataContext);

First type is the entity type and second one is the type of the primary key. If your object’s primary key is a Guid field, specify Guid instead of int.

How it works:

It figures out the table name and the primary key field name from the entity
Then it uses the table name and primary key field name to build a DELETE query

Figuring out the table name and primary key field name is a bit hard. There’s some reflection involved. The GetTableDef<TSource>() returns the table name and primary key field name for an entity.

Every Linq Entity class is decorated with a Table attribute that has the table name:

Then the primary key field is decorated with a Column attribute with IsPrimaryKey = true.

So, using reflection we can figure out the table name and the primary key property and the field name.

Here’s the code that does it:

Before you scream “Reflection is SLOW!!!!” the definition is cached. So, reflection is used only once per appDomain per entity. Subsequent call is just a dictionary lookup away, which is as fast as it can get.

You can also delete a collection of object without ever getting any one of them. The the following function to delete a whole bunch of objects:

The code is available here:

http://code.msdn.microsoft.com/DeleteEntitiesLinq

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

omar — Mon, 27 Oct 2008 13:31:00 GMT

If you are using Linq to SQL, instead of writing regular Linq Queries, you should be using Compiled Queries. if you are building an ASP.NET web application that’s going to get thousands of hits per hour, the execution overhead of Linq queries is going to consume too much CPU and make your site slow. There’s a runtime cost associated with each and every Linq Query you write. The queries are parsed and converted to a nice SQL Statement on *every* hit. It’s not done at compile time because there’s no way to figure out what you might be sending as the parameters in the queries during runtime. So, if you have common Linq to Sql statements like the following one throughout your growing web application, you are soon going to have scalability nightmares:

var query = from widget in dc.Widgets
                where widget.ID == id && widget.PageID == pageId
                select widget;

var widget = query.SingleOrDefault();

There’s a nice blog post by JD Conley that shows how evil Linq to Sql queries are:

You see how many times SqlVisitor.Visit is called to convert a Linq Query to its SQL representation? The runtime cost to convert a Linq query to its SQL Command representation is just way too high.

Rico Mariani has a very informative performance comparison of regular Linq queries vs Compiled Linq queries performance:

Compiled Query wins on every case.

So, now you know about the benefits of compiled queries. If you are building ASP.NET web application that is going to get high traffic and you have a lot of Linq to Sql queries throughout your project, you have to go for compiled queries. Compiled Queries are built for this specific scenario.

In this article, I will show you some steps to convert regular Linq to Sql queries to their Compiled representation and how to avoid the dreaded exception “Compiled queries across DataContexts with different LoadOptions not supported.”

Here are some step by step instruction on converting a Linq to Sql query to its compiled form:

First we need to find out all the external decision factors in a query. It mostly means parameters in the WHERE clause. Say, we are trying to get a user from aspnet_users table using Username and Application ID:

Here, we have two external decision factor – one is the Username and another is the Application ID. So, first think this way, if you were to wrap this query in a function that will just return this query as it is, what would you do? You would create a function that takes the DataContext (dc named here), then two parameters named userName and applicationID, right?

So, be it. We create one function that returns just this query:

Next step is to replace this function with a Func<> representation that returns the query. This is the hard part. If you haven’t dealt with Func<> and Lambda expression before, then I suggest you read this and this and then continue.

So, here’s the delegate representation of the above function:

Couple of things to note here. I have declared the delegate as static readonly because a compiled query is declared only once and reused by all threads. If you don’t declare Compiled Queries as static, then you don’t get the performance gain because compiling queries everytime when needed is even worse than regular Linq queries.

Then there’s the complex Func<DropthingsDataContext, string, Guid, IQueryable<aspnet_User>> thing. Basically the generic Func<> is declared to have three parameters from the GetQuery function and a return type of IQueryable<aspnet_User>. Here the parameter types are specified so that the delegate is created strongly typed. Func<> allows up to 4 parameters and 1 return type.

Next comes the real business, compiling the query. Now that we have the query in delegate form, we can pass this to CompiledQuery.Compile function which compiles the delegate and returns a handle to us. Instead of directly assigning the lambda expression to the func, we will pass the expression through the CompiledQuery.Compile function.

Here’s where head starts to spin. This is so hard to read and maintain. Bear with me. I just wrapped the lambda expression on the right side inside the CompiledQuery.Compile function. Basically that’s the only change. Also, when you call CompiledQuery.Compile<>, the generic types must match and be in exactly the same order as the Func<> declaration.

Fortunately, calling a compiled query is as simple as calling a function:

There you have it, a lot faster Linq Query execution. The hard work of converting all your queries into Compiled Query pays off when you see the performance difference.

Now, there are some challenges to Compiled Queries. Most common one is, what do you do when you have more than 4 parameters to supply to a Compiled Query? You can’t declare a Func<> with more than 4 types. Solution is to use a struct to encapsulate all the parameters. Here’s an example:

Calling the query is quite simple:

Now to the dreaded challenge of using LoadOptions with Compiled Query. You will notice that the following code results in an exception:

The above DataLoadOption runs perfectly when you use regular Linq Queries. But it does not work with compiled queries. When you run this code and the query hits the second time, it produces an exception:

Compiled queries across DataContexts with different LoadOptions not supported

A compiled query remembers the DataLoadOption once its called. It does not allow executing the same compiled query with a different DataLoadOption again. Although you are creating the same DataLoadOption with the same LoadWith<> calls, it still produces exception because it remembers the exact instance that was used when the compiled query was called for the first time. Since next call creates a new instance of DataLoadOptions, it does not match and the exception is thrown. You can read details about the problem in this forum post.

The solution is to use a static DataLoadOption. You cannot create a local DataLoadOption instance and use in compiled queries. It needs to be static. Here’s how you can do it:

Basically the idea is to construct a static instance of DataLoadOptions using a static function. As writing function for every single DataLoadOptions combination is painful, I created a static delegate here and executed it right on the declaration line. This is in interesting way to declare a variable that requires more than one statement to prepare it.

Using this option is very simple:

Now you can use DataLoadOptions with compiled queries.

My first book - Building a Web 2.0 Portal with ASP.NET 3.5

omar — Sun, 13 Jan 2008 20:23:00 GMT

My first book "Building a Web 2.0 Portal with ASP.NET 3.5" from O'Reilly is published and available in the stores. This book explains in detail the architecture design, development, test, deployment, performance and scalability challenges of my open source web portal Dropthings.com. Dropthings is a prototype of a web portal similar to iGoogle or Pageflakes. But this portal is developed using recently released brand new technologies like ASP.NET 3.5, C# 3.0, Linq to Sql, Linq to XML, and Windows Workflow foundation. It makes heavy use of ASP.NET AJAX 1.0. Throughout my career I have built several state-of-the-art personal, educational, enterprise and mass consumer web portals. This book collects my experience in building all of those portals.

O'Reilly Website:
http://www.oreilly.com/catalog/9780596510503/

Amazon:
http://www.amazon.com/Building-Web-2-0-Portal-ASP-NET/dp/0596510500

Disclaimer: This book does not show you how to build Pageflakes. Dropthings is entirely different in terms of architecture, implementation and the technologies involved.

You learn how to:

Implement a highly decoupled architecture following the popular n-tier, widget-based application model
Provide drag-and-drop functionality, and use ASP.NET 3.5 to build the server-side part of the web layer
Use LINQ to build the data access layer, and Windows Workflow Foundation to build the business layer as a collection of workflows
Build client-side widgets using JavaScript for faster performance and better caching
Get maximum performance out of the ASP.NET AJAX Framework for faster, more dynamic, and scalable sites
Build a custom web service call handler to overcome shortcomings in ASP.NET AJAX 1.0 for asynchronous, transactional, cache-friendly web services
Overcome JavaScript performance problems, and help the user interface load faster and be more responsive
Solve various scalability and security problems as your site grows from hundreds to millions of users
Deploy and run a high-volume production site while solving software, hardware, hosting, and Internet infrastructure problems

If you're ready to build state-of-the art, high-volume web applications that can withstand millions of hits per day, this book has exactly what you need.

Linq to SQL: How to Attach object to a different data context

omar — Sat, 08 Dec 2007 12:59:00 GMT

After upgrading to Visual Studio 2008 RTM, you will have trouble updating Linq to SQL Classes which are read from one data context and then updated into another data context. You will get this exception during update:

System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

Here's a typical example taken from a Forum post:

   1: public static void
UpdateEmployee(Employee employee)

   2: {

   3: using (HRDataContext dataContext =
new HRDataContext())

   4: {

   5: //Get original employee

   6: Employee originalEmployee =
dataContext.Employees.Single(e=
>e.EmployeeId==employee.EmployeeId);

7:

   8: //attach to datacontext

   9:
dataContext.Employees.Attach(employee, originalEmployee);

10:

  11: //save changes

  12: dataContext.SubmitChanges();

13:

  14: }

  15: }

When you call the Attach function, you will get the exception mentioned above.

Here's a way to do this. First, create a partial class that adds a Detach method to Employee class. This method will detach the object from it's data context and detach associated objects.

   1: public partial class Employee

   2: {

   3:   public void Detach()

   4:   {

   5:     this.PropertyChanged = null;
this.PropertyChanging = null;

6:

   7:     // Assuming there's a foreign
key from Employee to Boss

   8:     this.Boss = default(EntityRef
<
Boss
>);

   9:     // Similarly set child objects
to default as well

10:

  11:     this.Subordinates =
default(EntitySet
<
Subordinate
>);

  12:   }

  13: }

14:

15:

Now during update, call Detach before attaching the object to a DataContext.

   1: public static void
UpdateEmployee(Employee employee)

   2: {

   3:     using (HRDataContext
dataContext = new HRDataContext())

   4:     {

   5:         //attach to datacontext

   6:         employee.Detach();

7:

   8:        
dataContext.Employees.Attach(employee);

   9:         //save changes

10:

  11:        
dataContext.SubmitChanges();

  12:     }

  13: }

14:

This'll work. It assumes the employee object already has its primary key populated.

You might see during update, it's generating a bloated UPDATE statement where each and every property is appearing on the WHERE clause. In that case, set UpdateCheck to Never for all properties of Employee class from the Object Relational Designer.

Data Access usind DLinq

omar — Thu, 28 Dec 2006 07:47:36 GMT

DLinq is so much fun. It's so amazingly simple to write data access layer that generates really optimized SQL. If you have not used DLinq before, brace for impact!

When you use DLinq, you just design the database and then use SqlMetal.exe (comes with Linq May CTP) in order to generate a Data Access class which contains all the data access codes and entity classes. Think about the dark age when you had to hand code all entity classes following the database design and hand code data access classes. Whenever your database design changed, you had to modify the entity classes and modify the insert, update, delete, get methods in data access layer. Of course you could use third party ORM tools or use some kind of code generators which generates entity classes from database schema and generates data access layer codes. But do no more, DLinq does it all for you!

The best thing about DLinq is it can generate something called Projection which contains only the necessary fields and not the whole object. There's no ORM tool or Object Oriented Database library which can do this now because it really needs a custom compiler in order to support this. The benefit of projection is pure performance. You do not SELECT fields which you don't need, nor do you contruct a jumbo object which has all the fields. DLinq only selects the required fields and creates objects which contains only the selected fields.

Let's see how easy it is to create a new object in database called "Page":

var db = new DashboardData(ConnectionString);

var newPage = new Page();
newPage.UserId = UserId;
newPage.Title = Title;
newPage.CreatedDate = DateTime.Now;
newPage.LastUpdate = DateTime.Now;

db.Pages.Add(newPage);
db.SubmitChanges();
NewPageId = newPage.ID;

Here, DashboardData is the class which SqlMetal.exe generated.

Say, you want to change a Page's name:

var page = db.Pages.Single( p => p.ID == PageId );
page.Title = PageName;
db.SubmitChanges();

Here only one row is selected.

You can also select a single value:

var UserGuid = (from u in db.AspnetUsers
where u.LoweredUserName == UserName && u.ApplicationId == DatabaseHelper.ApplicationGuid
select u.UserId).Single();

And here's the Projection I was talking about:

var users = from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName };

foreach( var user in users )
{
    Debug.WriteLine( user.UserName );
}

If you want to do some paging like select 20 rows from 100th rows:

var users = (from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName }).Skip(100).Take(20);

foreach( var user in users )
{
    Debug.WriteLine( user.UserName );
}

If you are looking for transaction, see how simple it is:

using( TransactionScope ts = new TransactionScope() )
{
    List<Page> pages = db.Pages.Where( p => p.UserId == oldGuid ).ToList();
    foreach( Page page in pages )
        page.UserId = newGuid;
    
    // Change setting ownership
    UserSetting setting = db.UserSettings.Single( u => u.UserId == oldGuid );
    db.UserSettings.Remove(setting);
    
    setting.UserId = newGuid;
    db.UserSettings.Add(setting);
    db.SubmitChanges();

    ts.Complete();
}

Unbelievable? Believe it.

You may have some mixed feelings about DLinq performance. Believe me, it generates exactly the right SQL that I wanted it to do. Use SqlProfiler and see the queries it sends to the database. You might also think all these "var" stuffs sounds like late binding in old COM era. It will not be as fast as strongly typed code or your own hand written super optimal code which does exactly what you want. You will be surprised to know that all these DLinq code actually gets transformed into pure and simple .NET 2.0 IL by the Linq compiler. There's no magic stuff or no additional libraries in order to run these codes in your existing .NET 2.0 project. Unlike many ORM tools, DLinq also does not heavily depend on Reflection.

XLinq: Reading RSS and Atom using XLinq

omar — Tue, 26 Dec 2006 14:20:00 GMT

I am playing around with XLinq. I made a RSS Viewer which downloads RSS/Atom feed and shows a list of links of titles.

XLinq is so unbelievably easy to use. See in less than 10 lines I can populate a DataList full of links from RSS/Atom feed. First I made a DataList:

< asp:DataList ID ="FeedList" runat ="Server" EnableViewState ="False" > < ItemTemplate > < asp:HyperLink ID ="FeedLink" runat ="server" Target ="_blank" CssClass ="feed_item_link" NavigateUrl ='<%# Eval("link") % > '> < %# Eval ("title") % > </ asp:HyperLink > </ ItemTemplate > </ asp:DataList >

Now use XElement.Load to load a feed from any Url.

feed = XElement.Load(url);

After this, we do a select on the <item> nodes and create a projection of each node which contains only the title and link node values. The project acts as a Data Bound Item to the DataList.

if ( feed.Element( " channel " ) != null ) FeedList.DataSource = (from item in feed.Element( " channel " ).Elements( " item " ) select new { title = item.Element( " title " ).Value, link = item.Element( " link " ).Value }).Take( 3 );

For Atom, it's a bit difficult to discover. You need to use Xml Namespace in order to read through the Atom feed:

XNamespace ns = "http://www.w3.org/2005/Atom";

else if ( feed.Element(ns + " entry " ) != null ) FeedList.DataSource = (from item in feed.Elements(ns + " entry " ) select new { title = item.Element(ns + " title " ).Value, link = item.Element(ns + " link " ).Attribute( " href " ).Value }).Take( 3 );

Cool thing is that you can do paging easily by adding the Take and Skip methods at the end of Linq expression as I have done here. Here you see only Take which takes only the specified items from the feed. If you use Skip, then it will skip elements from beginning.